Assign Autonumber of choice

[fireman_gcfd]

Is there a code that anyone knows of to assign a number to a form via command button as opposed to having access assign it?

To clarify, once the form is complete, the user will click the command button which will assign the number (e.g. 2010-001) to the form, save and close. Then the next user will open new form and when clicked the number will be sequential to the last (e.g. 2010-002).

Thanks
Jaz

 

[John Big Booty]

You can do this by using the Dmax() function to increment your own counter.

It would look somthing like;

Me.CounterField = Nz(DMax("CounterField", "TableName"), [B][COLOR="Purple"]Seed Value[/COLOR][/B]) + 1 [COLOR="SeaGreen"]'Replace[/COLOR] [B][COLOR="Purple"]Seed Value[/COLOR][/B] [COLOR="SeaGreen"]with your desired start value[/COLOR]

The attached DB uses the Form's On Current event but you could just as easyly fire it from a button's On Click Event

 

[georgedwilkinson]

What happens if 2 people push the button at or near the same time?

 

[fireman_gcfd]

There will only ever be one user at a time. So that scenario isnt a concern for me. If anyone has the same idea of using it, then there could be issues for them.

 

[gemma-the-husky]

i take it you dont mean an AUTONUMBER - you mean a number.

personally i would have both. In general use an autonumber, and dont bother what number access assigns - BUT if you need a controlled number, than have this as well - and manage it with a suitable technique.

it is important to appreciate that the (secret) autonumber should be actually used as the FK in other tables, to link to your main table.