NotInList message

[Rick]

Once a user has entered data in a "Jobs" combo box on a
JobsActivity form based on the JobsActivity table that is
a lookup on a JobsMaster table and Access issues the
standard Access NotInList warning; how do you totally
cancel the input?

I have written a NotInListEvent which presents the user
with the option of opening the "JobsMaster" form based on
the JobsMaster table to enter a new job or cancelling
their attempt.

However, when they respond negatively the cursor remains
in the combo box with the offending data still present.
The Cancel=True code does not remove the input or reverse
the record entry.

Further, if they respond yes the JobMaster form opens but
the annoying NotItList warning pops up once again and has
to be closed before they can begin entering a new Job in
the JobMaster form.

Examples of NotInList procedures to use the data input in
the combo box originally to start a new job are not
helpful. I would like the user to move to the JobsMaster
form to record a new Job and then return to the
JobActivity form to choose the new job.

Any help would be appreicated.

 

[Ken Snell]

Include this step in the code before it ends:
Me.Jobs.Undo

 

[Rick]

Ken:

Thanks for the help. The command you suggest does clear
the input; however, I am still getting the persistent Not
In List message from access. Here's the code I am using.
Am I missing something?

Private Sub JIPID_NotInList(NewData As String, _
Response As Integer)

Dim Msg As String

On Error GoTo Err_JobNotThere_NotInList

If NewData = "" Then Exit Sub

Msg = "The Client - '" & NewData & _
"' does not exist in Jobs In Progess" & vbCr & vbCr
Msg = Msg & "Would you like to set up a new job now?"

If MsgBox(Msg, vbQuestion + vbYesNo, _
"WBLI user input requested") = vbYes Then
Me.JIPID.Undo
DoCmd.OpenForm "JobsInProgress", acNormal, _
, , acFormAdd, acWindowNormal
End If

Me.JIPID.Undo

Exit_JobNotThere:
Exit Sub

Err_JobNotThere_NotInList:
MsgBox Err.Number & " " & Err.Description
Response = acDataErrContinue
End Sub

 

[Ken Snell]

Yep - you also need to set the Response variable to tell ACCESS that you
don't want the standard error message to appear. Insert this line of code
after the Undo step:

Response = acDataErrContinue

 

[Ragnar Midtskogen]

And if you add

Response = acDataErrAdded

after the
DoCmd.OpenForm "JobsInProgress", acNormal, _
, , acFormAdd, acWindowNormal
statement, Access will requery the combo so the new item is selected.

Ragnar