Subform Filtering (1 Viewer)

[kodehunt]

I have a form "fApprovalPreLoad" with two Combo Boxes, "CBEmployee" and "CBDate". These two Combo Boxes are used to filter another form with several subforms. The code below works great and filters everything fine unless the comboniation of CBEmployee and CBDate returns no results. If no results are returned I get the error message "You entered an expression that has an invalid reference to the property Form/Report".
CardLink = "[StatementDate]= #" & Forms!fApprovalPreload!CBDate.Value & "# AND [Name] = '" & Forms!fApprovalPreload!CBEmployee.Column(3) & "'"
ReimPayLink = "[StatementDate]= #" & Forms!fApprovalPreload!CBDate.Value & "# AND [EmpID] = '" & Forms!fApprovalPreload!CBEmployee.Value & "'"
fMGRCardCharges.Form.Filter = CardLink
fMGRPayments.Form.FilterOn = True
fMGRExpReimb.Form.Filter = ReimPayLink
fMGRExpReimb.Form.FilterOn = True
fMGRMileReimb.Form.Filter = ReimPayLink
fMGRMileReimb.Form.FilterOn = True
fMGRPayments.Form.Filter = ReimPayLink
fMGRPayments.Form.FilterOn = True
Because there are several subforms on the parent form, I can't simply pop-up a message saying there are no recordsl, as they may need to use other functions on the parent form.
Any help is greatly appreciated.

 

[billmeye]

Have you considered using Master/Child form linking instead of filtering?

Prior to assigning your filter you can test for values and only apply the filter if they are present.

 

[kodehunt]

I started with child/parent filtering and for some reason ended up passing the filter via VBA, but I cannot remember why. I will revisit child/parent again and let you know, thanks for the idea.

 

[kodehunt]

So I went back to parent/child filtering and remember what my problem ws. When using the parent/child filter method, the form continually refreshes and flashes. Any thoughts on that?

 

[billmeye]

Do you have any code running in On current, After Update etc. calling for a refresh or requery?

 

[kodehunt]

Exactly right. I had an After Update running on a Parent Combo Box. It's value was a DLookup where I had it set to .value. Once I Just let it default and removed the .value at the end it stopped refreshing. Thank you very much for all of your help.

 

[billmeye]

Glad I could help. Good luck with the rest of your project.