Please Help

[JEPOOK]

I am trying to get a number that will start at (2 digit month, 2 digit year (not visible) and 3 digit sequence number) that will automatically reset to 001 when the month changes. The number needs reference a table and go off the last number used. I have not been having any luck with it any suggestions?


Private Sub cmd1_Click()
Set localConnection = CurrentDb()
Set rJN = localConnection.OpenRecordset("TEST")
rJN.MoveLast

Dim cMonth As Integer
Dim cYear As Integer
Dim vYear As Integer
Dim vMonth As Integer
Dim vNum As Integer

vYear = Int(Val(Left$(rJN![Test], 2)))
vMonth = Int(Val(Mid$(rJN![Test], 2)))
vNum = Int(Val(Right$(rJN![Test], 3)))
cYear = Year(Now)
cMonth = Month(Now)

If vMonth = cMonth Then
rJN = rJN + 1
Else
rJN = "001"
End If

End Sub


Thanks

 

[bat17]

You could keep your Year/Month/Serial Number in seperate columns with a joint index set to unique. Then you can use Dmax to find the highest serial number for a given Year/Month combination. something like this.

Dim intMonth As Integer
Dim intYear As Integer
intMonth = 11
intYear = 2003

Me.lngSer = Nz(DMax("[lngSerial]", "tbleSerial", "[lngYear] = " & intYear & " and [lngMonth] = " & intMonth)) + 1

End Sub

using the NZ() function will let you roll the month over ok.
HTH

Peter