Inserting Record Using Last ID of another Table

[kmrickert]

I'm trying to do a vba insert - where the newest "LOAN_ID" of a table called "LOANS" Is inserted into a table called "PAYMENTS" along with the current date being inserted into a "DATE_OF_PAYMENT" field also in the PAYMENTS table. Here's what I have:

'------------------------------------------------------------
' NEW_LOAN
'
'------------------------------------------------------------
Function NEW_LOAN()
On Error GoTo NEW_LOAN_Err


With CodeContextObject
If (.ORIGINAL_AMOUNT > 0) Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RefreshRecord
Beep
MsgBox "Saved Loan", vbInformation, "LOAN SAVED"
DoCmd.RunCommand acCmdRefresh
Else
DoCmd.RefreshRecord
Beep
MsgBox "Loan amount is missing.", vbExclamation, "ENTER AMOUNT:"
End If
DoCmd.Close acForm, "New Loan"


Dim lastID As Integer
strSQL = "INSERT INTO PAYMENTS (LOAN_ID, DATE_OF_PAYMENT) VALUES (lastID, DATE())"


DoCmd.RunSQL (strSQL)
lastID = DMax("[LOAN_ID]", "LOANS")

End With




NEW_LOAN_Exit:
Exit Function


NEW_LOAN_Err:
MsgBox Error$
Resume NEW_LOAN_Exit


End Function

[Minty]

That looks very convoluted.
And all a bit back to front. You are trying to include LastID in your SQL insert but aren't setting it's value until after the insert command ? ?

If the form you are closing has the New Loan ID on it simply get it from the form rather than relying on a DMax.

[kmrickert]

Okay, I'm trying that now. Could you please tell me how I can specify the form in the INSERT statement?

[kmrickert]

Figured it out. Thanks!