OnNotInList

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

On a form, I have 100+ combo boxes. To make sure that members not enter
additional data (beyond the options of the combo list), I set the combo's
property "Limit to List" = Yes.

Then, if a user attempts to type in an non-valid value, I'd like to throw a
customized message such as:

MsgBox "You must select a value from the list!", vbInformation,
"Information"


However, I don't want to include the same 3 lines 100+ times (one for each
combo box) into my VBA code... as illustrated below:

&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub Combo1_NotInList(NewData As String, Response As Integer)
MsgBox "You must select a value from the list!", vbInformation,
"Information"
End Sub


Private Sub Combo2_NotInList(NewData As String, Response As Integer)
MsgBox "You must select a value from the list!", vbInformation,
"Information"
End Sub

etc.
etc.
etc.

Private Sub Combo100_NotInList(NewData As String, Response As Integer)
MsgBox "You must select a value from the list!", vbInformation,
"Information"
End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&



Instead, I'd rather have the MsgBox once and just call the same function
from each combo box if invalid data is entered. I did this via the
following method...


&&&&&&&&&&&&&&&&&&&&&&&&&

Function ValueNotInListResponse() As Long

MsgBox "You must select a value from the list!", vbInformation,
"Information"

ValueNotInListResponse = acDataErrContinue

End Function

&&&&&&&&&&&&&&&&&&&&&&&&&


Then, in each combo's "On Not In List" event, I added the following:
=ValueNotInListResponse()

Here's what works:
If invalid data is entered, the customized message pops up

Here's what I need some help with:
After I get the customized message, Access still pops up the default
message:
"The text you entered isn't an item in the list..."

My question: How can I get rid of the 2nd message "The text you entered..."?
The additional line "ValueNotInListResponse = acDataErrContinue" doesn't
seem to work for this.


Thanks,
Tom
 
Tom,
Add this to the form module...
Private Function NotInListWarning()
MsgBox "You must select a value from the list!", vbInformation,
"Information"
End Function

In the NotInList property of each combo box call the function...
=NotInListWarning()

Tip: You can select all 100 combos at one time, and enter that function call
into all 100 at one time.

hth
Al Camp
 
Al:

Thanks for the reply... I already have tried that... it works fine; however,
I still get a 2nd Access default message after having clicked OK on the
customized message. And that's the problem.

Once I click OK onto msgbox, "You must select a value...", I don't want to
get the Access default message indicating that "The text you entered isn't
an item int the list."

Makes sense?
 
Graham:

Thanks for the info... I'm confident that I have exactly that the URL
suggests... still the Access default message pops up after my customized
message.

I'll reread this again... maybe I'm missing something.

Tom
 
Hi Tom

I'm sorry, but AFAIK there is no way to pass arguments to event handling
code other than via the event procedure. In other words, the only way to
set the Response argument for NotInList is by writing an individual
NotInList EP for each combo box.

You can simplify this by writing a single function to display a message and
return a response value. Then each EP boils down to a single line:

Private Sub Combo1_NotInList(NewData As String, Response As Integer)
Response = MyNotInList(NewData)
End Sub

If you have a huge number of combo boxes, it would be easy enough to write
some code to generate these procedures in the form module.
 
Tom,

That's not possible! If you have:

Response = acDataErrAdded
....or...
Response = acDataErrContinue

....there is no way that Access will display the default MsgBox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Back
Top