increment number with command button

[noonan1]

I have a project input form with project id as the primary key in the table where the values are stored.The id is a number and I would like to be able to add a new id by clicking on a command button that would automatically increment the last value entered.Id really appreciate it if you could help me, thank you in advance.

 

[Nikita6003]

Hi noonan1

guess you're using Access
If the number in your table isn't an AutoNumber, then perhas this might work:
What I do in one of the apps I wrote is to retrieve the MAX(ID) using som dynamic SQL, add 1 to it & use that as my new ID. This way, I *know* I'm not going to be violating the PrimKey.
Assuming you're using DAO (but it'll work just as well in ADO - just sub the Database pobject with a Connection object), try this (btw - I'm doing this the long way; you 8can* code this up in fewer lines):

Dim l_dbDatabase As DAO.Database
Dim l_rsGetID As DAO.Recordset
Dim l_sSQL As String
Dim l_lNextID As Long

'Set database to be the current db; you can use the CurrentDB object instead. Saves a few code lines
Set l_dbDatabase = CurrentDb
'Create SQL to get the max ID number in your table
'Replace field name / table name with your versions
l_sSQL = "SELECT MAX([IDFieldnameHere]) As MaxID FROM tblToGetIDFrom "
Set l_rsGetID = l_dbDatabase.OpenRecordset(l_sSQL)
l_lNextID = l_rsGetID("MaxID") + 1

'Release objects - neater & less chance of memory problems / phantom threads
Set l_rsGetID = Nothing
Set l_dbDatabase = Nothing

HTH - if you are using a different app or need/want more info just let us know
Ain't parapenting yet ;-)

Cheers
Nikki

 

[HobbitK]

Nikki ..
You need to do that Parapenting and let us know about it.
I think I want to try it
Michael

 

[Nikita6003]

will let you know March 10th unless unavoidably detained

"look mommy, I'm flying!"

*splat*







Cheers
Nikki

 

[noonan1]

Thank you all for your help the problem has been resolved using Dmax as the default value for the issue item number.