combo box errors

[oxfordian3]

I have an unbound combo box that is sporadically failing with an error message when an entry in its pulldown list is selected. The error message is to the effect: 'The value you entered isn't valid for this field' .

The record source for the combo box is a table/query which is updated by an event triggered by another control on the form. That code also sets the Format property of the combo box to match the native Format of the field from which the combo box is retrieving the data. So data from Yes/No field, a date field, or a currency field, for example, display properly in the revised combo box. When the combo box is populated from a source field that has no special format (the usual case, as with text fields), the code resets the combo box's Format to "".

Again, the combo box has no control source (it is unbound). So, the only culprit for an error message like this that I can think of is this Format property. Yet, when the error occurs, the combo box's Format is properly set to "", which should permit any kind of data, and I try entering any kind of data at all -- all data is rejected with this error message.

Is there any thing else I should look at?

 

[oxfordian3]

An MS/Access bug

I think I have encountered a real MS/Access bug. Here is how to reproduce it:

--- Start of Example ---

Set up a table (tblTest) with 3 columns: "Money" (data type Currency), "Year" (data type Number), and "LastName" (data type Text). Load a few records of sample data.

Create a form with a combobox control (cboTest) and leave it unbound, and with a null RowSource.

Make 3 buttons (cmdMoney, cmdYear, cmdLastName), each of which has a Click event that sets the cboTest.RowSource property to a different source:

cmdMoney:

cboTest.RowSource = _
  "SELECT tblTest.[Money] FROM tblTest GROUP BY tblTest.[Money];"

cmdYear:

cboTest.RowSource = _
  "SELECT tblTest.[Year] FROM tblTest GROUP BY tblTest.[Year];"

cmdLastName:

cboTest.RowSource = _
  "SELECT tblTest.[LastName] FROM tblTest GROUP BY tblTest.[LastName];"

Run the form.

Press cmdMoney to set the combobox to the Money data, then choose a value from the cboTest pulldown. You'll see that the "$" format will be placed in the combobox field (in addition to the pulldown entries), even though the cboTest.Format property was not set.

Then press cmdYear to set the combobox to the Year data, and choose a value from the cboTest pulldown. Bug: the year value in the field also is formatted as currency.

Then press cmdLastName to set the combobox to LastName data, and try to choose a value from the cboTest pulldown. Bug: the combobox will produce an error message and not allow the value to be selected.

--- End of Example ---

Alternatively, if you start up the form again, and choose cmdYear or cmdLastName FIRST (before you choose cmdMoney), then there will be no problem switching between the data choices (and no formatting will occur in the combo-box data field).

 

[cclambie]

Did you ever find a solution to this?

I am having a similar issue!

 

[oxfordian3]

a while ago

It was a while ago that I worked on that project. I think that I resolved that it was a MS bug, and I think I worked around it by avoiding the Currency data type for fields that get loaded into comboboxes.