Subform needs results for more than 1 main form record

[maw230]

I have two unbound subforms on an unbound "display form". I managed to link the two subforms by following this method: http://www.dbforums.com/microsoft-access/979668-link-2-subforms.html.

This is great and works well enough, however, by design, subform2 will only display records relevant to the very first record on subform1. I need subform2 to be filtered based on ALL of the relevant records being displayed in subform1 not just the first one.

I realize that how I have designed it will prevent that from happening, but I'm not aware of other methods. Previously, subform2 was based on a query using subform1's records as criteria, but this gave me the same result.

Edit: Merry Christmas

 

[pbaldy]

I would expect to be able to join on the record source table/query of subform1 in subform2's record source. That should enable it to show all records related to those in subform1. Can you post a sample of the db here?

 

[CJ_London]

your subform 2 recordsource needs to include your subform 1 recordsource as well

e.g.
subform1 recordsource=query1
subform2 recordsource =query2

modify subform2 recordsource to

"SELECT * FROM Query2 INNER JOIN Query1 ON Query2.SomeValue=Query1.SomeValue"

 

[pbaldy]

I must not have said that satisfactorily.

 

[CJ_London]

it was late, I didn't read what you said properly

 

[maw230]

Both subforms had tables as recordsources. Subform1 = table 'Main'. Subform2 = table 'Notes'. I changed subform2's recordsource to the following:

SELECT Notes.Notes, Notes.[Notes Added Date], Notes.[Added By], Notes.[Doc Link], Notes.[Follow Up], Notes.Main_IDfk
FROM Main INNER JOIN Notes ON Main.ID = Notes.Main_IDfk
GROUP BY Notes.Notes, Notes.[Notes Added Date], Notes.[Added By], Notes.[Doc Link], Notes.[Follow Up], Notes.Main_IDfk;

Should this have worked? It didn't. Do I need to undo the linking method I described in the first post?

Edit: Can I join subform2's recordsource to subform1's (table), so that when subform1 is filtered it reflects in sf2?

 

[maw230]

post christmas holiday bump.

Edit: In a nutshell, subform1's recordsource = table 'Main' that is filtered by unbound combo boxes. Subform2's recordsource is a query that is joined to table 'Main'.

 

[JHB]

Can't you use the answer from pbaldy and CJ_London?
If not the post your database with some sample data + information in which form you have the problem.

 

[CJ_London]

Well, now i can see what you have in your subforms, I can offer a different solution.

What I would do now is slightly different.

In your subform1 current event put the following code:

Parent.[COLOR=red]Subform2[/COLOR].Recordsource="SELECT Notes, [Notes Added Date], [Added By], [Doc Link], [Follow Up], Main_IDfk
FROM Notes WHERE Main_IDfk=" & Me.ID

You need to change the bit in red to the name of your 2nd subform control.

I'm not sure why you are grouping in the second subform so have left it out but you can reinstate if required - but since you are not summing, counting etc, you should really use SELECT DISTINCT rather than GROUP BY

It is generally a bad idea to have field names the same as the table name so you may want to consider changing that. I'm sure you are also aware that having spaces in field names is not a good idea and can cause problems when trying to debug. For example, the VBA editor will replace spaces in field names with underscores when they are referred to via the me collection.

 

[maw230]

In your subform1 current event put the following code:

Parent.[COLOR=red]Subform2[/COLOR].Recordsource="SELECT Notes, [Notes Added Date], [Added By], [Doc Link], [Follow Up], Main_IDfk
FROM Notes WHERE Main_IDfk=" & Me.ID

You need to change the bit in red to the name of your 2nd subform control.

Does this code assume that I have parent/child links setup that I described previously?
I'm sorry, but I'm not sure which control on Subform2 you are referring to.

I can post a sample db if needed. What format should I save it in?

 

[CJ_London]

I've gone back to your original post to see what I've missed and come back to what was proposed by Paul and I initially.

Whatever you have used as the linkchild value in subform 1 needs to be used as the linkchild value in subform2 - which means in the query you posted in #6, you need to bring through from your Main table whatever field you are using as this value

 

[maw230]

All records are shown until subform1 is filtered using combo boxes with code similiar to the following:

subform1.Form.Filter = "[Analyst]= '" & cmb_Analyst & "'"

In this case, subform2's recordsource will not reflect those filters since it is tied to the table that subform1 is based on and not the values displayed in subform1 itself.

In the beginning, subform2's recordsource was a query that used a control on subform1 as criteria, but again it would only use the first record in subform1.

So, I thought it would make sense to setup the child/master links. After doing so I realized that again subform2 would only be filtered on the first master value of subform1 (possibly unless I manually cycled through the records of subform1 and requeried subform2).

 

[CJ_London]

so you need to apply the same filter to subform2

 

[maw230]

so you need to apply the same filter to subform2

Makes sense, but is this the best approach? I suppose I thought linking them with parent/child and/or using recordsource would be preferred.

If I used your suggestion, I would need to add fields to subform2 and then I would need to hide them since they should not be displayed. Then I would need to repeat the subform1 filter code and apply it to subform2.

 

[CJ_London]

you don't need to have controls for the fields you don't want to display

 

[maw230]

you don't need to have controls for the fields you don't want to display

True enough. I would have to add fields to the recordsource, but not controls.

I can't help but wonder again though if I should perhaps redesign my forms, so that I don't need to jump through hoops to apply filters. Or, is your suggestion the only way to filter these two forms to behave as I would like?

 

[CJ_London]

is your suggestion the only way to filter these two forms to behave as I would like

There is often more than one way to solve a problem, which is the best? With only knowing what you have told us it is impossible to advise.

It sounds like you have a main form which is doing the filtering in some way and it is normally the case that subform 2 works off a subset of subform 1 - i.e. select a record in subform1 to display the related data in subform2. But this is not what you want.

 

[maw230]

select a record in subform1 to display the related data in subform2. But this is not what you want.

That's correct. I want subform2 to display related data for every displayed record in subform1 before and after subform1 is filtered.

 

[maw230]

so you need to apply the same filter to subform2

Rather than trying to explain why this may or may not work I will just post a sample db. Don't expect it to qualify for any awards.

 

[maw230]

I believe this is solved for now. Thanks for the help.