Finding Record ID of newly inserted record

[JMongi]

For background this is a drawing file/revision tracking table structure.

When I create a new record in my main table (tblDrawings) via a form, I also need to create default new records in tblDrawingRevision and tblRevisionStatus.
Running an INSERT INTO for tblDrawingRevision is not a problem. But, I need the record ID of the newly created record in tblDrawingRevision to put the new record into tblRevisionStatus.

Is there a way to grab the ID of the new record to use for creating the second record? Since I'm using SQL is there something in that language that facilitates this idea?

 

[Gasman]

I believe you were using sql server?

Identity function tutorial in SQL Server

This article explores the Identity function in SQL Server with examples and difference in these functions.

www.sqlshack.com

 

[JMongi]

I will be for our production database. But, this is a personal database so I was just going to run this on Jet/Access.
I shifted gears temporarily to some of these small personal databases so I can work through some of my form/query knowledge (like this question) before tackling the more complicated forms of the main database.

I'll read through your suggestion, though.

 

[Gasman]

I have only ever done this with a recordset?

        rs.AddNew
        rs!Surname = txtSurname
        rs!Initials = txtInitials
        strPKField = rs(0).Name                  'Find name of Primary Key (ID) Field
        rs.Update

        rs.Move 0, rs.LastModified
        intNewID = rs(strPKField)

 

[JMongi]

I have only ever done this with a recordset?

        rs.AddNew
        rs!Surname = txtSurname
        rs!Initials = txtInitials
        strPKField = rs(0).Name                  'Find name of Primary Key (ID) Field
        rs.Update

        rs.Move 0, rs.LastModified
        intNewID = rs(strPKField)

Interesting. Thanks!

So, I'm investigating a new trick I learned from examining one of @Pat Hartman sample databases. I expanded the underlying query to include tblDrawingRevision even though I don't need any of the fields linked to a form control. This should allow me to set the value of those fields via VBA before the form is completed. If I understand everything correctly, that should generate both new records and then that just leaves the third new record to be created via INSERT INTO in an AfterInsert form event.

If I'm right

 

[JMongi]

Well, I'm running into a snag with my post above. So, let's not confuse some poor future reader.

In my original idea of using two consecutive INSERT INTO commands I would need to insert some VBA code in between the two INSERT INTO commands. I would be using either DMAX (to find the highest record ID), or in my case I have composite candidate key in which I can use DLOOKUP to pull the specific ID of the record that matches that candidate key. I don't have tested working code but it should be simply a case of formatting the syntax of the DMAX or DLOOKUP command correctly. I'll circle back but I'm interested in figuring out post #5.

 

[The_Doc_Man]

The concept of relational integrity is important here. If you have a table on which other child-type tables depend, the RI rule is "no orphans." The parent must exist BEFORE the child records exist. Gasman's little code snippet does this by loading up the parent recordset's fields and then executing the .Update - but the recordset variable isn't moved yet. After that update, if there is an autonumber field, it has been loaded and can be seen. So the trick is to create the parent FIRST and allow it to update without navigating. At that point, the ID field should be usable.

Trying to create the child records first when there is a relationship between parent's prime key and the child table foreign key will always balk you.

 

[JMongi]

I believe that's what I was doing. The form creates record #1 (the parent record). Then the AfterInsert event would fire (due to it being a new rather than updated record) which would run an INSERT INTO for the first table and then an INSERT INTO on the second table. Unless I don't understand the nuts and bolts, the first direct SQL command should create the new record and then be accessible for the DMAX/DLOOKUP and then store that ID for use in the second INSERT INTO. But, I haven't had an opportunity yet to test it.

 

[JMongi]

Also, Welcome Back! Glad you are doing okay!

 

[JMongi]

I believe that's what I was doing.

Apparently not, since it doesn't really work! Ha! Ok, I was going to create a separate thread, but since @The_Doc_Man already opened it up to the more workings of everything it fits. Full disclosure, this could not be the "normal" way to handle this. So, feel free to correct that as well.

Here is the query underpinning the form:

The main tables are the tblDrawings and the tblDrawingRevision.

As @The_Doc_Man pointed out, even when I place the query fields from the child table on the form, Access won't let me update those fields. So, when does Access perform the new record insert of the parent record (tblDrawings)? Tied to this, are the event triggers (BeforeInsert, AfterInsert) related to the parent record? This is just a standard form.

 

[theDBguy]

Hi. Pardon me for jumping in... but, you should try to go back to using/implementing the @@IDENTITY function; especially, if you're planning to migrate your data into SQL Server in the future anyway.

 

[JMongi]

I read through the tutorial and I'm swimming in SQL that I hazily understand. To make sure I'm not trying to reinvent the wheel, here is the full table schema.

When creating a new record for tblDrawingRevision (via form of course) I want to create a default new record in tblDrawingRevision and a corresponding record in tblRevisionStatus. I put this out here just in case I'm approaching this all wrong.

I originaly created a basic query just based on tblDrawings and tblDrawingTypes with the idea of using SQL statements in the AfterInsert event of the form to create the default records. But, to create the tblRevisionStatus record would require the DwgRevID of the newly created record. My first attempt failed.

Then I thought of creating a bigger query with tblDrawingRevision but ran into a similar issue. I can't use VBA to set the field values from this query that come from the child record.

And here we are.

 

[theDBguy]

I read through the tutorial and I'm swimming in SQL that I hazily understand. To make sure I'm not trying to reinvent the wheel, here is the full table schema.

View attachment 94611

When creating a new record for tblDrawingRevision (via form of course) I want to create a default new record in tblDrawingRevision and a corresponding record in tblRevisionStatus. I put this out here just in case I'm approaching this all wrong.

I originaly created a basic query just based on tblDrawings and tblDrawingTypes with the idea of using SQL statements in the AfterInsert event of the form to create the default records. But, to create the tblRevisionStatus record would require the DwgRevID of the newly created record....and here we are.

Hi. Just to clarify, did you mean to say you are creating a new record in tblDrawings? If so, are you creating one new record at a time? In other words, you are not creating multiple new records at a time in the same table, are you? Because, you can only retrieve the ID for the last/newest record you just created (without using some other way to identify the new records).

 

[JMongi]

Correct. The user initiates a new drawing which pulls opens the form in DataEntry mode to create a single new record in tblDrawings. The default revision and reason are the same for all new drawings, as is the revision status. So, the user doesn't need to see or input anything (about revisions that is). The various child records just need created with the correct data in the proper order. I don't know if there is a "standard" way to deal with such child dependencies on new record creation.

 

[Pat Hartman]

I use the @@Identity with Jet/ACE also because most of my apps get converted to SQL Server and this is one less thing to change when that comes around.
Here's a snippet from the routine that imports a KSS file (a complex text file format with multiple record formats created by most drawing applications to document the environmental data for a drawing). Once the drawing "header" is added, the code then inserts the various different detail rows regarding revision, etc.

    rsD.AddNew
        rsD!JobID = JobID
        rsD!DrawingNum = vDrawingNum
        rsD!DrawingPfx = vDrawingPfx
        rsD!DrawingSfx = vDrawingSfx
        rsD!FullDwgName = CurFullDwgNum     '(vDrawingPfx + "-") & vDrawingNum & ("-" + vDrawingSfx)
        rsD!DrawingTypeID = 9      'default type imported from KSS file  
        rsD!Quantity = strDrec(5)
        rsD!AssemblyMark = strDrec(3)
        rsD!PartMark = strDrec(4)
        rsD!Desc = strDrec(11)
        rsD!UpdateBy = Environ("UserName")
        rsD!UpdateDT = Now()
        'DrawingID = rsD!DrawingID  'only works for Jet/ACE
    rsD.Update
    HoldDrawingID = db.OpenRecordset("SELECT @@Identity")(0)    'must be outside .AddNew for SQL Server

If you KNOW that you will NEVER convert the app, you can use the commented out line just before the update statement.

I tend to do this type of process with DAO rather than an append query because the commented out line only works with DAO. However, the Identity query should work regardless of how the record is inserted.

I'm not sure what sample of mine you were looking at but even though my forms are bound to queries and may include additional tables, the normal process is to ONLY update the primary table. In fact, the lookup tables are included just to show additional fields from the lookup tables and the controls for those fields are always locked to prevent accidental update.

Just FYI, I'm pretty sure that Jet/ACE and SQL Server differ on what you can do with updating when your query joins several tables. Access SQL allows it but SQL has limitations. It may be that columns can be updated in the lookup tables but rows cannot be added. I don't quite remember the details but consider the warning if the app will need to be converted.

 

[theDBguy]

Correct. The user initiates a new drawing which pulls opens the form in DataEntry mode to create a single new record in tblDrawings. The default revision and reason are the same for all new drawings, as is the revision status. So, the user doesn't need to see or input anything (about revisions that is). The various child records just need created with the correct data in the proper order. I don't know if there is a "standard" way to deal with such child dependencies on new record creation.

Hi. Thanks for the clarification. It definitely sounds like a perfect candidate for using the @@IDENTITY function. Cheers!

 

[JMongi]

SQL and Recordsets...I guess I'm expanding my knowledge today. I think I understand recordsets as a VBA way to deal with the data I just haven't had much opportunity to use them.

While I planned on keeping it simple by using Jet for this personal DB, it is the type of functionality that should eventually be in the production app so coding with an eye towards SQL server makes sense.

 

[theDBguy]

SQL and Recordsets...I guess I'm expanding my knowledge today. I think I understand recordsets as a VBA way to deal with the data I just haven't had much opportunity to use them.

While I planned on keeping it simple by using Jet for this personal DB, it is the type of functionality that should eventually be in the production app so coding with an eye towards SQL server makes sense.

Okay, if it will make it easier for you, you can skip recordsets for now. You should be able to use SQL to add the record and then get the last ID used/assigned. Here's an example:

Dim db As DAO.Database
Dim strSQL As String
Dim lngNewID As Long

Set db = CurrentDb()
strSQL = "INSERT INTO tblDrawings(Description) VALUES('Sample Drawing')"

With db
    .Execute strSQL, dbFailOnError
    lngNewID = .OpenRecordset("SELECT @@IDENTITY")(0)
End With

MsgBox "One new record was added to tblDrawings with the assigned new ID of " & lngNewID, vbInformation, "Done!"

Set db = Nothing

Hope that helps...

 

[JMongi]

Well, I don't want to skip anything if it's the preferred method. It may make my life a little more difficult but I've always been more interested in the full picture way to do something rather than the quick, get it done way.

 

[theDBguy]

Well, I don't want to skip anything if it's the preferred method. It may make my life a little more difficult but I've always been more interested in the full picture way to do something rather than the quick, get it done way.

SQL and Recordsets are almost equivalent. SQL usually works faster than Recordsets for processing multiple records. Since you'll only be processing one record, I can't say either one is the "preferred" method. It's your choice though. Cheers!