?
Solved

Access Vba find auto number

Posted on 2016-08-30
4
Medium Priority
?
38 Views
Last Modified: 2016-09-18
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 .

Thanks
0
Comment
Question by:yasanthax
  • 3
4 Comments
 
LVL 10

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41777254
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.addnew
  rs!Field1=1
  rs!Field2="Santa clause"
rs.AddNew
rs.Bookmark=rs.LastModified
msgbox "Primary key is:" & rs!PrimaryKeyFieldName

Open in new window

0
 

Author Comment

by:yasanthax
ID: 41777670
Hi

Thanks i was looking for a code that would wrap two SQL inserts into a transaction .
0
 
LVL 10

Accepted Solution

by:
Anders Ebro (Microsoft MVP) earned 2000 total points (awarded by participants)
ID: 41777689
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
      DBEngine.Workspaces(0).BeginTrans
      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
  .AddNew
  'Some stuff here I presume
  .Update
  .Bookmark = .LastModified
  lPrimaryKey=rs!YourAutoNumberField
End With
      Dim rsTransRelated As DAO.Recordset
      Set rsTransRelated = dbTransactional.OpenRecordset("SomeOtherTable", dbOpenDynaset, dbSeeChanges)

with rsTransRelated 
  .AddNew
    !ForeignKeyField=lPrimaryKey
  'Other stuff I presume
  .Update
End With
dbEngine.Workspaces(0).CommitTrans
Exit Sub
ErrHandler:
dbEngine.Workspaces(0).RollBack
  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.
0
 
LVL 10

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41803588
No action taking from OP
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question