Incrementing an ID field

[Guest]

I am setting up an application for an existing SQL database using Access to
update/enter new records. When I enter new records on any of the forms I want
to get Access to calculate the next ID no ie if the last was 1256 then I want
Access to insert the value 1257 into the ID field when I go to a new record.
The SQL statement I would run in SQL would be ' User_ID = (Select
Max(user_ID) +1)' on the appropriate table. I don't know which Event to put
the statement in on or whethermy code is correct.

Can anyone help me?

Drew

 

[Guest]

The code is okay, I think, but you will get an error on the first record you
try to insert into the table because User_ID = (Select Max(user_ID) +1) will
return Null if there are no records in the table. This will correct that:
User_ID = Nz((Select Max(user_ID),0) +1)

It goes in the Before Update event of your form.

 

[RuralGuy]

In VBA Help look up the Dmax function.

I am setting up an application for an existing SQL database using Access to
update/enter new records. When I enter new records on any of the forms I want
to get Access to calculate the next ID no ie if the last was 1256 then I want
Access to insert the value 1257 into the ID field when I go to a new record.
The SQL statement I would run in SQL would be ' User_ID = (Select
Max(user_ID) +1)' on the appropriate table. I don't know which Event to put
the statement in on or whethermy code is correct.

Can anyone help me?

Drew

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

Ok, I have tried this but it is now returning a NIL value so am trying to
attack this a different way. I have created a query to return the correct
value but am having trouble referencing it in the form ie.

Me!PROBLEM_ID = [Query name]

Can anyone help?