I'm using the following code in a database that creates a new record and assigns it a Julian Date, Serial Number, and Timestamp, when a user presses a button. The idea is that the serial must always remain the same number of digits, so it rolls over from N999 to N001 - the timestamp allows the databse to distinguish between seperate records with the same serial number. The problem is this: Occasionally, a user may need to change the serial to a non-standard (other than "N###")serial number. This causes a problem when the code cannot properly increment the serial for the next record. I need my code to find the latest serial, based on the SN_Date(timestamp) that begins with "N". In other words, if the latest serial is "N001" and a user creates a new record and changes the serial from "N002" to "1D03", the next new record should get a serial of "N002". Any Ideas?
--Code Follows--
--Code Follows--
Code:
Private Sub New_Record_Click()
DoCmd.GoToRecord , , acNewRec
Dim txtSN As String
txtSN = DMax("Doc", "Off-Station", "[SN_Date]=#" & DMax("SN_Date", "Off-Station") & "#")
If Left(txtSN, 1) = "N" Then
txtNum = Right(txtSN, 3)
If txtNum < 999 Then
'add 1 to the value of the last SN if SN less than 999
'and re-establish the leading zeros
Doc = "N" & Right("000" & txtNum + 1, 3)
Else
'restart at 001 if last SN was 999
Doc = "N001"
End If
End If
'set serial number date field to current date/time
SN_Date = Now()
'establish variables for determining Julian date
txtStart = "01/01/" & Year(Now) 'first day of current year
txtEnd = Now 'today
txtYear = Right(Year(Now), 1) '"year" portion of 4 digit Julian date
'create 4 digit Julain date with leading zeros for days 001 - 099
Julian = txtYear & Right("000" & DateDiff("y", txtStart, txtEnd) + 1, 3)
End Sub