-----Original Message-----
On my form I have a date field that automatically fills
in a date for the user when they open the form. What I
would like to know is it possible to have a textbox on
the form that would grab the month from the date field
and add a sequence of numbers. I want to stay away from
the autonumber option. (eg Mar-000001, Mar-000002 and it
would then appear in the textbox). What code would I use
to accomplish this?
Thanks in Advance for all the help
.
Here is the code I use to make a mission number based on
the date. The format is XXYYYZZZ
XX = Year
YYY = Julian Day
ZZZ = Next consecutive Mission Number
'Called by Fuction NextMissionID
Public Function MissionIDPrefix() As String
On Error GoTo Err_MissionIDPrefix
Dim DateFlightSchedule As Date
Dim JulieDay As String
Dim JulieYear As String
Dim JulieDate As String
' I broke this down here so that it's easier to
' see what it does. You could do it all on less
lines,
' and without so many variables.
DateFlightSchedule = Forms![frmSwitchboard]!
[txtDateFlightSched]
'get the
date from the form
JulieDay = Format(DateFlightSchedule, "y") 'make
the julian day portion
JulieDay = Format(JulieDay, "000") 'make it
a 3 digit text string
JulieYear = Format(DateFlightSchedule, "yy") 'make
the 2 digit year portion
JulieDate = JulieYear & JulieDay 'stick
them together
MissionIDPrefix = JulieDate 'return
a 5 digit text String
Exit_MissionIDPrefix:
Exit Function
Err_MissionIDPrefix:
Resume Exit_MissionIDPrefix
End Function
'Function will return the appropriate 8 digit, missonID.
'Format is YYDDDXXX. YYDDD is Julian date. XXX is 001 -
999.
'Considers values already in table tblDEVELOPMENT1
Public Function NextMissionID() As String
On Error GoTo Err_NextMissionID
Dim NextId As String
Dim MissionId As String
Dim XXX As String
If (IsNull(DLast("MissionId", "q_MissionId"))) Then
'runs a
query which returns
'all
[missionID]s beginning
'with
the first 5 digits determined
'in
MissionIDPrefix() from a date
'provide
d by the user in frmDEVELOPMENT1.
NextId = MissionIDPrefix() 'no
existing record, get prefix
NextId = NextId & "001" 'add
001 to string
NextMissionID = NextId 'return
new mission ID number
Else
MissionId = DLast("MissionID", "q_MissionId")
'is not
null so back to the same
'query
to get the the last entry for day.
XXX = Mid(MissionId, 6, 3) 'pull
charactors 6,7,8 from string
XXX = Format(XXX + 1, "000") 'add 1
and make sure its 3 digits
NextId = Left(MissionId, 5) 'start
to build missionID with left 5 dgits
NextId = NextId & XXX 'attach
the next 3 in sequence
NextMissionID = NextId 'return
a value.
End If
Exit_NextMissionID:
Exit Function
Err_NextMissionID:
Resume Exit_NextMissionID
End Function
