Automatically inserting next ID number

[jasemckee]

Hello all,

How do I go about setting up a form so that when I go to insert a new entry, the next ID number (running in numerical order) is automatically inserted, rather than typing it in.

Thanks for your help.

Jase.

 

[nat1967]

HI Jase,

Are you storing the record (ID) number in a table? I am assuming you are. You dont want to use autonumber. If records ever get deleted, your number sequence could and would be way off.

I am using the following method for tracking in a issue log my testers use. Basically, when the testers open a new record in the issues log, this code checks to see if there are any known (existing) issues logged against a change request. If there are no existing records, the form starts when 1. If there are existing records, the code gets the highest value in the issue number log and adds 1 to it. It creates a numeric record like 1, 2, 3, etc...

heres the code:

'****begin code****
Dim strNewRecord As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

'**build an sql query to use for recordset of the issues
'**form. This will determine if there are any existing
'**issues.
strNewRecord = "SELECT * FROM tblissuelog " _
& " WHERE [feature] = '" & Forms!frm_UATInput!FEATURE & "'" _
& " and [testcase] = '" & Forms!frm_UATInput!Acceptance & "'"

'**use above sql to open recordset
Set rs = db.OpenRecordset(strNewRecord, dbOpenDynaset)

'**if recordcount is 0, then start counting at 1
If rs.RecordCount = 0 Then
Me!issue = 1
Exit Sub
Else
'**if records exist, get highest existing value
'**and increment 1
Me!issue = DMax("[issue]", "qry_issuenewlookup&quot + 1

End If

'*****end code*****

I hope this example helps.

Nathan
Senior Test Lead