Autonumber/ auto reference question

[joolsUK0575]

Hi there

I would like to build a database whereby when I create a new record it generates a new reference.

What I'd like is say the first record to be: SNN0001 and then when I create a new record the next record would be: SNN0002

How in the heck do I do this

Thanks for any help

Jools

 

[ColinEssex]

I would have a field for the text part and a field for the number part. You can concatonate them together in a query later if you wish.

Use the DMax() function to determine the last number used and add one to it to give the new number.

edit: don't use an AutoNumber

Col

 

[scouser]

New Record

OK. On the form add the following code to your On Current Event:

    Me!OrderID.DefaultValue = Nz(DMax("[OrderID]", "tblOrders"), 0) + 1
End If

Change the above values to whatever you have called your controls/table.

Format the control as follows: "SNN 000"&&&&&

That SHOULD do the trick!!
Phil.

 

[neileg]

I mostly agree with the previous posts.

Despite what Colin says, you could also use an autonumber instead. This makes the programming simpler (you just set the field type to autonumber) but what you can't do is guarantee that there won't be gaps in the sequence. If a users starts to create a record and abandons it while another user is also creating a record, you'll get a gap.

Be wary, though, of using DMax(). If you use this approach, you should set this as the last event when creating a record or use an explicit save after calculating this value, otherwise you could end up with two users holding the same value in their forms and getting a conflict when the second record is saved with the same value. You'll need to set this field as Indexed, no duplicates, to prevent you having two records with the same number.

Sequential number is not an easy subject!

 

[ColinEssex]

Despite what Colin says, you could also use an autonumber instead.

Yes you can, but personally, I prefer to keep AutoNumbers specifically for ID linking purposes and nothing else.

Col

 

[The_Doc_Man]

As you can gather from this discussion answering your problem, you have found a tricky little topic.

If you want to absolutely guarantee sequential numbering, you cannot use an autonumber field to concatenate with anything else because there is no way to guarantee uniform number assignment. As noted, you can have gaps.

If you absolutely MUST have this numbering system, the key you suggested appears to have meaning. In general, this implies that autonumber is not the proper choice because autonumber has no other meaning except by accident.

Therefore, the DMax function is your next choice. However, as noted, you have the problem of destructive interference if two users are in your system at the same time and happen to hit the SAVE button at the same time.

If you MUST have uniform number, put a special SAVE button on the form and put some VBA code underneath it to do the save. TRAP ERRORS IN THIS CODE! Then, if the save fails due to key issues, you can: Call DoEvents (look that up in the Help files to see what it does), RECOMPUTE the key, wait for a random amount of time, and try again. Perhaps generate a random large long integer and count it down as a way to delay for some amount of time. DO NOT USE THE FORM TIMER EVENT to wait for the random time. You do not want the form to appear to be available until you succeed in storing the record.