Access Vba find auto number

I am trying to find a search work which describes the scenario of running code but have not been successful

Scenario :
Insert a new row into a table
Retrieve auto number from above and insert this value from above .
There are no unique record except for time stamp . Do I use some Dbo code that can record the auto number recorded in the first insert by simply using Dbo commands . I am sure I have used it before but have lost my work .

In addition what syntax would you use to wrap the code so no inserts are partially done and if so it will roll back both inserts .

Who is Participating?
Anders Ebro (Microsoft MVP)Connect With a Mentor Microsoft DeveloperCommented:
From the access point of view, you can wrap it in a transaction. (note that you cannot use CurrentDB)
Dim dbTransactional As DAO.Database
      On Error Goto ErrHandler
      On Error GoTo ErrInTransaction
      Set dbTransactional = DBEngine.Workspaces(0).Databases(0)
      Dim rsTrans As DAO.Recordset
      Set rsTrans = dbTransactional.OpenRecordset("YourTable", dbOpenDynaset, dbSeeChanges)
Dim lPrimaryKey as Long
With rsTrans
  'Some stuff here I presume
  .Bookmark = .LastModified
End With
      Dim rsTransRelated As DAO.Recordset
      Set rsTransRelated = dbTransactional.OpenRecordset("SomeOtherTable", dbOpenDynaset, dbSeeChanges)

with rsTransRelated 
  'Other stuff I presume
End With
Exit Sub
  Msgbox err.number & " - " & err.description

Open in new window

Now the code above should give you a general idea of how it should work, but as it has been copied/cut and pieced together there might be missing pieces, or typos.
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
If adding by DAO code, the simplest way is to use the bookmark (Or at least that is what I do)
Dim rs as Dao.Recordset
Set rs=Currentdb.openrecordset("SomeTableOrQuery",dbopendynaset+dbSeeChanges)
  rs!Field2="Santa clause"
msgbox "Primary key is:" & rs!PrimaryKeyFieldName

Open in new window

yasanthaxAuthor Commented:

Thanks i was looking for a code that would wrap two SQL inserts into a transaction .
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
No action taking from OP
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.