Solved

Access Vba find auto number

Posted on 2016-08-30
4
32 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 8

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 8

Accepted Solution

by:
Anders Ebro (Microsoft MVP) earned 500 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 8

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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