Applying a Filter to a Subform From Another Subform

[ItsRoland]

I have a form with a number of subforms, the important ones here being the main form, subform1, and subform2. If a new record is added to subform1, I want to apply a filter to subform2 that only shows records related to the new record created through subform1.

In case this isn't obvious, all the subforms are open when the main form opens, but only the one the user want's to work with at any given moment will be visible. This is changed through command buttons.

I've tried going about this a few different ways:

1. I used a global variable that gets assigned a value anytime a new record is added through subform1, and tried to use that to tell subform2 whether or not to apply the filter. This would have worked, but I can't figure out where to place the code telling it if it should filter or not. I initially placed this in the OnOpen event, but all the subforms open when the main form does, so that doesn't work. I also tried placing it in the OnCurrent event, but that caused Access to crash (presumably because it was constantly checking for the value of the global variable).
The code: (glbvar is set to 0 by default and then set to the appropriate ID value when a new record is added from subform1, in case that wasn't clear)

If glbvar > 0 Then

       Me.Filter = "fieldname = " & glbvar
      Me.FilterOn = True

End If

2. applying the filter to subform2 in the code of subform1, but that doesn't seem to work either. After a bit more digging on this one I don't think it's possible to do that, but I'm not 100%.

3. applying the filter to subform2 in the code of the main form, had a similar result/issue as attempt 2, unsure if this can even be done.


If anyone knows where I should place the code from attempt 1, if I can apply the filter to subform2 from another source like I tried in attempt 2 and 3, or another/better way of doing this, please let me know.



Any help is greatly appreciated.

[ranman256]

You can apply filters but use the entire path. (See builder)
if in sub1, filter sub2 via:

forms!myForm!sub2!form.filter= forms!myForm!sub1!form.filter

[ItsRoland]

I tried what you said, and got the error "Couldn't find field 'Form'", so I changed it up a bit and got it to work

Forms!mainForn!subform2.Form.Filter = "fieldName = " & variable & " "


Forms!mainForn!subform2.Form.FilterOn = True

Thanks for the help!


Side note, while I was testing this I noticed that the lost focus event on one of my controls doesn't trigger properly if I use tab to move away from the control, but does if I click away. When using tab it displays message boxes but doesn't call the function, or at least the function doesn't do anything. But when clicking away everything's fine. Is that supposed to happen or is there something else going on here? Just figured I'd ask while I was here lol