Form filter (1 Viewer)

[sponsoraw]

I've got a hopefully an easy question - how to create a form filter?
I've got a form with a subform. Form is showing all records from a table, but some records on the subform are blank. I want to create a filter which will eliminate any record from the form where there is nothing to show in the subform.

Any help will be appreciated.
Thanks

 

[CJ_London]

You'll need to substitute for your field and recordnames but the recordsource for your main form will be along the following lines.

SELECT * FROM MainTbl WHERE Exists(SELECT Top 1 * FROM SubTbl as TMP WHERE tmp.ParentFKFld=MainTbl.PKFld)

 

[sponsoraw]

Thanks CJ_London for reply. I'm a bit of a novice here so could you explain a bit more where do I put that code please?

 

[CJ_London]

sure - in the recordsource for your main form - don't forget to substitute for your field and recordnames.

MainTbl is your current recordsource for the main form
SubTbl is your recordsource for your subform
ParentFKFld is the name of the field used in your subform LinkChild field
PKFld is the name of the field used in your subform LinkMaster field

 

[sponsoraw]

Thanks for expiaining, however it is showing all fields.
Any ideas?

 

[CJ_London]

That is because of SELECT * which selects all fields (your post does not detail what fields you want). Even though you may seem them in the recordsource query, they don't have to display in the form.

If you want to reduce the number of fields just change

SELECT *

to

SELECT Fld1, Fld etc

for the fields you want

 

[sponsoraw]

I will need all fields from the source table for the form to display correctly, however not all records need to show, as some of the records on the form have their associated subform information blank and that is what I'm trying to eliminate. I hope it makes sense.
Thanks

 

[CJ_London]

that is what the query is supposed to do - 'remove' from your datasource so you only see records where relevant matching records exist in the subform.

If you look at the query it is saying "bring all fields through for records where matching records in the subform recordsource exists"

If it is not doing that then I suspect you have not converted my query correctly or there is some other factor which may be causing it.

Can you post the sql to the recordsource of both your main form and your subform and confirm the settings for the subform linkChild and linkMaster properties

 

[Simon_MT]

Trying building your Form the opposite way around. Bottom > Top. Put the Subform in the detail and the master information in the header and qualify the data based on the bottom information. If the record is blank then nothing will appear providing you are searching populated records.

Simon