Using a Query as a Form Filter...

[Jen]

I'm using Access 2000.

I have a form with a subform; the main form contains information about
particular news items, and the subform has information for when and how each
of the items has been disseminated. The data is in two tables, in a
one-to-many relationship. The form works fine.

I want to run a parameter query that pulls a list of items that, for
example, were disseminated on a certain day (this info is in the subform). I
can run this query no problem. But what I'd like is for it to open the form,
filtered to show only the items from the parameter query.

However, Access help tells me the following:

Requirements for using queries as filters
A query must meet certain requirements if you're going to use it as a
filter. The query: must be based on the same underlying table or query as the
datasheet or form to which you're applying it; can't include other tables or
queries (etc.).

So, it looks like I can't use this parameter query - which pulls information
from both the main items table and the dissemination table - as a filter for
the form (which is based only on the main items table).

Is there another way to accomplish what I want to do here? I could do my
updates in the query, but that doesn't really give me the full
functionality/ease of use I'm looking for.

Your suggestions would be much appreciated!

Thanks,
Jen

 

[Carl Rapson]

I'm using Access 2000.

I have a form with a subform; the main form contains information about
particular news items, and the subform has information for when and how
each
of the items has been disseminated. The data is in two tables, in a
one-to-many relationship. The form works fine.

I want to run a parameter query that pulls a list of items that, for
example, were disseminated on a certain day (this info is in the subform).
I
can run this query no problem. But what I'd like is for it to open the
form,
filtered to show only the items from the parameter query.

However, Access help tells me the following:

Requirements for using queries as filters
A query must meet certain requirements if you're going to use it as a
filter. The query: must be based on the same underlying table or query as
the
datasheet or form to which you're applying it; can't include other tables
or
queries (etc.).

So, it looks like I can't use this parameter query - which pulls
information
from both the main items table and the dissemination table - as a filter
for
the form (which is based only on the main items table).

Is there another way to accomplish what I want to do here? I could do my
updates in the query, but that doesn't really give me the full
functionality/ease of use I'm looking for.

Your suggestions would be much appreciated!

Thanks,
Jen

It seems to me that your query needn't pull information from the
dissemination table, it only needs to refer to that table in the WHERE
clause. Your SELECT clause can still pull fields only from the main items
table.

Carl Rapson

 

[Jen]

Carl - thanks for your reply! I confess I haven't done a lot of manipulation
of queries in SQL (I just use the design view since usually my queries are
pretty simple), so I'm not sure how to have a query use a table's values as a
parameter but not actually pull values from there. Would you mind explaining
how I can do that? (Or pointing me to a resource?)

Thanks so much!
Jen

 

[Carl Rapson]

If I understand what you're trying to do, you have a table of items linked
(via a primary-foreign key link) to a child table of dissemination dates,
and you want to get a list of items that were disseminated on a certain
date. Is that correct?

Basically, you want to structure your query something like this:

Dim strSQL As String
strSQL = "SELECT * FROM [main table]"
strSQL = strSQL & " WHERE [ID] IN (SELECT [ID] FROM [subtable]"
strSQL = strSQL & " WHERE [dissemination date] = #" & [Enter the
dissemination date] & "#)"
Me.RecordSource = strSQL

So, you're only referring to the dissemination table in the WHERE clause. Of
couse, I don't know the names of your tables or fields, so I just made these
up. You'll have to put in your actual names.

Carl Rapson

 

[Jen]

Thanks again Carl.

OK, I'm probably just very dense, but I'm not even sure where to put this
code.

I have a main switchboard - I'd like to click a button on there and have the
parameter box pop up, then have the main form open filtered to records from
the specified date (in the subform).

So, I was thinking I could pop the code into the "On Click" event from the
switchboard, something like this:

Private Sub cmdItemsByDate_Click()
DoCmd.OpenForm "Items", , , "ItemID IN (SELECT ItemID FROM ItemCommUse
WHERE ItemCommUseSuggDate = #" & [Enter the suggested date of dissemination]
& "#)"
End Sub

However, this doesn't work. I get kind of a weird error message that Access
can't find the field [long vertical bar] referred to in my expression. Since
there is no long vertical bar in my expression, I'm not sure what that means.

Have I completely messed up this code? Not sure how to proceed here...again,
your help would be much appreciated.

Thanks,
Jen

If I understand what you're trying to do, you have a table of items linked
(via a primary-foreign key link) to a child table of dissemination dates,
and you want to get a list of items that were disseminated on a certain
date. Is that correct?

Basically, you want to structure your query something like this:

Dim strSQL As String
strSQL = "SELECT * FROM [main table]"
strSQL = strSQL & " WHERE [ID] IN (SELECT [ID] FROM [subtable]"
strSQL = strSQL & " WHERE [dissemination date] = #" & [Enter the
dissemination date] & "#)"
Me.RecordSource = strSQL

So, you're only referring to the dissemination table in the WHERE clause. Of
couse, I don't know the names of your tables or fields, so I just made these
up. You'll have to put in your actual names.

Carl Rapson

Carl - thanks for your reply! I confess I haven't done a lot of
manipulation
of queries in SQL (I just use the design view since usually my queries are
pretty simple), so I'm not sure how to have a query use a table's values
as a
parameter but not actually pull values from there. Would you mind
explaining
how I can do that? (Or pointing me to a resource?)

Thanks so much!
Jen

 

[Carl Rapson]

Interesting, I would have thought that would work, although I haven't ever
tried putting an IN clause in the WhereCondition parameter. Here's what I
usually do:

1. Create a small form for inputting the desired date. Only controls: a
textbox and a command button.
2. In the command button Click event, call OpenForm to open your Items form.
You might also want to do some error checking on the date value.
3. Pass the inputted date string in the OpenArgs parameter of OpenForm:

DoCmd.OpenForm "Items",,,,,txtDate

4. In the Load event of the Items form, modify the Record Source to
incorporate the passed date:

Me.RecordSource = "SELECT * FROM Items WHERE ItemID IN(..."

That kind of setup has always worked for me.

Carl Rapson

Thanks again Carl.

OK, I'm probably just very dense, but I'm not even sure where to put this
code.

I have a main switchboard - I'd like to click a button on there and have
the
parameter box pop up, then have the main form open filtered to records
from
the specified date (in the subform).

So, I was thinking I could pop the code into the "On Click" event from the
switchboard, something like this:

Private Sub cmdItemsByDate_Click()
DoCmd.OpenForm "Items", , , "ItemID IN (SELECT ItemID FROM ItemCommUse
WHERE ItemCommUseSuggDate = #" & [Enter the suggested date of
dissemination]
& "#)"
End Sub

However, this doesn't work. I get kind of a weird error message that
Access
can't find the field [long vertical bar] referred to in my expression.
Since
there is no long vertical bar in my expression, I'm not sure what that
means.

Have I completely messed up this code? Not sure how to proceed
here...again,
your help would be much appreciated.

Thanks,
Jen

If I understand what you're trying to do, you have a table of items
linked
(via a primary-foreign key link) to a child table of dissemination dates,
and you want to get a list of items that were disseminated on a certain
date. Is that correct?

Basically, you want to structure your query something like this:

Dim strSQL As String
strSQL = "SELECT * FROM [main table]"
strSQL = strSQL & " WHERE [ID] IN (SELECT [ID] FROM [subtable]"
strSQL = strSQL & " WHERE [dissemination date] = #" & [Enter the
dissemination date] & "#)"
Me.RecordSource = strSQL

So, you're only referring to the dissemination table in the WHERE clause.
Of
couse, I don't know the names of your tables or fields, so I just made
these
up. You'll have to put in your actual names.

Carl Rapson

Carl - thanks for your reply! I confess I haven't done a lot of
manipulation
of queries in SQL (I just use the design view since usually my queries
are
pretty simple), so I'm not sure how to have a query use a table's
values
as a
parameter but not actually pull values from there. Would you mind
explaining
how I can do that? (Or pointing me to a resource?)

Thanks so much!
Jen

:

I'm using Access 2000.

I have a form with a subform; the main form contains information
about
particular news items, and the subform has information for when and
how
each
of the items has been disseminated. The data is in two tables, in a
one-to-many relationship. The form works fine.

I want to run a parameter query that pulls a list of items that, for
example, were disseminated on a certain day (this info is in the
subform).
I
can run this query no problem. But what I'd like is for it to open
the
form,
filtered to show only the items from the parameter query.

However, Access help tells me the following:

Requirements for using queries as filters
A query must meet certain requirements if you're going to use it as
a
filter. The query: must be based on the same underlying table or
query
as
the
datasheet or form to which you're applying it; can't include other
tables
or
queries (etc.).

So, it looks like I can't use this parameter query - which pulls
information
from both the main items table and the dissemination table - as a
filter
for
the form (which is based only on the main items table).

Is there another way to accomplish what I want to do here? I could
do
my
updates in the query, but that doesn't really give me the full
functionality/ease of use I'm looking for.

Your suggestions would be much appreciated!

Thanks,
Jen



It seems to me that your query needn't pull information from the
dissemination table, it only needs to refer to that table in the WHERE
clause. Your SELECT clause can still pull fields only from the main
items
table.

Carl Rapson