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", "[Code] = '" &
strStdNo & "' AND [Name] = '" & _
strStdName & "'")

Any suggestions?

"towdit" <(E-Mail Removed)> wrote in message
news:B167830E-CD46-41F9-8280-(E-Mail Removed)
> 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", "[Code]
> = '" & strStdNo & "' AND [Name] = '" & _
> strStdName & "'")
>
> Any suggestions?

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)

"Dirk Goldgar" wrote:

> "towdit" <(E-Mail Removed)> wrote in message
> news:B167830E-CD46-41F9-8280-(E-Mail Removed)
> > 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", "[Code]
> > = '" & strStdNo & "' AND [Name] = '" & _
> > strStdName & "'")
> >
> > Any suggestions?
>
> 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)
>
>
>
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

"towdit" <(E-Mail Removed)> wrote in message
news:A78874F2-11C4-453B-9992-(E-Mail Removed)
>
> 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.

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

(please reply to the newsgroup)