Cannot find a record after an insert statement

[towdit]

Hi,

I am using VBA to insert a new record in a table that has an autonumber as
the primary key. After the insert, I do dlookup on the table to retrieve the
primary key of the newly inserted record so that I can add it to a relation
table. However, the issue I am having is that when I do the lookup, it cannot
find the record in the table.
Here is a portion of the code I am using:
' Add the competency element entry to the tblCompetencyElement table.
Set db = CurrentDb()
strSQLQuery = "INSERT INTO tblCompetencyElement (Code, Name)
VALUES " & _
"('" & strStdNo & "', '" & strStdName & "')" & Chr(10)
Debug.Print strSQLQuery
Call db.Execute(strSQLQuery)

' Now get the unique id of the entry just added.
varX = DLookup("[CEID]", "tblCompetencyElement", "

 = '" &
strStdNo & "' AND [Name] = '" & _
strStdName & "'")

Any suggestions?

 

[Dirk Goldgar]

Hi,

I am using VBA to insert a new record in a table that has an
autonumber as the primary key. After the insert, I do dlookup on the
table to retrieve the primary key of the newly inserted record so
that I can add it to a relation table. However, the issue I am having
is that when I do the lookup, it cannot find the record in the table.
Here is a portion of the code I am using:
' Add the competency element entry to the tblCompetencyElement table.
Set db = CurrentDb()
strSQLQuery = "INSERT INTO tblCompetencyElement (Code,
Name) VALUES " & _
"('" & strStdNo & "', '" & strStdName & "')" & Chr(10)
Debug.Print strSQLQuery
Call db.Execute(strSQLQuery)

' Now get the unique id of the entry just added.
varX = DLookup("[CEID]", "tblCompetencyElement", "

= '" & strStdNo & "' AND [Name] = '" & _
strStdName & "'")

Any suggestions?[/QUOTE]

I don't see anything particularly wrong with that, though I don't
normally do it that way.  I'd be more likely to do it like this:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM tblCompetencyElement WHERE False")

With rs
.AddNew
!Code = strStdNo
!Name = strStdName
.Update
.Bookmark = .LastModified
varX = !CEID
.Close
End With

Set rs = Nothing

That said, I'd expect the way you're going about it to work, and it does
in my simple tests.  Maybe it's a timing problem.  If you set a
breakpoint after the db.Execute call, let the code break there, and then
let it continue, is the record found?

 

[towdit]

Hi,

I am using VBA to insert a new record in a table that has an
autonumber as the primary key. After the insert, I do dlookup on the
table to retrieve the primary key of the newly inserted record so
that I can add it to a relation table. However, the issue I am having
is that when I do the lookup, it cannot find the record in the table.
Here is a portion of the code I am using:
' Add the competency element entry to the tblCompetencyElement table.
Set db = CurrentDb()
strSQLQuery = "INSERT INTO tblCompetencyElement (Code,
Name) VALUES " & _
"('" & strStdNo & "', '" & strStdName & "')" & Chr(10)
Debug.Print strSQLQuery
Call db.Execute(strSQLQuery)

' Now get the unique id of the entry just added.
varX = DLookup("[CEID]", "tblCompetencyElement", "

= '" & strStdNo & "' AND [Name] = '" & _
strStdName & "'")

Any suggestions?[/QUOTE]

I don't see anything particularly wrong with that, though I don't
normally do it that way.  I'd be more likely to do it like this:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM tblCompetencyElement WHERE False")

With rs
.AddNew
!Code = strStdNo
!Name = strStdName
.Update
.Bookmark = .LastModified
varX = !CEID
.Close
End With

Set rs = Nothing

That said, I'd expect the way you're going about it to work, and it does
in my simple tests.  Maybe it's a timing problem.  If you set a
breakpoint after the db.Execute call, let the code break there, and then
let it continue, is the record found?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
[/QUOTE]
Dirk,

Thanks for your prompt reply. I agree that it seems to be a timing issue as
the error does not always occur. The database resides on a network server and
I am accessing it from my workstation over the LAN. If I step through the
code with the debugger I don't get the error, further confirming a timing
issue. Is there a way I can commit the change prior to looking up the primary
key of the new entry?

Thanks

 

[Dirk Goldgar]

Thanks for your prompt reply. I agree that it seems to be a timing
issue as the error does not always occur. The database resides on a
network server and I am accessing it from my workstation over the
LAN. If I step through the code with the debugger I don't get the
error, further confirming a timing issue. Is there a way I can commit
the change prior to looking up the primary key of the new entry?

You might try inserting the line

DBEngine.Idle dbRefreshCache

after executing the query and before your DLookup.