Generic Query?

[Craig]

When I create a db I normally create two tables, one called groups,
and the other called lookup. The Groups table contains a GroupID
(PrimaryKey_AutoNumber) and a Group Description (textfield). The
lookup table contains LookupID (PrimaryKey_AutoNumber) Group (Lookup
vale from groups table) and Lookup_Description (text field). The idea
is that I can add as many groups and lookup values as I need to for
dropdowns. It also allows me to be a little more flexible if I need to
make changes. Then, when I create a form I can create a query on the
lookup table that limits based on groupID. It may not be perfect but
it’s the habit I’ve gotten into.

The problem I have is that I’m reinventing the wheel with each query.
For example, right now I’m creating a form that is going to require
30+ fields with dropdowns. That means I’m going to need to create 30
queries limited based on group id. That seems a little redundant
given it’s the same query with a different groupid. My question is
this, is there a way to create a generic query and have the correct
groupid passed into the query for each field? If so……how do I do
that?

I’m using access 2003 and I have limited skills so don’t get to fancy
on me. Thoughts?

 

[Tom van Stiphout]

On Tue, 19 May 2009 13:40:12 -0700 (PDT), Craig <[email protected]>
wrote:

Sure you can. I'm assuming your queries typically are of the kind:
select LookupID, LookupDescription
from Lookups
where GroupID = 5
(or any other number)

Create a combobox and set the Tag property to 5.
Then set the RowSource to:
select LookupID, LookupDescription
from Lookups
where GroupID = Forms!myForm!myCombobox.Tag
(of course you change myObjectNames to yours)

Now you can simply copy/paste a bunch of comboboxes, and set the Tag
property to the GroupID you want.

-Tom.
Microsoft Access MVP