Problem with "Not In List" Event - Tries to Create a whole new record

[Cassy01]

I have entered the following code to the "Not in List" event of my combo box.

Private Sub Combo74_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo74_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The ground " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Ground")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblGround([GroundName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new ground has been added to the list." _
, vbInformation, "Ground"
Response = acDataErrAdded
Else
MsgBox "Please choose a ground from the list." _
, vbInformation, "Ground"
Response = acDataErrContinue
End If
Combo74_NotInList_Exit:
Exit Sub
Combo74_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Combo74_NotInList_Exit
End Sub

But when the code has fininshed running I get the following message:

"You cannot add or change a record because a related record is reqired in 'tblTeams'.

I know what this is, it is telling me that one of the fields is empty, is there anyway of stopping this?

I mean... when the code has fininshed the curser goes to the next combo box instead of trying to add the record before it is complete?

Many Thanks
Benn

 

[boblarson]

The problem that is happening with this error message is that you have set fields to be required, or they just are based on the data requirements, and when you have the not-in-list event the normal behavior for Access is to save the record first because it is exiting from the record to go do your addition of your not-in-list stuff. Because it can't leave without completing the record first, it can't do it.

There may be other ways around this, but the thought occurs to me that you could save all of the currently entered values to variables then do an Undo of the record, go do the addition for the list, and then have it populate the controls again from the variables and then put yourself back to the next control.