Query from form to return all results

[maw230]

I have a form that displays multiple records in datasheet view. I want to query off of that form and return all results for all records being displayed. However, I'm not sure how to tell Access to do this. Access instead wants to return records for only the first displayed record in the form or the record that is highlighted by the cursor.

In the attached image you can see a list of AccountingUnits (AU) going down. My query using AU from the form as criteria will only return records for AU 114510 since it is the first record, but I would like to see records for all AU's being displayed by the form.

How can I do this?

 

[maw230]

Beginning to wonder if this is possible?

 

[namliam]

The "current" record is the only record that you can reference in access, in any direct form.

You want to see all records "being displayed" do you mean you want the records ACTUALLY visible or the ones also visible on the form if you scroll, i.e. the recordset from the form?

 

[maw230]

The recordset! I feel like you're on to something here.. The recordset of the form, will it include applied filters? The main form has combo boxes that filter subform1, so after subform1 is filtered will that be reflected in the recordset?

 

[namliam]

Well the subform will either have a rowsource that has a where clause or have a Filter active....

However if you know how the filter is build with the combobox, simply send the filter to the new/other form as a where clause....

 

[maw230]

For the sake of clarity...
There is an unbound main form with 2 unbound subforms. The filters are on the main form and apply to subform1. I want to then query off of subform1 recordset to populate subform2.

Does this change your answer at all?

 

[namliam]

Basicaly no, needs to tweak it a little, BUT... if you have the ability to add the filter to one subform then you should be able to make the same filter to another subform quite easily? Just copy/paste what you did to subform1

 

[maw230]

The issue there is that the filter only applies to subform1. If I apply it to subform2 it won't give me the results I need. Let me try to explain.

The filter is Analyst. When I choose an Analyst subform1 shows all of their AccountingUnits (AU). I need subform2 to show all of the Notes for those AUs, not for that Analyst.

 

[namliam]

You want to show the notes related to all the AUs? or only the AU selected?

If you want to show all the notes for all AUs you "simply" make sure you join the notes in such a way that you have the Analyst in there, so you can join the subform on that (hidden) column.

 

[maw230]

You want to show the notes related to all the AUs? or only the AU selected?

If you want to show all the notes for all AUs you "simply" make sure you join the notes in such a way that you have the Analyst in there, so you can join the subform on that (hidden) column.

I want to show the notes related to all the displayed AUs.

So I need to add Analyst to my Notes subform recordset? There are multiple filters; Analyst is just one of them.
When you say "join the subform" are you referring to the record source? I think you may have lost me.

 

[namliam]

Well presumably you have a table with the AU's in it, then have a NOTES table that has a foreign key to the AU table?

The one subform is the AU form, the other the NOTES one, both have the tables as their recordsource. The notes one needs to have a QUERY as its recordsource, that joines the NOTES and AU table. You can then filter on anything AU related, without it being visible and without much overhead.

 

[maw230]

Well presumably you have a table with the AU's in it, then have a NOTES table that has a foreign key to the AU table?

The one subform is the AU form, the other the NOTES one, both have the tables as their recordsource. The notes one needs to have a QUERY as its recordsource, that joines the NOTES and AU table. You can then filter on anything AU related, without it being visible and without much overhead.

Yes, that is how the tables are set up. I will give your suggestion a try. Thanks!

 

[jdraw]

I suggest you show us a jpg of your tables and relationships. We're really guessing at your structure.

You may get some ideas from this tutorial.

 

[maw230]

Applying the filter to both subforms appears to be doing the trick. I will post back if I hit any snags. Thanks!