Using combo-box as a user filter - trying to use format to display text "all" for null values

[google]

This is something I've done plenty of times in '97, but I can't seem to
get it to work correctly in Access 2003. Say, for example, I have a
form with an unbound combobox, the data source is a table of customers,
first column is the key field, which is hidden with a 0" width, and the
second column is the customer name. The SQL is a union query that also
inserts Null in the first column, and "All" in the second column. That
combobox gets used as criteria for a query, such that a value entered
by the user restricts the results to records that are related to that
customer, and choosing "All" results in that combobox having a null
value, which I use in the query to return records irrespective of the
customer.

Functionally, it works fine in 2003 as it did in '97: "All" shows up
in the combobox as a possible selection, and when it's selected, the
comboboxes value gets set to Null, which I use in the query to not have
the customer ID be a criteria in the results. The problem is in the
display: in '97, I could set the default value of that combobox to
Null, and the format of that field to @;"All", which would
automatically display "All" in that field when the form was opened,
showing that as a default, all customers would be returned. In 2003,
the combobox is blank. The value of the combobox is null, as it should
be, but the word "All" doesn't show up in that field until the user
pulls it down and selects All. I even have other controls that
programatically reset this combobox to Null, and in that case, it also
doesn't work: it is simply a blank display.

In short, the combobox will not display "All" on a null value UNLESS
that value was selected by the user. How do I get the combobox to
display a pre-defined string when the value of that control is Null,
even when the value wasn't set to Null directly by the user by
selection within the combobox?

Thanks!

 

[google]

I'll just add, I've implemented a work-around: rather than using Null
as the value in the query's criteria to indicate that that field should
not be used to restrict results, I'm using -1. -1 is an impossible
value for a valid customerID field, as it's an autonumber, so in this
case I can use that just as easily as Null. I'd still, however, like
to understand why this doesn't work in 2003 like it does in '97.

Thanks again!

 

[pietlinden]

Does this work?
http://www.mvps.org/access/forms/frm0043.htm

 

[google]

Yes, the first method listed there (using a union query), is exactly
what I'm doing to get "All" into the list of choices for the user.
When they select "All", the value of the combobox is set to Null, as it
should be. The problem I'm having is that I'm hiding the first column
(it's width is set to 0), and therefore what is displayed in the field
when the box is collapsed is the value of the second column in the row
source (in this case, either All, or the customer's name, corresponding
to either Null, or the customer's primary key field being stored as the
value for the control). If they select "Company ABC" in the drop down,
"Company ABC" remains visible in the combobox when it's collapsed, just
as it should. If they select "All" in the drop-down, "All" remains
visible in the control when it's collapsed, as it should. If I set the
value of the combobox control to 1 (either through code, or by setting
the default value of that control to 1 then opening the form), and 1
happens to be the primary key ID for "Company ABC", then "Company ABC"
is shown in the value of the combobox when the form wakes up, or when
the value is changed programatically, just as it should. In other
words, since the second column of the combobox is the first visible
field, when the value of the combobox is set programmatically, it
automatically displays the coresponding value from the second column.
This is what is NOT working with Null values: if the value of the
combobox is set to Null, it is NOT automatically displaying "All" in
the collapsed combobox, even though "All" is the corresponding value of
the second column to a Null value in the first column, as per the union
query as described in your link.

If I remember correctly, Access '97 behaved in a similar manner, in
that the second column wasn't automatically pulled in if Null was the
value of the first (bound) column. But in '97, I was able to work
around that by setting the format property to @;"All" (or possibly
;;;"All" with numerics, can't remember for sure). But using the format
property to display "All" for Null isn't working when the control is a
combobox with a row source such as this.

Perhaps my "new" workaround is simply the new solution to this in 2003,
but I'm still curious to see what others may have to say.