?
Solved

Access Vba find auto number

Posted on 2016-08-30
4
Medium Priority
?
36 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 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 8

Expert Comment

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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

771 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