DMax autonumber question (Cross-Post)

[johngordon]

I need some guidance.

How well would this autonumber do on a network?

= DMax ("[YourFieldName]","YourTableName") + 1

If I enter a record based on the above expression, and someone else on the network enters a record, does the next higher number come from the table or from their form?

I am thinking that it comes from their form, am I right?

Thanks,

John

 

[scriverb]

You are using conflicint terms here. If the field is a true AutoNumber field there is no need perform the DMax as ACCESS will do that for you. As soon as you enter the first piece of data in the new record on the form the next number is assigned to that record. If someone else is also entereing a new record with a similar form they will get the next number assigned as soon as they fill in their first enterable field. If the first user then cancels their new record for some reason then there will be a gap in the numbering scheme but this has no negative effect on the database. The AutoNumber type field will take care of the siatuation for you.

Now if you are creating your own numbering scheme and use the DMax of your field then there is a slight problem but you can overcome this by immediately saving the record upon the creation of the new record. Then the record has been assigned the next number and the user is just updating the fields for that record. If someone else creates another record using the DMax it will work properly and assign a new number which is saved immediately also. You may want to install some VBA code on the save command and trap for a dup record possibility and then just reincrement the number until a good one is accepted. The user wouldn't even know that it is happening. This last scenario is just put in place in case there was two records added at exactly the same instant.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jrbarnett]

John

Using the Autonumber field in a networked multiuser environment is not a good idea because of the possibility of conflict that you raise.
TT member MichaelRed has an excellent FAQ on how to overcome this problem here:

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

John

 

[johngordon]

Hi Bob,

Paragraph two answered my question. I just need to work on some VBA code now, to save and trap for a dup.

Thanks for you help!

John