Calculating a number everytime u click add record

[bigdrewecu]

ok i need a way to to calculate a primary key value everytime you add a record starting from 13261. The reason being is that that is the last number in the database for primary keys and it was originally not an autonumber field. So far i think i have to do it on form level in the add record button's click event with a loop and setting the textbox to the calculated value. Anyone have any ideas or suggestions

 

[scriverb]

Yes, this is one way to do this but you must be careful because if two users are doing it at precisely the same time they would get the same number. You should incorporate a save record command at the very time you calculate your next PK field number. A DLookup of the Max value from the table is an easy way to do this.

Another way is to not assign this number until you perform the save command after all data is added to the record. This is necessary to do at the end if there are required fields that would not allow you to save at the beginning when you initially perform the Add record command.

If you need assistance with the code to do this I can help. Just post back with a little bit of details about the table and fields involved.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[bigdrewecu]

only one user will be doing data entry on the form

 

[bigdrewecu]

the table involved would be simple address info with a primary key which was generated from the old database that i converted from paradox.....the numbers run from 10004 to 13261 and i just need it to so that when the click add new record it increments the primary key by one ie the next record would be 13262 and so on.

 

[scriverb]

Okay, then. In the command button that you are clicking to create the new record add the following after the new record command.

Me.[[i][red]primarykeycontrol[/red][/i]] = DMax("[[i][red]primarykeyfield[/red][/i]]", "[i][red]tablename[/red][/i]") + 1

That should do it. Just update the appropriate names of control, field and the table names.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[MichaelRed]

for the 'complete' (multiuser) soloution see faq 700-184





MichaelRed
mlred@verizon.net