BugFix: Combo Datatype bug
Please fix the combo box RowSource datatype binding bug.
The bug is as follows:
1. Create a table [Table1] with two columns:
- [NumberField], datatype: Number, Double
- [TextField], datatype: Short Text
Add 1 Row to the table:
[NumberField] = 10
[TextField] = "Some Text 10"
- Create a combo box:
Set the RowSource of the combo box to this:
SELECT NumberField, TextField FROM Table1;
Add a Button to the form: [cmbErrorExample]
Add On Click Event:
If Me.cmbErrorExample.RowSource = "SELECT NumberField, TextField FROM Table1;" Then
Me.cmbErrorExample.RowSource = "SELECT TextField, NumberField FROM Table1;"
Me.cmbErrorExample.DefaultValue = "Some Text 10"
Me.cmbErrorExample.Value = "Some Text 10"
Me.cmbErrorExample.RowSource = "SELECT NumberField, TextField FROM Table1;"
Me.cmbErrorExample.DefaultValue = 10
Me.cmbErrorExample.Value = 10
Now open the form:
Drop the combo box, you should see the one row with values:
10 | Some Text 10
Select the row - the value of the field becomes 10.
Now, click the button. The Rowsource should change to:
"SELECT TextField, NumberField FROM Table1;"
However, if the user has already made a selection as instructed above, error appers:
Error Occurred in Sub cmdToggleRowSourceClick of Module FormForm1
Error The value you entered isn't valid for this field.
Expected: the code should be able to programmatically change the Rowsource of the combo box.
Actual: Because the unbound field has had a numeric value assigned as a value, the programmatic change of the RowSource of the combo box to a text value results in a type mismatch that should not be happening.
This used to be possible in Access 2003, but in Access 2019 the combo box appears to change itself to be bound to a numeric data type, even though it is an UNBOUND field.
This is a problem when creating forms that change combo box data sources programmatically.
This URL contains a .accdb with a sample form that demonstrates the issue: