Using Query Defs in a Form

[Amy E. Baggott]

I am building a dialog box to allow users to generate mailing or broadcast
email/fax list of either all our companies or exclude the ones who are
exhibiting in the current show. I have two base queries that generate full
contact data for these two groups. However, I am trying to build a dialog
that will let the user choose from three record selection options (all
companies, by specified product category, or by specified state(s)), then
select which fields to include (all fields for mailing, or just company,
contact name, phone, fax, and e-mail for broadcast) and whether or not to
include current exhibitors. If I use a separate query for each circumstance,
that works out to 12 queries. So I am trying to use a QueryDef to modify my
base query on the fly to use only the selected fields and/or filter for
product category or state.

The problem is that when I actually try to run the code, I get "item not
found in this collection" even though the base queries exist and run in the
database window. What am I doing wrong?

 

[Dirk Goldgar]

I am building a dialog box to allow users to generate mailing or broadcast
email/fax list of either all our companies or exclude the ones who are
exhibiting in the current show. I have two base queries that generate
full
contact data for these two groups. However, I am trying to build a dialog
that will let the user choose from three record selection options (all
companies, by specified product category, or by specified state(s)), then
select which fields to include (all fields for mailing, or just company,
contact name, phone, fax, and e-mail for broadcast) and whether or not to
include current exhibitors. If I use a separate query for each
circumstance,
that works out to 12 queries. So I am trying to use a QueryDef to modify
my
base query on the fly to use only the selected fields and/or filter for
product category or state.

The problem is that when I actually try to run the code, I get "item not
found in this collection" even though the base queries exist and run in
the
database window. What am I doing wrong?

I'd have to see the code, and it would help a lot if you could identify
which line is raising the error.

When I'm doing this sort of thing, I usually don't modify an existing query
on the fly, but rather build a SQL string based on the user's selectuibs and
assign that to a recordsource or rowsource, or else open a recordset on it
(depending on what I am doing). There have been times when I've seen no
alternative but to modify the SQL of a stored "work query", but I try very
hard to avoid it.

 

[Amy E. Baggott]

I reposted the question because when I searched for my post I couldn't find
it. The code is posted there. The reason I started with an existing query
is because the SQL involved in getting non-exhibiting companies is complex
because it has to make sure that they don't have a current show record in the
Orders table, don't have a current record in the Applications table, and
don't have a current show record in the SkippingShow table, so the base query
for non-exhibiting companies (the most common list we run) has three
sub-queries that check for those current records.

 

[Dirk Goldgar]

I reposted the question because when I searched for my post I couldn't find
it.

I understand. So far as I can tell, the search function in the webnews
interface is either broken or too limited to be useful.

The code is posted there. The reason I started with an existing query
is because the SQL involved in getting non-exhibiting companies is complex
because it has to make sure that they don't have a current show record in
the
Orders table, don't have a current record in the Applications table, and
don't have a current show record in the SkippingShow table, so the base
query
for non-exhibiting companies (the most common list we run) has three
sub-queries that check for those current records.

I've posted a reply, with revised code, in your other thread.