Re: Unable to reset Combo Box Data Field Format.
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sun, 18 Mar 2007 10:14:31 +0900
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike Samyn" <MikeSamyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:16101338-963E-42F6-8285-EAE2B4A28B5F@xxxxxxxxxxxxxxxx
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
"Allen Browne" wrote:
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike Samyn" <MikeSamyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FDCB65B7-92D8-48D6-8AE3-D05908001607@xxxxxxxxxxxxxxxx
> 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
.
- References:
- Re: Unable to reset Combo Box Data Field Format.
- From: Allen Browne
- Re: Unable to reset Combo Box Data Field Format.
- From: Mike Samyn
- Re: Unable to reset Combo Box Data Field Format.
- Prev by Date: Re: How do I override the "required" field via a form
- Next by Date: Re: How do I override the "required" field via a form
- Previous by thread: Re: Unable to reset Combo Box Data Field Format.
- Next by thread: Re: Switchboard Error - On Click has a function that can't be found.
- Index(es):
Relevant Pages
|
