DLookup after AddNew

[Rance]

In my app, I create a new record in a table then
immediately do a DLookUp on that record to get the primary
key (which is an AutoNumber data type) so that I can pass
that value to a function. Intermitently, the Dlookup fails
even though the record was created.

What can I do to make sure that I will always be able to
get the value of the primary key on the record just
created?

 

[Tim Ferguson]

What can I do to make sure that I will always be able to
get the value of the primary key on the record just
created?

The safest way to do this is to use DAO to create the record in the first
place:

Public Function GetNewMyTableID() As Long

' set up an empty query to save network bwidth
strSQL = "SELECT * FROM MyTable WHERE FALSE;"

' open it as a dynaset
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

' create a new record
rs.AddNew

' any other required fields go here...

' recover the number
GetNewMyTableID = rs!IDNumber

' finished with objects...
rs.Update
rs.Close

End Function


HTH


Tim F