Solved

Access Vba find auto number

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

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 7

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 7

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

786 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