[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access Vba find auto number

Posted on 2016-08-30
4
Medium Priority
?
37 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 9

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 9

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 9

Expert Comment

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

656 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