Isn't an Item on List

[Al Smith]

Using a combo box for autolookup works fine, except when the item isn't found.
I receive the error, as expected, "The text you entered isn't an item in the
list." I have a macro that returns a message that the "Record is not found".
When the item isn't found, in this case a prescription, because the
prescription number has changed, I want to 1) erase the searched value; 2)
close this form; 3) open a form to find a medication by brand name, all
without prompts or errors. I tried "SetWarnings" off, as the first line in
the macro, but this does not help.

Help would be appreciated!

Thanks in advance.

 

[Klatuu]

Al,

In the properties for your combo box, set Limit to List to No. In the Not
In List event of the combo box, put code in to open the form you want and
close the current form.

 

[Dirk Goldgar]

Al,

In the properties for your combo box, set Limit to List to No. In
the Not In List event of the combo box, put code in to open the form
you want and close the current form.

The NotInList event won't fire if LimitToList is set to No. I'd
suggest, instead, leaving it set to Yes, but putting code in the
NotInList event along these lines:

'----- start of code -----
Private Sub YourComboBox_NotInList( _
NewData As String, Response As Integer)

Response = acDataErrContinue
Me!YourComboBox.Undo

If MsgBox( _
"That prescription number isn't on file. " & _
"Do you want to search by brand name?", _
vbYesNo, _
"Unknown Prescription") _
= vbYes _
Then
DoCmd.Close acForm, Me.Name, acSaveNo
DoCmd.OpenForm "frmSearchByBrandName"
End If

End Sub

'----- end of code -----

 

[Klatuu]

Sorry, Dirk, I meant No.

The NotInList event won't fire if LimitToList is set to No. I'd
suggest, instead, leaving it set to Yes, but putting code in the
NotInList event along these lines:

'----- start of code -----
Private Sub YourComboBox_NotInList( _
NewData As String, Response As Integer)

Response = acDataErrContinue
Me!YourComboBox.Undo

If MsgBox( _
"That prescription number isn't on file. " & _
"Do you want to search by brand name?", _
vbYesNo, _
"Unknown Prescription") _
= vbYes _
Then
DoCmd.Close acForm, Me.Name, acSaveNo
DoCmd.OpenForm "frmSearchByBrandName"
End If

End Sub

'----- end of code -----


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)