Filter Subforms

[primagic]

I have a parent form with two subforms

Subform A - frmFilteredLeads in this example is a datasheet view. When I select a record in that form it filters and displays the details of the record in subform b - frmLeadsSubForm.

I have put the following code to the OnCurrent event of frmFilteredLeads.

Forms!frmLeads![frmLeadsSubForm].Form.Filter = "[LeadID] = '" & Me.LeadID & "'"
Forms!frmLeads![frmLeadsSubForm].Form.FilterOn = True

However, when I open the parent form I get the error message:

You entered an expression that has an invalid reference to the property Form/Report

If i click end on this error message, the form loads and the filtering works fine.

I have tried putting the above code on the OnLoad event of the parent form and the error message doesnt appear but then the filtering doesnt work.

 

[dhookom]

The method I use is to place a hidden text box on the parent form [txtLeadID] and set the value of this text box in the On Current event of frmFilteredLeads:

   Me.Parent.txtLeadID = Me.LeadID

Then use the Link Master/Child properties of frmLeadsSubform like:
Link Child: LeadID
Link Master: txtLeadID

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

The hidden field is working fine however, the link master/child isnt working. the details form isn't changing

 

[MajP]

This technique should work. Likely your names are incorrect.

can you provide the properties of the subform control:
linkChildFields:
LinkMasterFields:
The name of your linking text box.

 

[primagic]

linkChildField: LeadID
linkMasterField: txtLeadID
Name of linking textbox: txtLeadID

I am using an sql backend. Would this make a difference?

 

[MajP]

I think it should still work because the subform control filters at the form level.
Do you have any leftover code from your original attempts? This should alleviate any other code. Ensure you are still not filtering the subform, and double check that the filter property of the subform is empty. In your code and designing you may have inadvertently saved a form filter.

 

[primagic]

checked it all and nothing there.

when I added to subform to the parent form using the wizard though where it allows you to select the fields to link, the txtLeadID never showed up so had to type it manually

 

[dhookom]

so had to type it manually

So are the forms working as desired now or do you still need assistance? If you need assistance, please reply with what you did, your code, and your results since we can't see your computer screen.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

yes that is correct the wizard will not allow it, and you have to do it manually.
Not sure what to say. This technique usually works well. There is likely some minor thing that is missing or incorrect.

Going back to your initial strategy. Your problem is that the subform loads before the main form. So when you call this

Forms!frmLeads![frmLeadsSubForm].Form.Filter = "[LeadID] = '" & Me.LeadID & "'"

The main form does not exist yet. You can trap the error, and then figure out some workaround for the initial conditions. You could set the intial filter for both subforms in their on load event.

on error goto errLbl
'this throws an error when the form is loading because
'the parent form does not exist yet
me.parent.frmLeadsSubForm.Form.Filter = "[LeadID] = '" & Me.LeadID & "'"
...
exit sub
errLbl:
if err.number = xx 'not sure what the number is then
exit sub
else
msgbox err.number & " " & err.description
end if

 

[primagic]

No the forms are not working.

I added to subforms to a parent form

The first subform, frmFilteredLeads displays a list of leads for the current adviser logged in. this works fine.

I then added the txtLeadID on the parent as per your advice and on the current event of the frmFilteredLeads added your code which works 100% updating the txtLeadID on the parent form with the select record on frmFilteredLeads.

I then added a second subform, to the parent form and changed the linked fields as per your advice. However, this subform is not updating after the selection is made in the first subform.

 

[MajP]

Sounds like your are doing things correctly, and maybe there is an issue with using a SQL back end. My first guess would be no.

Not sure if this will help, but here is an example using the discussed technique

http://www.4shared.com/document/a-mEoboo/SubForms.html

Maybe you will see something that you are/are not doing.

 

[primagic]

Thanks for your help anyway. I will try to work around it.

Do things another way

 

[MajP]

Yes you can do it manually. Like I said just handle the case when the subforms loads because you can not reference the parent form. You have to catch the error.

 

[primagic]

Fixed it.

I just added requery code to the current event of the first subform and it works perfectly.

Thanks both of you for all your help