shell for insert with vba

[icemonster]

can anyone show me a shell or sample of how to insert using a vba while capturing the last autonumber inserted? thank you!

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=222843

 

[icemonster]

i tried the .addnew before and it works fine for me, what i would like to know is how this would be doing using an INSERT statement in vba?

i have this right now but am getting an error "Argument not optional" i think my sytnax is wrong.

Function ShowIdentity()

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO tbl_school ( school_name, school_degree, school_major, school_startdate, school_enddate ) SELECT tmptbl_school.school_name, " _
         & "tmptbl_school.school_degree, tmptbl_school.school_major, tmptbl_school.school_startdate, tmptbl_school.school_enddate FROM tmptbl_school "

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
lngLastSchoolID = rs!LastID
Me.txtschoolid = lngLastSchoolID
rs.Close

Set db = DBEngine(0)(0)
db.Execute = "INSERT INTO tbl_address ( address_street1, address_street2, address_city, address_state, address_zipcode, address_country ) SELECT temptbl_address.address_street1, " _
             & "temptbl_address.address_street2, temptbl_address.address_city, temptbl_address.address_state, temptbl_address.address_zipcode, " _
             & "temptbl_address.address_country FROM temptbl_address "
             
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
lngLastAddressID = rs!LastID
Me.txtschoolid = lngLastAddressID
rs.Close

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO tbl_address_school (addschool_id_school, addschool_id_address) VALUES (" & lngLastSchoolID & "," & lngLastAddressID & ");"
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[pbaldy]

This worked in a brief test:

db.Execute "INSERT INTO tblTrips(DOR_Date) VALUES(#4/24/12#)"
Set rs = db.OpenRecordset("SELECT @@Identity AS LastInserted FROM tblTrips")
MsgBox rs!LastInserted

 

[jdraw]

Post deleted.
Sorry, think I've missed something in the post.

 

[icemonster]

so yeah with single inserts it works but with multiple insert i get the error.

i have this code but it's for my mysql back end, how would i adapt this to ms access? because the idea here is, there is a temp table within the front end that needs to be appended to the back end.

                     strSQL = "INSERT INTO tbl_school ( school_name, school_degree, school_major, school_startdate, school_enddate ) SELECT tmptbl_school.school_name, " _
                            & "tmptbl_school.school_degree, tmptbl_school.school_major, tmptbl_school.school_startdate, tmptbl_school.school_enddate FROM tmptbl_school "
                                 
                            .Execute strSQL, , adCmdText + adExecuteNoRecords
                            strSQL = "SELECT Last_Insert_ID();"
    
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    ' Found the new ID - build the second Insert SQL string
                                    lngLastSchoolID = .Fields(0)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With
                            
                    strSQL = "INSERT INTO tbl_address ( address_street1, address_street2, address_city, address_state, address_zipcode, address_country ) SELECT temptbl_address.address_street1, " _
                             & "temptbl_address.address_street2, temptbl_address.address_city, temptbl_address.address_state, temptbl_address.address_zipcode, " _
                             & "temptbl_address.address_country FROM temptbl_address "
                                 
                            .Execute strSQL, , adCmdText + adExecuteNoRecords
                            strSQL = "SELECT Last_Insert_ID();"
    
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    ' Found the new ID - build the second Insert SQL string
                                    lngLastAddressID = .Fields(0)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With
                                        
                    strSQL = "INSERT INTO tbl_address_school (addschool_id_school, addschool_id_address) VALUES (" & lngLastSchoolID & "," & lngLastAddressID & ");"
                            .Execute strSQL, , adCmdText + adExecuteNoRecords