Filtering records based upon another table

[Brian O'Malley]

I have many combo boxes that get their content from another table. Although
I can build a query that will accurately produce a dynaset based on info in
another table, I can't get the macro to filter records in the form. What am
I doing wrong?

 

[Steve Schapel]

Brian,

Can you please give an example to illustrate what you are trying to do?
And explain the macro you are referring to? Thanks.

 

[Brian O''Malley]

Thanks Steve. I have a contacts database with address info, etc. of business
associates. I store the tiltes in a seperate table that a combo box displays
on the form. When I do a query based on title, the dynaset returns
accurately without any problems. When I do a macro to filter based on the
query it does not return the data. (it will usually display a box asking me
to enter "title.titles" or something similar.

Brian

 

[Steve Schapel]

Brian,

Thanks for the further information. Can you also say please what "do a
macro to filter" means? What are the actions in this macro? And where
is it being run from? And what is is supposed to do?

 

[Brian O''Malley]

Thanks for responding again Steve.

I have macros where the action is "Apply Filter" and the Filter Name is the
name of the query I use for reports that works fine. It is being run from a
form that uses the main table for its data source. Each combo box uses its
own query of the title table for its data and inserts the ID number for that
title in the main table.

The query used in the macro is asking the Title table to return all of the
records in the main table that have the same ID as the one for the requested
title, i.e., if I put the title "manager" in the query box, the query looks
at the title field in the title table and its associated ID, and checks the
Title field in the main table for all records that have matching numbers.
It's supposed to then filter all the records that have the appropriate number.

Does that make sense?

Brian

 

[Steve Schapel]

Brian,

Thanks for the further information.

Can you go to the design view of the query used for the ApplyFilter
macro, select SQL from the View menu, and then copy/paste the SQL of the
query into your reply here? Thnks. Also, can you please give details
of what is the Row Source of the combobox? Thanks.

 

[Brian O''Malley]

Steve, your help is greatly, greatly appreciated.

Here is the SQL from the query for the "county" drop down box:
SELECT [County List].County
FROM [County List]
ORDER BY [County List].County;

The row source for the box is:
County List Query

However, as I went from box to box (there are many on the form) I noticed
some had SQL in the row source. I also noted that when I pressed the button
to activate the filter/query, a different SQL statement showed up, but I
couldn't read it because it's too long and it is only present when the
requesting box is open.

Thanks again.

 

[Steve Schapel]

Brian,

You mentioned that you have a query that the ApplyFilter refers to. Are
you able to post back with a copy/paste of the SQL view of that query?

 

[Brian O''''Malley]

Yes, sorry, thought I had included that.

SELECT Title.Title, Organization.*
FROM Title INNER JOIN Organization ON Title.ID = Organization.Title
WHERE (((Title.Title) Like [Enter Title]));

Brian,

You mentioned that you have a query that the ApplyFilter refers to. Are
you able to post back with a copy/paste of the SQL view of that query?

--
Steve Schapel, Microsoft Access MVP

Steve, your help is greatly, greatly appreciated.

Here is the SQL from the query for the "county" drop down box:
SELECT [County List].County
FROM [County List]
ORDER BY [County List].County;

The row source for the box is:
County List Query

However, as I went from box to box (there are many on the form) I noticed
some had SQL in the row source. I also noted that when I pressed the button
to activate the filter/query, a different SQL statement showed up, but I
couldn't read it because it's too long and it is only present when the
requesting box is open.

Thanks again.

 

[Steve Schapel]

Brian,

Well, it is interesting to see your progression from O'Malley to
O''Malley to O''''Malley

To be honest, I am still fairly confused about what you are doing and
what thre problem is. This query that you have posted, contains a
Parameter [Enter Title] so whenever the query is run, you will be
prompted to provide a value for Title, which the query will then use as
a criterion. Is this what you intended?

 

[Brian O''''Malley]

Yes, that's what I intended. But I get prompts like (and I'm paraphrasing):
Enter [Title].[title] when I run the macro in addition to my [Enter Title]
prompt.

Don't know how my name changed like that either. Although I am checking
the "notify me of replies" box, I'm not getting notified, so I apologize for
the delay in responding.

Brian,

Well, it is interesting to see your progression from O'Malley to
O''Malley to O''''Malley

To be honest, I am still fairly confused about what you are doing and
what thre problem is. This query that you have posted, contains a
Parameter [Enter Title] so whenever the query is run, you will be
prompted to provide a value for Title, which the query will then use as
a criterion. Is this what you intended?

--
Steve Schapel, Microsoft Access MVP

Yes, sorry, thought I had included that.

SELECT Title.Title, Organization.*
FROM Title INNER JOIN Organization ON Title.ID = Organization.Title
WHERE (((Title.Title) Like [Enter Title]));

 

[Steve Schapel]

Brian,

So, is there a field in the Title table named Title?

Please give the exact wording of the parameter being prompted for.