assigning #s based on a field selection, need 'b4 update' expressi

[Gingertrees]

In my library database, I've set up a table of topics and their base-# like
this:
ID = 1
TopicName = Parenting
Shelf Number = 120
I linked this table to my book entry table/form, such that when entering a
new book, I could select "parenting" as my topic, and have the computer
assign my book a
120.--- number (120.001,120.002 etc), BASED on 120.--- numbers that already
exist in the database (such that I have no duplicates with existing data).

Another user told me "If you are adding a new record, you need to assign one
more than the last ShelfSequence used. This can be done in the 'before
update' event for the form. "

What sort of expression do I need for the 'before update' event in the form?
I'm not great with building expressions.
~G

 

[mscertified]

Look up the DMAX function in Access help. This will give you the greatest
value in a column. So you just apply this function to your 'shelf sequence'
column and then add 1.
If this is a multi-user system, you will have to save your record
immediately otherwise another user might get the same number.

The whole subject of generating sequence numbers is fraught with problems
because in multi-user systems the same number could be assigned twice and if
a user cancels out of an update it's possible the sequence could end up with
gaps.

Dorian