Hi all,
Good morning
Below is code for what I am working on and ran into a glitch. I need to find a way once the record is created in strSql, to then get the auto number from that and put it in a different table. See bleow in RED
Code:
Private Sub CboInstitutionSearch_NotInList(NewData As String, Response As Integer)
Dim strsql As String
Dim maxID As Long
If MsgBox("Institution is not in Current List, Would you Like to Add?", vbYesNo) = vbNo Then
Me.Undo
Me.CboInstitutionSearch = Null
Response = acDataErrContinue
Exit Sub
End If
strsql = "Insert Into tblInstitution ([InstitutionName]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
"After above puts the [InstitutionName] into that table I need to get the [InstitutionID] AutoNumber from that record I just created,
and put that [InstitutionID] in column InstitutionID into the table "tblOnLineAccess" as a new record"
Not sure if an Insert Into or Update or what is needed here to accomplish this?
Response = acDataErrAdded
Me.CboInstitutionSearch = Null
Me.Requery
maxID = DMax("InstitutionID", "tblInstitution")
Me.RecordsetClone.FindFirst "InstitutionID=" & maxID
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
Thank you all,
Dave