unique numbers questions

  • Thread starter Thread starter D McM
  • Start date Start date
D

D McM

I am working on a license program in Access 2000. Every year, the county
assigns a block of numbers to be used. When someone applies for a license,
they are assigned from that group. For example, the block of number for
2002 was 11451 to 11800. The starting number was 11451, so the first person
who applied for the license got that number. The next person got 11452.
Each year, the block of number changes. So this year, the numbers start
with 19001. When the person enters in the information on a form, the next
License number will be assigned. How do I do this?

Thank you for your help
Katie
 
This function would return the next largest value. Obviously, you'll have to
insert your own table and field names

Function NextID() As Long
Dim lngID As Long

lngID = DMax("YourField","YourTable")
NextID = lngID + 1

End Function
 
As a followup (and something whic I forgot to mention), this code *could*
generate some troubles in a multiuser environment. Say, for example, UserA
requests this function and receives a new, incremented number (12345) ...
then UserB does the same thing BEFORE UserA saves this data to the database
.... both users would receive the SAME number, which would obviously be a
violation. You'd have to determine how best to handle this (perhaps by
retreiving and writing this number as the new record is being saved instead
of while the user is editing a new record, using a transaction, somehow
locking the key generation table, using a push-pull stack, or some other
method). Just some random thoughts ...
 
Back
Top