Private Sub Crew_ID_NotInList(NewData As String, Response As Integer)
Dim txtSurname As String, txtInitials As String, strPKField As String
Dim intNewID As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Response = acDataErrContinue
If MsgBox(NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
strSQL = "SELECT * from Crew WHERE 1 = 0"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
txtSurname = Left(mixed_case(NewData), InStr(1, NewData, " ") - 1)
txtInitials = UCase(Trim(Mid(NewData, InStr(1, NewData, " ") + 1)))
rs.AddNew
rs!Surname = txtSurname
rs!Initials = txtInitials
strPKField = rs(0).Name 'Find name of Primary Key (ID) Field
rs.Update
rs.Move 0, rs.LastModified
intNewID = rs(strPKField)
Response = acDataErrAdded
MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
Else
Response = acDataErrDisplay
End If
End Sub