I am trying to do something like the following in a query:
Dim rs As RecordSet
Dim NewPrimaryKey as Long
Set rs = Currentdb.OpenRecordset("SELECT * FROM MyTable WHERE MyPrimaryKey Is Null;")
NewPrimaryKey = !MyPrimaryKey
!DateValue = Now()
You can use two SQL statements to accomplish what I think you want. First an INSERT. Then "SELECT @@Identity" to get the last added autonumber value. Use an object variable for the database connection with both SQL statements.
Dim db As DAO.Database Dim NewPrimaryKey As Long Dim strInsert As String strInsert = "INSERT INTO MyTable ([DateValue])" & vbCrLf & _ "VALUES (Now());" Set db = CurrentDb db.Execute strInsert, dbFailOnError NewPrimaryKey = db.OpenRecordset("SELECT @@Identity")(0) Debug.Print NewPrimaryKey Set db = Nothing
I enclosed the field name DateValue in square brackets because it is a reserved word.
Edit: If you insert multiple records with one SQL statement, SELECT @@Identity will still give you the last autonumber. It's the last autonumber for inserts performed through that connection instance. And you don't get a sequence of the autonumbers used; only the last one.
strInsert = "INSERT INTO MyTable3 ([some_text])" & vbCrLf & _ "SELECT TOP 3 foo_text FROM tblFoo" & vbCrLf & _ "WHERE foo_text Is Not Null ORDER BY foo_text;"