Getting new record ID?

[kman42]

When a new record is added, I need to add a set of records to a related table, but I can't seem to get the ID of the new record to use in the INSERT query.



pk_ContractID is the value from the new record that I want to use in the subsequent INSERT query.

Private Sub button_New_Click()
On Error Resume Next
DoCmd.SetWarnings False

Dim rs As DAO.Recordset
Dim strSQL As String
Dim currentEvent As Double
Dim db As Database
Dim currentContractID As Double

DoCmd.GoToRecord , , acNewRec
currentContractID = tbl_Contracts.pk_ContractID

Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_ContractEvents", dbOpenDynaset)

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
    currentEvent = rs!pk_ContractEventID
    strSQL = " INSERT INTO tbl_ContractEventLog (fk_ContractID, fk_ContractEventID) values (" & currentContractID & ", " & currentEvent & ")"
    DoCmd.RunSQL strSQL
    rs.MoveNext
Loop
DoCmd.SetWarnings True

End Sub

[boblarson]

Okay, here you go. But I modified some of your other code as it either wasn't necessary or it was the wrong way to go.

1. Make sure to NOT use On Error Resume Next. Use a real error handler for this. On Error Resume Next can obscure some important errors and this is not one place to use it.

2. You don't need Set Warnings if you use the .Execute method of the database object.

3. You don't have anything there that needs the rs.MoveLast, rs.MoveFirst. So just leave those out.

4. You need to sort your first recordset by ID because it isn't guaranteed that it will be sorted and then your data could be out of whack easily enough. Access does not store the data in any particular order in the tables, contrary to what it seems. So you always want to apply an order if you want specific behaviour. Also, I wasn't sure if you wanted to have the largest ID first or not but you can add the DESC to the ORDER BY clause if you want the largest one to start off with.

5. Don't use Double for something that should be a Long. Double can suffer from floating point errors and give you decimal places which can throw things off. Better to use a Long so you get no decimal places.

6. I would use Do Until instead of Do While Not. I can't explain why but I do know that there is a behaviour difference and it would probably be best to use the Do Until in this instance.

Private Sub button_New_Click()
    Dim rs     As DAO.Recordset
    Dim rs2    As DAO.Recordset
    Dim strSQL As String
    Dim currentEvent As Double
    Dim db     As Database
    Dim currentContractID As Long

On Error GoTo Errors
    DoCmd.GoToRecord , , acNewRec
    currentContractID = tbl_Contracts.pk_ContractID
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * From tbl_ContractEvents ORDER BY pk_ContractID", dbOpenDynaset)
    currentEvent = rs!pk_ContractEventID
    Do Until rs.EOF
        strSQL = "INSERT INTO tbl_ContractEventLog (fk_ContractID, fk_ContractEventID) values (" & currentContractID & ", " & currentEvent & ")"
        db.Execute strSQL, dbFailOnError
        
        Set rs1 = db.OpenRecordset("Select @@IDENTITY As LastID")
        currentEvent = rs1!LastID
        rs1.Close
        rs.MoveNext
    Loop
 
ExitHere:
     Exit Sub
Errors:
           
            MsgBox "Error " & Err.Number & " - " & " (" & Err.Description & ") in procedure button_New_Click of Module Module26", , CurrentDb.Properties("AppTitle")
            Resume ExitHere
            Resume
    
End Sub

[kman42]

I don't think my purposes were very clear in my first post, so let me try clarifying.

I have three tables:

tbl_Contracts
pk_ContractID

tbl_ContractEventLog
pk_ContractEventLogID
fk_ContractID
fk_ContractEventID
ActualDate
RevisedDate
ProposedDate
Comments

tbl_ContractEvents
pk_ContractEventID
NameOfEvent

There are other fields in these tables, but you get the idea.

When I create a new record by clicking on the New button on the form displaying tbl_Contracts, it needs to create 30 records in tbl_ContractEventLog, one for each record in tbl_ContractEvents. I then need the subform displaying tbl_ContractEventLog to display all 30 of these events.

kman