none
returning the record id back to form RRS feed

  • Question

  • Hello, I am hitting a wall trying to get the newly inserted record's id number to return back to the form.  Below is the code that I have been working with.  At one point I got zero(0) back but obviously that wasn't correct.  The tables are linked.  Using ADO on this project.  The 'conn' is the connection string that goes straight to the db.  The code below is part of the SAVE button when inserting a new record.  Other than what is below, all the other code in the routine works fine.  A breakpoint and step thru shows there is 'null' listed for the rst value even tho the db shows the record and the autonumber (recordid).  The treeview gets updated with the new location and if I click on the new inserted location the company id will show.

    Would like someone who knows what they are doing (as it is apparent I do not) to see what else needs to be done to get the companyid number back to the form.

    Thank you....John

            '"SELECT @@Identity" is for getting the CompanyID and return to form
            Dim db As New ADODB.Connection
            Dim rst As ADODB.Recordset
                    
            db.Open conn
            
            Set rst = db.Execute("SELECT @@Identity")   ', , adCmdText)
    
    '        sqlID = "SELECT @@Identity"
            
    '        rst.Open sqlID, conn, adOpenStatic
            Me.txtCompanyID = rst.Fields(0).Value
    
            '===============================
    

    Monday, January 25, 2016 4:18 AM

Answers

  • Hi John,

    Based on my understanding, if you want to get the last-inserted identity value via the system function @@IDENTITY, we'd better combine the insert SQL with this function in the same transaction so that the code can work for the multiple user environment.

    If you were get the last-inserted identity value in different transactions, we can get the identity value based on the values of fields we inserted before.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 26, 2016 2:11 AM
    Moderator

All replies

  • Hi. I don't use ADO, but it might be the same with DAO when it comes to this issue. Try retrieving the ID using the same connection as the one used to create the new record. In DAO, here's what it might look like:

    With db
      .Execute "INSERT INTO...", dbFailOnError
      Me.txtCompanyID = .OpenRecordset("SELECT @@IDENTITY")(0)
    End With

    Hope that helps...

    Monday, January 25, 2016 4:26 AM
  • Thanks for the reply.  Nope, didn't work.  I tried a variation of it earlier as well with no results.

    ...John

    Monday, January 25, 2016 4:40 AM
  • Hi John. Sorry to hear that. Did you try it using DAO or ADO? If ADO, can you please post the complete code? Thank you.
    Monday, January 25, 2016 5:26 AM
  • It is ADO.  The forms and controls are all unbound.  Here is the entire SAVE button code.  The ELSE part is for updating the record and has not been worked on yet.  Need to solve this issue first.  The part in question is between the lines.  I considered using a stored proc but need to see if it can be resolved first.

    ...John

    Private Sub btnSave_Click()
    On Error GoTo btnSave_Click_Error
    
        Dim sqlInsert As String
        Dim sqlUpdate As String
        
        
        If IsNull(Me.txtCompanyID) Then
            
            sqlInsert = "INSERT INTO dbo_tblCompany(StatusID, ShippingMethodID, ParentID, " & _
                                                "Name, Telephone, Fax, Website, EmailAddress, " & _
                                                "IsCorporate, IsLocation, IsHeadquarters, IsSubsidiary, IsRemove, " & _
                                                "DateAdded, FTStaff, PTStaff, TempStaff, InternStaff, ContractStaff, " & _
                                                "Comment) " & _
                        "VALUES(cboStatus, cboShippingMethod, cboParent, " & _
                            "txtName, txtTelephone, txtFax, txtWebsite, txtEmail, " & _
                            "chkIsCorporate, chkIsLocation, chkIsHeadquarters, chkIsSubsidiary, chkIsRemove, " & _
                            "txtDateAdded, txtFTStaff, txtPTStaff, txtTempStaff, txtInternStaff, txtContractStaff, " & _
                            "txtComment)"
    
            DoCmd.SetWarnings False
            DoCmd.RunSQL sqlInsert
            DoCmd.SetWarnings True
    
            MsgBox "Information has been ADDED.", vbOKOnly, "New Record Added"
            
            '===============================
            
            '"SELECT @@Identity" is for getting the CompanyID and return to form
            Dim db As New ADODB.Connection
            Dim rst As ADODB.Recordset
                    
            db.Open conn
            
            Set rst = db.Execute("SELECT @@Identity")   ', , adCmdText)
    
    '        sqlID = "SELECT @@Identity"
            
    '        rst.Open sqlID, conn, adOpenStatic
            Me.txtCompanyID = rst.Fields(0).Value
            
            '===============================
            
            rst.Close
            Set rst = Nothing
            
            Application.Echo False
            
            Call BuildTreeview  'building the treeview
                
            Call ParentUpdate   'fills the parent
            
            Application.Echo True
                
        Else
            MsgBox "Updated existing"
            
    '        sqlUpdate = " "
    '
    '
    '        DoCmd.SetWarnings False
    '        DoCmd.RunSQL sqlInsert
    '        DoCmd.SetWarnings True
    '
    '        MsgBox "Information has been UPDATED.", vbOKOnly, "Record Updated"
    '
    '        Me.tvwCompanyStructure.Requery
    
        End If
        
    
    On Error GoTo 0
    Exit Sub
    
    btnSave_Click_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_frmCompany"
    
    End Sub
    

    Monday, January 25, 2016 6:00 AM
  • I might have gotten to work properly with the ADO.  I redid all the code again to see if there was anything wrong and then began adding pieces of it back in.  Baby steps.  Need now to clean up the code and see if it continues to work.  

    ...John

    Monday, January 25, 2016 2:30 PM
  • Hi John,

    Glad to hear you got it sorted out. But regarding your original code, as I have mentioned, you were creating the new record using a separate connection (DoCmd.RunSQL) than the one you used to retrieved the ID number (ADODB.Connection). I think I could fix that, but I'll just leave it alone since you already have a working solution. Good luck with your project.

    Monday, January 25, 2016 3:50 PM
  • DB, I'm also open to new and better and different ideas.  How would you fix it (or change it)?

    ...John

    Monday, January 25, 2016 6:35 PM
  • Hi John,

    Okay, the idea is to grab the ID as the record is created - not after. So, in pseudo code, I would just use the recordset method and skip the SQL one. For example:

    With rs
       .AddNew
       !FieldName = SomeValue
    ...
       Me.txtCompanyID = !CompanyID
    ...
       .Update
    End With

    That would be just one way of doing it though. There are still other ways to do it.

    If that doesn't work with ADO (like I said, I don't use it much), you might do it this way:

    With rs
       .AddNew
       !FieldName = SomeValue
    ...
       .Update
       .LastModified
       Me.txtCompanyID = !CompanyID
    End With

    Hope that helps...

    Monday, January 25, 2016 7:09 PM
  • Interesting.  I might just give those a try to see how it flows.  But, here is what I ended up doing.  The insert statement in the prior post was shortened so as to work with it easier.  It's all test data anyway that is going into the table.  The table gets cleaned later.

    So, I removed the 3 DoCmd lines and replaced it with the code below.  This got the ID number back to the form after the insert.  The 'conn' bypasses the linked table and goes straight to the db table.  Actually, the table wouldn't even need to be linked.  Originally, the tables weren't linked but had a problem with the treeview so I linked the Company table.

    Now my issue is with the Insert statement once I added all the fields back in and the values.  I get an error (below).  Not sure what is causing this.  The debug.print shows the info in the fields.  Even added to the fields and values one at a time and still got this.  A message box shows the information as well.  My first thought is do I have the values marked with quotes correctly.

    Anyway, enough time spent.  Going down to the local truck driving school to see if they got a driving opening for me.  

    Later...John

    

            '"SELECT @@Identity" is for getting the CompanyID and return to form
            Dim db As New ADODB.Connection
            Dim rst As ADODB.Recordset
    
            db.Open conn
            db.Execute (sqlInsert)
    
            MsgBox "Information has been ADDED.", vbOKOnly, "New Record Added"
    
            Set rst = db.Execute("SELECT @@Identity")   ', , adCmdText)
    
            Me.txtCompanyID = rst.Fields(0).Value
    

    Monday, January 25, 2016 7:30 PM
  • Hi John. Good luck!
    Monday, January 25, 2016 7:48 PM
  • Hi John,

    Based on my understanding, if you want to get the last-inserted identity value via the system function @@IDENTITY, we'd better combine the insert SQL with this function in the same transaction so that the code can work for the multiple user environment.

    If you were get the last-inserted identity value in different transactions, we can get the identity value based on the values of fields we inserted before.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 26, 2016 2:11 AM
    Moderator