Combobox throwing error the first time you use it, but not later times (1 Viewer)

[cricketbird]

I have a combobox that looks up the employeeid (user sees a list of employee names) to navigate to that record. Many folks who know the employee's ID number just type the ID number itself rather than scroll, and that works great too.

However, some folks occasionally type the user's name ("Jones") in instead, which obviously doesn't work.

I played around with adding some code to allow this, which ALMOST works great.

If you open the form and type a text string into the combo box you will immediately (before any code executes) get the error "The value you entered isn't valid for this field".

However, once you USE the combo box to navigate somewhere (anywhere), it will then work fine (allowing text searches) forever until you re-open the form. Somehow, after using the combobox once, Access no longer verifies that you've entered a number and allows my code to run. You can navigate between records, refresh, requery, and the combo box will still work great, but only after you use it (in the traditional way) once each time you open the form.

Any ideas on why it throws an error if you enter text the first time you use the combo box after opening the form, but doesn't later? And how to get that "later" behavior at the start?

Thank you!

Private Sub Combo1_AfterUpdate()

Dim myVal As String
On Error GoTo Err_Handler

myVal = Me!Combo1


If IsNumeric(myVal) Then
    <open form to appropriate employeeid>
Else
   <open form to the first user where their name contains the search string>
End If

Err_Handler:
   MsgBox "There is no user with that ID or name."
   Exit Sub   

End Sub

 

[jdraw]

I suggest you post a copy of your database with instructions to highlight the issue.
What is the rowsource of your combo?
You would normally select a combo entry, you would not be typing a value into the combo.

 

[MajP]

It sounds like your control is bound. Navigation controls should be unbound. Seperae data entry an record navigation.

 

[cricketbird]

Thanks all!
The control is not bound.
The rowsource is a query.
I cannot post the database, unfortunately.

 

[Gasman]

I have a combobox that looks up the employeeid (user sees a list of employee names) to navigate to that record. Many folks who know the employee's ID number just type the ID number itself rather than scroll, and that works great too.

However, some folks occasionally type the user's name ("Jones") in instead, which obviously doesn't work.

I played around with adding some code to allow this, which ALMOST works great.

If you open the form and type a text string into the combo box you will immediately (before any code executes) get the error "The value you entered isn't valid for this field".

However, once you USE the combo box to navigate somewhere (anywhere), it will then work fine (allowing text searches) forever until you re-open the form. Somehow, after using the combobox once, Access no longer verifies that you've entered a number and allows my code to run. You can navigate between records, refresh, requery, and the combo box will still work great, but only after you use it (in the traditional way) once each time you open the form.

Any ideas on why it throws an error if you enter text the first time you use the combo box after opening the form, but doesn't later? And how to get that "later" behavior at the start?

Thank you!

Private Sub Combo1_AfterUpdate()

Dim myVal As String
On Error GoTo Err_Handler

myVal = Me!Combo1


If IsNumeric(myVal) Then
    <open form to appropriate employeeid>
Else
   <open form to the first user where their name contains the search string>
End If

Err_Handler:
   MsgBox "There is no user with that ID or name."
   Exit Sub 

End Sub

Well that cannot be the code?. It will fall through to the error message every time?

 

[cricketbird]

Well that cannot be the code?. It will fall through to the error message every time?

It works fine every time except upon opening the form. The error isn't being thrown by the code - it's being thrown by Access doing some sort of typecheck on the entry, but it doesn't seem to do that after you use the combobox.

 

[jdraw]

I cannot post the database, unfortunately.

I think there is something else in your set up you are not telling us.

 

[GPGeorge]

Thanks all!
The control is not bound.
The rowsource is a query.
I cannot post the database, unfortunately.

It can be difficult to know what is important and what isn't when first learning to troubleshoot code, so please take Jack's comment to heart. Overshare the context and details.

 

[MajP]

It works fine every time except upon opening the form. The error isn't being thrown by the code - it's being thrown by Access doing some sort of typecheck on the entry, but it doesn't seem to do that after you use the combobox.

Before you select a value from the combo its value is Null. Any code or query that relies on this could fail. Do you have either a query or code that references this combo?

 

[LarryE]

The combox Bound Column property will allow either numbers or text to be entered but not both. If you want the user to use the combox to search numbers, then the Bound Column property number needs to be a number in the RowSource.

Given the users are searching employee id's, I would not allow anything to be entered in the combo box. Allow the user to select an employee ID number from the drop-down list and set the combo box Limit To List property to Yes. That way, users cannot enter anything into the box, but must use the drop-down numerical vlaue.

You can also refresh the combo box when a user enters the box:

Private Sub combo1_Enter()
Me.combo1.Requery
End Sub