filtering parent and child records in form and subform (1 Viewer)

[nerther]

Hi,

I've spent countless hours trying to work this out and searching this forum so I hope this question hasn't been asked before!

I have a form and a subform with a master/child relationship set based on the primary key of each underlying table. All good there.

Now, I want to use VBA to create a filter based on a set of inputs via combo boxes. But the filter must filter both the Parent and Child records.

Example. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string".

I can do this in a QRY as follows:

SELECT Projects.[Project Number], Lessons.[Actions Resolved]
FROM Projects INNER JOIN Lessons ON Projects.ProjectsRecordID = Lessons.ProjectsRecordID
WHERE (((Projects.[Project Number])="AU-2102421") AND ((Lessons.[Actions Resolved])=True));

But, if I make this as a record source for the Parent Form, then the records in the Parent Form are repeated for each individual record in the Child form.

Does anyone have any ideas?

I hope this makes sense!

 

[Uncle Gizmo]

It is possible to directly provide your form and your sub form with a record source through vba.

This is very handy especially when you are trying to build complicated queries because you can structure your query in text format in the code behind a form and then push that/those query(s) straight into your forms record source.

 

[nerther]

excellent. How exactly can I do that?

 

[Uncle Gizmo]

for your main Form:
Forms!frmMain.Recordsource = "Your SQL"

Now the subform is tricky, because the subform is not present on your main form, it looks like it is but it isn't! It is actually contained in a subform window, which as one of the more illustrious members here have pointed out to me is not the correct terminology for it.

However you will find it helps in your thinking about it if you assume it's a window.

So now you have your main form on which is a control we are going to term a "Window" and inside this window is your subform. let's imagine you call this window the: Sub Form Window and you might identify it like this:

subFrmWinWithMyFormIn

And to refer to the form in your subform window you don't mention the form by name; it is just the "form" the "form object" contained within your sub form window.

So to refer to its recordsource you would write your code like this:

Forms!frmMain!subFrmWinWithMyFormIn.Form.Recordsource = "Your Other SQL"

 

[nerther]

Thanks for your help Uncle!

your comments made me re-think how a subform works. Now, the approach I took is as you suggested, more or less

In the Parent Load event. I set the Parent record source to show only the parent records I want via an SQL statement.

Then, from the same place, I set the Subforms records to show only the child records that I wan via an SQL Statement. (I release that this as actually after the subform has already loaded with data, but it will do me for now cause all my code for the filtering is in the one place).

Then, MSAccess works out how to join the records and display based on the linked master/child field property.

So we have another happy customer

 

[Uncle Gizmo]

If you set your Subform Record Source to "" it won't show any records until you fill it.

 

[Uncle Gizmo]

Error in Rendering a post.

The Text of my post:

The screen render, and the problem (Using Google Chrome Browser)

 

[nerther]

Ok, Things aren't going as I planned.

I can create the query I want to show using something along the lines of SELECT DISTINCT MasterTable.* from MasterTable INNER JOIN ChildTable on LinkingID.... etc.

This displays the data I want in the master table and subform table.....however, I had to use DISTINCT to prevent the master records being repeated for each of the child records. This then prevents me from being able to edit any fields in the table because I've used the DISTINCT function.....

I must be totally over engineering this....... All I want to do is only show Parent Records where the joined child Field is equal to "search string"

I.e. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string"

I Can't filter out the parent records based upon a child record's field unless I join the query.....which causes duplicates in the parent records when displaying in the forms...

Any help is appreciated!