Unable to reset Combo Box Data Field Format.

[Mike Samyn]

To All: I am really stuck on an Access Control problem and I haven't seen
this exact problem addressed in the Access forum so here goes.

I’ve written some code in an Access 2003 form to generate dynamic SQL based
on selections for combo boxes. All works very well except for one problem.


One of the series of combo boxes allows the user to select criteria based on
either selecting values from the combo box row source or by typing them it
into the box. Also I allow the '*' character as a wild card character. This
works fine with text or numeric values the first time around. However, if
the user attempts to update a previously used combo box that has a numeric
value with a text value the combo box complains saying, “The value you
entered isn’t valid for this fieldâ€. The only way to correct the problem is
to close and restart the form.

My combo boxes are bound via queries that reference tables which are filled
based on other combo box selections of fields in the DB. Here is what one
looks like.

ComboBox_1.RowSource = “SELECT tbl_Combobox_Data1.* FROM tbl_Combobox_Data1;â€

ComboBox_1.DefaultValue = “*â€

ComboBox_1.Value = will vary – either selected from the row source or hand
entered including '*'.

As the user selects different fields to build a different SQL statement the
code change the values in the row source tables as needed and re-queries the
control. The problem comes when the user selects a value in the Combo Box.
It appears that the CB assumes the value type (numeric [integer or real] or
text) from the selection and then locks the combo box into that format until
I close the form. There MUST be a way to reset the CB programmatically.
I’ve tried to reset every possible property w/o success. Attempting to null,
or blank out, or reassign row sources w/o success. Any help would be greatly
appreciated.

 

[Allen Browne]

Access doesn't cope well with controls that change data type after the form
is open. In some cases, you can actually crash Access doing that. Setting
the combo's Default Value to "*" makes it a string value, and you say you
are doing this with combos where the bound column is numeric also.

So the solution is going to be to find a way to build the query so it
doesn't change data type. Ideally, it should not contain redundant and
inefficient stuff in the WHERE clause either, such as lots of:
[SomeField] Like "*"

(And, just in case you are not aware, Like "*" does not return all rows. It
eliminates the rows where the field is null.)

While the purpose of the combo is not clear (bound, filter for form,
cascading combos, or other), the solution will probably be to leave the
combo null rather than insert asterisks, and then just omit the blank
controls from the target SQL statement. There's an example of how to build
such a SQL statement (or WHERE clause, or Filter, or WhereCondition) in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

To All: I am really stuck on an Access Control problem and I haven't seen
this exact problem addressed in the Access forum so here goes.

I’ve written some code in an Access 2003 form to generate dynamic SQL
based
on selections for combo boxes. All works very well except for one
problem.


One of the series of combo boxes allows the user to select criteria based
on
either selecting values from the combo box row source or by typing them it
into the box. Also I allow the '*' character as a wild card character.
This
works fine with text or numeric values the first time around. However, if
the user attempts to update a previously used combo box that has a numeric
value with a text value the combo box complains saying, “The value you
entered isn’t valid for this fieldâ€. The only way to correct the problem
is
to close and restart the form.

My combo boxes are bound via queries that reference tables which are
filled
based on other combo box selections of fields in the DB. Here is what one
looks like.

ComboBox_1.RowSource = “SELECT tbl_Combobox_Data1.* FROM
tbl_Combobox_Data1;â€

ComboBox_1.DefaultValue = “*â€

ComboBox_1.Value = will vary – either selected from the row source or hand
entered including '*'.

As the user selects different fields to build a different SQL statement
the
code change the values in the row source tables as needed and re-queries
the
control. The problem comes when the user selects a value in the Combo
Box.
It appears that the CB assumes the value type (numeric [integer or real]
or
text) from the selection and then locks the combo box into that format
until
I close the form. There MUST be a way to reset the CB programmatically.
I’ve tried to reset every possible property w/o success. Attempting to
null,
or blank out, or reassign row sources w/o success. Any help would be
greatly
appreciated.

 

[Mike Samyn]

Allen,

Thanks for your comments. Regards to mixed data types and the combo boxes -
I've been coming to the same conclusion. I guess I was hoping for a quick
solution to the problem – some magic way to reset combo box data properties
on the fly. At this point I may have to completely rewrite the form or
simply check for attempts by the user to change data types and force the form
to close and reload. Not a pleasant solution. I'll take a closer look at
the code you've directed me to but I fear your data types do not change in a
give combo box. The only other solution maybe to convert all numeric data to
text before displaying but that may take some very fancy coding and total
unbound combo boxes.

Thanks Again,
Mike

Access doesn't cope well with controls that change data type after the form
is open. In some cases, you can actually crash Access doing that. Setting
the combo's Default Value to "*" makes it a string value, and you say you
are doing this with combos where the bound column is numeric also.

So the solution is going to be to find a way to build the query so it
doesn't change data type. Ideally, it should not contain redundant and
inefficient stuff in the WHERE clause either, such as lots of:
[SomeField] Like "*"

(And, just in case you are not aware, Like "*" does not return all rows. It
eliminates the rows where the field is null.)

While the purpose of the combo is not clear (bound, filter for form,
cascading combos, or other), the solution will probably be to leave the
combo null rather than insert asterisks, and then just omit the blank
controls from the target SQL statement. There's an example of how to build
such a SQL statement (or WHERE clause, or Filter, or WhereCondition) in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

To All: I am really stuck on an Access Control problem and I haven't seen
this exact problem addressed in the Access forum so here goes.

I’ve written some code in an Access 2003 form to generate dynamic SQL
based
on selections for combo boxes. All works very well except for one
problem.


One of the series of combo boxes allows the user to select criteria based
on
either selecting values from the combo box row source or by typing them it
into the box. Also I allow the '*' character as a wild card character.
This
works fine with text or numeric values the first time around. However, if
the user attempts to update a previously used combo box that has a numeric
value with a text value the combo box complains saying, “The value you
entered isn’t valid for this fieldâ€. The only way to correct the problem
is
to close and restart the form.

My combo boxes are bound via queries that reference tables which are
filled
based on other combo box selections of fields in the DB. Here is what one
looks like.

ComboBox_1.RowSource = “SELECT tbl_Combobox_Data1.* FROM
tbl_Combobox_Data1;â€

ComboBox_1.DefaultValue = “*â€

ComboBox_1.Value = will vary – either selected from the row source or hand
entered including '*'.

As the user selects different fields to build a different SQL statement
the
code change the values in the row source tables as needed and re-queries
the
control. The problem comes when the user selects a value in the Combo
Box.
It appears that the CB assumes the value type (numeric [integer or real]
or
text) from the selection and then locks the combo box into that format
until
I close the form. There MUST be a way to reset the CB programmatically.
I’ve tried to reset every possible property w/o success. Attempting to
null,
or blank out, or reassign row sources w/o success. Any help would be
greatly
appreciated.

 

[Allen Browne]

There is a workaround, involving the Like operator which always treats the
data as text regardless of its type.

I really hate people doing that instead of handling the data types
correctly, but this utility approaches the problem like that:
http://allenbrowne.com/AppFindAsUType.html

(This utility is very simple to implement (copy 2 contorls for your form,
and set one property), but considerably more cryptic to follow if you read
the code, due to all the weird things it has to handle.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for your comments. Regards to mixed data types and the combo
boxes -
I've been coming to the same conclusion. I guess I was hoping for a quick
solution to the problem – some magic way to reset combo box data
properties
on the fly. At this point I may have to completely rewrite the form or
simply check for attempts by the user to change data types and force the
form
to close and reload. Not a pleasant solution. I'll take a closer look at
the code you've directed me to but I fear your data types do not change in
a
give combo box. The only other solution maybe to convert all numeric data
to
text before displaying but that may take some very fancy coding and total
unbound combo boxes.

Thanks Again,
Mike

Access doesn't cope well with controls that change data type after the
form
is open. In some cases, you can actually crash Access doing that. Setting
the combo's Default Value to "*" makes it a string value, and you say you
are doing this with combos where the bound column is numeric also.

So the solution is going to be to find a way to build the query so it
doesn't change data type. Ideally, it should not contain redundant and
inefficient stuff in the WHERE clause either, such as lots of:
[SomeField] Like "*"

(And, just in case you are not aware, Like "*" does not return all rows.
It
eliminates the rows where the field is null.)

While the purpose of the combo is not clear (bound, filter for form,
cascading combos, or other), the solution will probably be to leave the
combo null rather than insert asterisks, and then just omit the blank
controls from the target SQL statement. There's an example of how to
build
such a SQL statement (or WHERE clause, or Filter, or WhereCondition) in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

To All: I am really stuck on an Access Control problem and I haven't
seen
this exact problem addressed in the Access forum so here goes.

I’ve written some code in an Access 2003 form to generate dynamic SQL
based
on selections for combo boxes. All works very well except for one
problem.


One of the series of combo boxes allows the user to select criteria
based
on
either selecting values from the combo box row source or by typing them
it
into the box. Also I allow the '*' character as a wild card character.
This
works fine with text or numeric values the first time around. However,
if
the user attempts to update a previously used combo box that has a
numeric
value with a text value the combo box complains saying, “The value you
entered isn’t valid for this fieldâ€. The only way to correct the
problem
is
to close and restart the form.

My combo boxes are bound via queries that reference tables which are
filled
based on other combo box selections of fields in the DB. Here is what
one
looks like.

ComboBox_1.RowSource = “SELECT tbl_Combobox_Data1.* FROM
tbl_Combobox_Data1;â€

ComboBox_1.DefaultValue = “*â€

ComboBox_1.Value = will vary – either selected from the row source or
hand
entered including '*'.

As the user selects different fields to build a different SQL statement
the
code change the values in the row source tables as needed and
re-queries
the
control. The problem comes when the user selects a value in the Combo
Box.
It appears that the CB assumes the value type (numeric [integer or
real]
or
text) from the selection and then locks the combo box into that format
until
I close the form. There MUST be a way to reset the CB
programmatically.
I’ve tried to reset every possible property w/o success. Attempting to
null,
or blank out, or reassign row sources w/o success. Any help would be
greatly
appreciated.