I'm creating a table with a field call tracking but the data type not
allowing me to customize that field. I like to see this automaticly
every time a new record is enter.
05001
05002
For a start it's really not a good idea to combine these two separate
bits of information into a single field. If you "like to see" it looking
like that, then it's not hard to arrange it in a control on a form or a
report.
You need two fields, and integer called YearNum and a text field called
"SerialCode". It would be simpler to have the serial number wrapping from
009 to 010 rather than 00A, but including the letters just takes a little
bit more code.
You also need to make sure that you can control all record creation, and
manage this with code behind a form. No table datasheets, no Excel, no
append queries etc. The code would need to do something like:
' pick the right year somehow
' if it's already in a text box you can read it from there,
' otherwise hack it out of today's date...
sqlCriterion = "YearNum = " & Format(Year(Date()) Mod 100, "00")
' get the largest one so far
varMaxSerial = DMax("SerialNum", "MyTable", sqlCriterion)
' see if it got any answer
If IsNull(varMaxSerial) Then
' no; put a zero in the text box
me!txtSerialNum = "000"
else
' yes: increase the value by one before putting it in
' you can probably write your own function to increment the
' text string
Me!txtSerialNum = NextSerialNumber(varMaxSerial)
End If
For more information, try googling for Access Custom Autonumbers. There
is good code on Dev Ashish's site
http://www.mvps.org/access/ and lots of
other places. Note too that this is not strictly safe for multiuser
setups, if there is a chance that two users may be creating a record at
the same time.
Hope that helps
Tim F
