process combo box input before lookup

[Brettt3]

Is it possible to manipulate the data that is typed into
the text portion of a combo box before the combo box tries
to check it against the list part?

What I want to do is check to see if the data entered
was numeric and if it is I want to fill with leading
zeroes. It is a 12 char text field that isn't always
numeric, but when it is it is a pain manually putting
in all those zeroes.

thanks

 

[RickSpr]

You can't actually check the value before Access looks it up, but you can do something almost as good.

Make sure the combo box's LimitToList property is set to Yes. This will cause Access to raise the NotInList event if the value entered isn't found in the list.

In the NotInList event procedure, check that the combo box data is less than 12 characters. If it is, you can prepend zeros on it, set the Response argument to acDataErrContinue, and return to Access. Access will drop down the list and scroll it to where the entry is or would be, and wait for the user to press Enter (or Tab, etc.) again. If the expanded value is in the list, the user can see it and just press Enter. If the expanded value still isn't found, the user can see that it isn't. In either case, when the user tries to leave the control, Access will check the value again.

If the data is already 12 characters (or more) when your NotInList procedure is called, either the user entered too long a string, or you've already expanded it and it's still wrong. In this case, set the Response argument to acDataErrDisplay, and Access will display the normal "The value you entered is not in the list" message.

If you don't like the drop-down effect after you've expanded the value, you can avoid it by setting the focus to the next control, but ONLY after you've verified that the expanded value is valid in your own code. When you change the focus, you in effect abort Access' own checking, and the combo box can end up with invalid data in it.

Here's a code sample:

    Private Sub cboMyCombo_NotInList(NewData As String, Response As Integer)
        If Len(NewData) < 12 Then
            cboMyCombo = String$(12 - Len(NewData), &quot;0&quot;) & cboMyCombo.Value
            Response = acDataErrContinue
        Else
            Response = acDataErrDisplay
        End If
    End Sub

Rick Sprague

 

[Brettt3]

Rick, thank you. It works exactly "as advertised".

I did add code to validate the new data in order
to prevent the drop-down effect. It was pretty
straight forward.

Thanks again.