I am helping a friend with a database (Access 2007). It is done using
macros and I don't want to introduce any Visual Basic if possible.

I want to apply a filter to a form's recordsource using a macro. The
filter Where Condition needs to be something like the following:

Forms.Customers.CustomerID IN (SELECT CustomerID FROM
qryActiveCustomers)

As you can see, I am trying to apply an IN statement to the query
where the IN is a SELECT from a query. Access is not accepting this.
I also tried

Forms.Customers.CustomerID IN (qryActiveCustomers.CustomerID)

which is accepted, but when run prompts me for
qryActiveCustomers.CustomerID, which gives me the impression that
Access does not understand that this is a query.

Does anyone have any suggestions? Again, no Visual Basic if possible.

Bill

Bill E. Hollywood, FL wrote:
macro)?
Try
CustomerID IN (SELECT CustomerID FROM qryActiveCustomers)

I tried that. It does not seem to work.

Bill E. Hollywood, FL wrote:
Filter property for this, unless there is a way to directly incorporate the
criteria in qryActiveCustomers into the form's filter. Are both the form and
the saved query based on the same table?

On Sep 6, 5:37*am, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
No, they are not. The saved query provides a DISTINCT list of
customers that are present in certain other child tables.

As a workaround, I have modified the query that serves as the form's
recordsource to incorporate these queries with an IN statement in the
WHERE clause but with appropriate OR conditions to "turn off" these
parts of the query. It is working but it would be better to be able
to apply these as filter conditions.

Bill

Bill E. Hollywood, FL wrote:
I'd be more inclined to do a left join to qryActiveCustomers in the form's
recordsource, allowing the filter

qryActiveCustomers.CustomerID is not null

A left join renders the query non-updatable

Bill E. Hollywood, FL wrote:
Not always, but yes, that is a problem.

I take it that you don't have a "master" customer table? A table containing
a record for every customer used in your database? If you did, you could add
an Active field (Yes/No), and periodically update it based on the data
returned by qryActiveCustomers. I don't know how dynamic that list is - how
often is a customer activated/deactivated?

If you don't have one, it would be easy to generate it: change
qryActiveCustomers to a make-table query, add a calculated column called
Active that contains -1, and run it.
Then modify qryActiveCustomers so it returns inactive customers (and returns
0 in that Active field). Create an append query to add the inactive
customers to the master customer table. Modify the append query to base it
on a union between qryActiveCustomers and qryInactiveCustomers, create a
Delete query to clear master customers, and you have the mechanism to
periodically update master customers.

The benefit of course is the ability to use an inner join.

The truth is that the qryActiveCustomers is really a fabricated query
name and has nothing to do with "active" status. In reality, the
query is returning a list of individuals with active tasks, which
depends upon another table entirely. Yes, I could start adding
columns to the main table and use code to track these things (ideally,
I would prefer to use a trigger for something like that but Access
doesn't have triggers). However, I don't like this approach and I'm
trying to keep this as simple as possible so that my friend is not
overwhelmed--thus the request to not involve Visual Basic in the
solution.

Bill E. Hollywood, FL wrote:
Not true anymore, but I guess it would be a hard sell to get your friend to
upgrade.

I'm afraid I'm out of suggestions. Hopefully someone else will chime in.