Query Criteria from a Subform

[cm2013]

Hello!

I'm new here but I've used this forum quite extensively in the past to figure out answers to my questions. I have a moderate amount of Access experience, and I'm using Access 2010, (though the database in question is 2002 - 2003 format). I looked through this forum and have searched on google and found a few answers that were close to my situation, but nothing that truly dealt with the problem. Hopefully I can explain it in a way that isn't too confusing.

I have a form (called Search) that has two combo boxes on it that user can choose some values from and then a subform (called Parts Header subform) displays the results from a table. This works great.

What I want to do is use a field from the results of that subform to query a different table and display that. Though the tables are different, there are definitely matches between them. [Before you say I'm repeating information needlessly these tables actually come from two different databases my company uses and I have no control over anything about those data sets, only my interface with them.]

Here is the SQL I have right now:

SELECT dbo_RepairDB.[Sales Order], dbo_RepairDB.[Base Part Number], dbo_RepairDB.Description, dbo_RepairDB.Site, dbo_RepairDB.[Parent Name], dbo_RepairDB.[Sale Tot], dbo_RepairDB.Invoiced
FROM dbo_RepairDB
WHERE (((dbo_RepairDB.[Base Part Number])=[Forms]![Search]![Parts Header subform].[Form]![BASE PN]) AND ((dbo_RepairDB.Year)>2011));

​

This query does work, but ONLY for the first record in the subform.

For example if my subform has results in the BASE PN field of a, b, c, d, e it will only display results for a in the query when it pops up but I want it to use all five criteria.

Just to prove that I've done a bit of homework on this already, I've already read this thread: (It won't let me post links but it's post 1300908 that deals with filter by form) but I really need my results to be in a query and not a report and when I tried to adjust the information in that thread I found out you couldn't pass stWhere to a query.

I also read this thread: (Post 196581) which deals with the same problem I'm having, but the answer he came up with was some looping code and I have to believe there's an easier way to handle this.

Thanks so much for reading my mini-novel!
CM

 

[CJ_London]

in your subform current event you need to requery what is presumably another subform or listbox which contains the sql you posted

the code would be something like

Parent.OtherSubFormName.Requery

 

[cm2013]

Hi CJ, thanks for your lightning-speed respose!

I'm a little confused about what you mean? Right now I have a button on the form that runs the query that contains the SQL code I posted because I don't always need the query to run, only when I need that additional information from the other table, perhaps 1 out of 4 times I use this search form. I've only got one form and one subform. What am I supposed to be requerying?

-CM

 

[CJ_London]

Sorry, misunderstood.

A subform can only have one current record, whereas what you actually require is a list of what is in the subform.

You need to change your query to include the recordsource from your subform plus the filtering that is done when you select the values from your comboboxes and perhaps also the linkmaster and linkchild fields, as another query (lets call it qrySearch) and then link so it would be something like

SELECT dbo_RepairDB.[Sales Order], dbo_RepairDB.[Base Part Number], dbo_RepairDB.Description, dbo_RepairDB.Site, dbo_RepairDB.[Parent Name], dbo_RepairDB.[Sale Tot], dbo_RepairDB.Invoiced
FROM dbo_RepairDB INNER JOIN qrySearch on dbo_RepairDB.[Base Part Number])=qrySearch.[BASE PN]
WHERE ((dbo_RepairDB.Year)>2011);

 

[cm2013]

Aha! That I understand. Okay, let me tinker with it a bit and see if I can make it work.

 

[cm2013]

That worked like a charm. Thanks so much CJ!

For future reference if anyone else has this problem I went into the subform record source and saved that as its own query (qrySearch), then I pulled that into a second query that referenced the second table, made a join between qrySearch and the table.