Entry

In my SQL statements, how can I get the "next value" generated by an autonumber field in my Access database ?

Jan 16th, 2002 12:43
Mike DiBenedetto, Kit K,

I had problems inserting into a table that had an autonumber field in 
Access. To fix the problem, I changed the field to a number and used 
this code.

Sub MyAutoNum

'Take an existing counter or autonumber column
'and store all values in a recordset

SQLCmd = "SELECT Primary_ID FROM NewRequest"
Set objAutoNum = Server.CreateObject("ADODB.Recordset")
Set objAutoNum = objConn.Execute(SQLCmd)

'Move through the entire recordset and store the last value

Do While Not objAutoNum.EOF
	lastnum = AutoNum("Primary_Id")
	objAutoNum.MoveNext
Loop

'Increment the last value by one to get a new unique number

newnum = lastnum + 1

End Sub

Hope this helps