Solved

Access 2010: Use ID of inserted record in VBA code

Posted on 2014-04-07
11
3,589 Views
Last Modified: 2014-04-09
I have created the following insert statement and it is working.

    strQuery = "INSERT INTO tblDocuments(DocumentControlNumber, ExistingControlNumber, DocumentName, FileName, CreateDate, DepartmentID, AuthorID, OwnerID, EPD, Casters, Hinges, Other, ProcedureObjective, IsProcess, IsWorkInstruction, IsForm, IsMisc, DocStatus, RevisionDate, Revision) " & _
                "SELECT tblDocuments.DocumentControlNumber, tblDocuments.ExistingControlNumber, tblDocuments.DocumentName, tblDocuments.FileName, " & _
                "tblDocuments.CreateDate, tblDocuments.DepartmentID, tblDocuments.AuthorID, tblDocuments.OwnerID, tblDocuments.EPD, tblDocuments.Casters, tblDocuments.Hinges, tblDocuments.Other, " & _
                "tblDocuments.ProcedureObjective, tblDocuments.IsProcess, tblDocuments.IsWorkInstruction, tblDocuments.IsForm, tblDocuments.IsMisc, tblDocuments.DocStatus, Date() AS RevisionDate, '00' & Val([tblDocuments.Revision])+1 AS Revision " & _
                "FROM tblDocuments " & _
                "WHERE (((tblDocuments.DocumentID)=" & Me.SearchResults.Column(0) & "));"
   
    MsgBox (strQuery)
    dbData.Execute strQuery

I would like to use the ID from this record in additional queries after this.  How do I assign the ID for the last record inserted to a value which can be used?
0
Comment
Question by:btgtech
  • 4
  • 4
  • 2
  • +1
11 Comments
 

Expert Comment

by:kevinrea
ID: 39984848
why don't you just have a hidden text field and when you create that last record, it also copies the index number to the hidden text field.
then, you can just get the value of that hidden text field and use it as you need to.

kevin rea
Lancaster, Calif.
0
 

Author Comment

by:btgtech
ID: 39984859
This code is developed for a button on the form.  I am writing code to
1. Copy a record to a new version of the record
2. Using the ID from the new record,
     - Copy the records from a sub table (s) to a new version of the record in the sub table(s)

I was not planning on going back to the form.

What code would you use to identify the last record?
0
 

Expert Comment

by:kevinrea
ID: 39984864
just add a new field to a table, and then have that field in the form you use to create the new record.
then just refer to the table and that field to use in the future.

kevin rea
0
 

Author Comment

by:btgtech
ID: 39984882
There is already an autonumbered field in the table.  I just want to know how to query and assigned the autonumbered value to a value that I can use.
0
 
LVL 3

Accepted Solution

by:
Oliver Wastell earned 500 total points
ID: 39985403
You might want to rethink the way you have implemented this.  When I am looking to obtain the id of a new record I work with recordsets, for example:

    Dim db As DAO.Database
    Dim rsCustomisationRate As DAO.Recordset
    Dim sqlCustomisationRate As String
    Dim lngID As Long

    sqlCustomisationRate = "SELECT tblCustomisationRate.idCustomisationRate, " & _
			 "tblCustomisationRate.fkWorkshop , " & _
			 "tblCustomisationRate.fkCustomisationType, " & _
			 "tblCustomisationRate.customisationRate , " & _
			 "FROM tblCustomisationRate " & _
			 "WHERE tblCustomisationRate.idCustomisationRate=0"

    Set db = CurrentDb()
    Set rsCustomisationRate= db.OpenRecordset(sqlCustomisationRate dbOpenDynaset)
    With rsCustomisationRate
        .AddNew
            !fkWorkshop = Me!cmbFkWorkshop
            !fkCustomisationType = Me!cmbFkCustomisationType
            !customisationRate = Me!txbCustomisationRate
            lngID = !idCustomisationRate
        .Update
        .Close
    End With
    Set rsCustomisationRate = Nothing
    Set db = Nothing

Open in new window


The id of the new record is then stored here in lngID.  Obviously this is a snippet of code taken from one of my existing projects but hopefully you can see how you could tailor it for your own purposes.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

Expert Comment

by:PatHartman
ID: 39986663
I agree with Oliver.  Using a recordset is the best solution.  However, if your tables are SQL Server or some other RDBMS, you need to change the technique a little since unlike with Jet/ACE, the autonumber (identity) column isn't available until AFTER the rs.Update.  For SQL Server, et al, you will need to use the @Identity method to retrieve the new identity column value.  See http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ for details
0
 

Author Comment

by:btgtech
ID: 39987479
If I have a field that I would like to assign today's date to - what function can be used in access to do this?
0
 

Author Comment

by:btgtech
ID: 39987743
I have the DAO process working.

Now I would like to use the DocumentID to query and update 8 other tables.  The other tables may have multiple records related to the documentID in then and now we need to have the same records assigned to the new DocumentID.

Thoughts?
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 39987841
For the date simply use Date(), or for current date / time use now().
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 39987849
If you need to make changes to existing records you'll probably want an UPDATE query and to add new ones you'll need an APPEND query. Design your queries in the query builder then cut and paste the sql into a VBA procedure. Then alter the sql string to pull in the new documentID in the appropriate place(s). You can then execute the sql strings using the method in your first post.

Happy to provide additional information if required but perhaps this should be done under a new question (post the link for the new question here) as your original issue I think has been resolved?
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 39988410
btgtech:
Thanks for the points and grading.  If you do post an additional question I'll take a look upon reaching my holiday destination in the next 48 hours if no one else has answered it in the meantime - just leaving the office now.  Thanks again.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 …
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now