Rolling serial number 1

[NavAmmo]

I'm trying to create a system, whereby an incrementing serial number is automatically assigned to each new entry.
The serial needs to increment from 001 to 999, and then "roll-over" back to 001. Duplicate entries are allowed, because the user will enter other identifying data that when combined the the serial will form a unique "document number". The key is that all "document numbers" must have the same number of digits, which is why my serial number cannot increment above 999. Any ideas?

 

[MoLaker]

First, your serial number field will have to be a text field to maintain the leading zeros.

It is relatively easy to increment by 1 based on a DLookUp of the highest number - then just add 1. But, in your case, you must start over after 999. So, I recommend using a date/time stamp field along with the serial number field. The DLookUp would then be to find the serial number with the lastest date and add 1 to the accompanying serial number.

Here's one possible method

Dim txtSN as string
txtSN = DLookup("your_sn_field_name", "yourtablename", "[sn_date]=#" & DMax("[sn_date]", "yourtablename") & "#")
If IsNumeric(txtSN) then
   If val(txtSN) < 999 then
   'add 1 to the value of the last SN if SN less than 999
   'and re-establish the leading zeros
   your_sn_field_name = right("000" & val(txtSN) + 1,3)
   Else
      'restart at 000 if last SN was 999
      your_sn_field_name = "001"
   End If
   'set serial number date field to current date/time
   sn_date = now()
Else
   'error handling for no number returned
End IF

 

[NavAmmo]

That's perfect MoLaker, I hadn't even thought of using a timestamp! I think this is going to do the trick!