Special Combo box - how to set up

[paul.schrum]

Access 2007, programming in 2003 mode.

I have a table "tbl_tasks" which is the main table of my application.
Several of its fields are lookups into other tables such as
tbl_persons and tbl_urgencies.

On one or more of my forms, I want to be able to filter list boxes via
queries tied into the combo boxes. As an example, tbl_urgences
consists of

urgencyID urgencyText
1 High
2 Standard
3 Low

I want to enable the user to filter what shows up in list boxes based
on a combo box which would take its values from tables like
tbl_urgencies, but also give them an option of "All" such that the
urgencies combo box would have a drop-down like

All
High
Standard
Low

in which "All" is prepended to the list otherwise taken from the
table.

I can't figure out a quick and convenient way to do this. I know I
can set the Row Source programatically, but how to abstract this
operation into a single function is baffling me. I want to have "All"
for many of these filters of peripheral tables. That is why I want to
abstract the setup of this into a function.

Can anyone offer advice on this? Thanks.

- Paul Schrum

 

[n00b]

I usually create a table with one record in it, the "(All)" record.
Then I use a union query to append my first query with my "All" table.

Something like:

SELECT UrgengyText As Urgency FROM tbl_urgencies UNION SELECT AllText
As Urgency FROM tblAll ORDER BY Urgency

 

[paul.schrum]

n00b,

This sounds like a good idea. But then I thought about the primary
key value of the single row from tbl_all conflicting with the pk value
of the first "Urgency" type, which would also have value = 1.

How do you overcome this?

- Paul

 

[n00b]

The query I gave you doesn't select the primary key so you should be
good to go. The query only selects the UrgencyText and the text
"(All)".

If you absolutley need a numerical key, add another integer field to
the tblAll table and put a zero in it. In the union query select both
the key field and the text field. Autonumber always starts at 1 so
that should work fine. I've seen -1 used for this purpose as well.

 

[Bob Quintal]

@q77g2000hsh.googlegroups.co
m:

I usually create a table with one record in it, the "(All)"
record. Then I use a union query to append my first query with my
"All" table.

Something like:

SELECT UrgengyText As Urgency FROM tbl_urgencies UNION SELECT
AllText As Urgency FROM tblAll ORDER BY Urgency

You don't even need the table, you can use literal strings.

SELECT -1 as ID, "ALL" as UrgencyText FROM tbl_Urgencies
UNION
SELECT ID, UrgencyText As Urgency FROM tbl_Urgencies
ORDER BY ID;

 

[paul.schrum]

Bob,

Sorry for the long delay in response, but I am doing this project in
my "free" time.

I think your approach is much better all around. So much so, I went
back and backed out of the changes I had already done based on n00b's
advice.

Thanks a bunch for the advice.

- Paul

 

[n00b]

You still may want to use the table approach. The table is a
"scalable" solution that requires no hardcoded changes if you decide
you want to add some other type of selection. Depending on the size
of your project that could mean a big time saver in the future.
Always plan ahead.

 

[.]

dans l'article
(e-mail address removed),
(e-mail address removed) à (e-mail address removed) a écrit le 22/01/08 3:51 :