returning the record id back to form

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

        '===============================

Answer 1

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...

Answer 2

Thanks for the reply.  Nope, didn't work.  I tried a variation of it earlier as well with no results.

...John

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

DB, I'm also open to new and better and different ideas.  How would you fix it (or change it)?

...John

Answer 8

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...

Answer 9

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

Answer 10

Hi John. Good luck!

Answer 11

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.