Solved: Filter main form based on subform

[BIRD_FAT]

I've been banging my head against the wall with this one for a day now, someone put me out of my misery, PLEASE!


I have a main form: FrmMacroCode, and a subform: SubProgram

on the main form the field MacroID is the primary key, it is linked in a One-to-One relationship to the MacroID field of the subform (also Primary Key). there is also another field on the subform: MacroProgram with the various office program names as a Value List: "Word";"Excel";"Access";"InfoPath";"Outlook";"PowerPoint";"Publisher";"OneNote";"General"

I have a command button on the main form: FilterWord through which I am trying to apply a filter, based on the subform, which would allow me to only see, on the main form, data related to the value "Word" on the subform.

Here is what I have:
Private Sub FilterWord_Click()
Me.FilterOn = False
Me.Filter = "Forms![FrmMacroCode]![SubProgram].Form.[MacroProgram]= '" & "Word" & "'"
Me.FilterOn = True
End Sub
I've tried all sorts of combinations of the Me.Filter =... line, but I'm obviously missing something!

Any ideas, Ladies and Gents?

[OBP]

You have lost me there, you are referencing the Subform, but have a Constant "Word" as well with 2 equals signs.
Can you run through what you are trying to do again, are you trying to enter or choose something in the Subform that filters the mainform?
Or are you trying to enter or choose something in the main form to filter the main form or the Subform?

If you use the me.filter it means you are referencing the mainform's filter.

[BIRD_FAT]

My Bad!

Originally I was thinking that I would need a One-to-Many relationship, so I built a seperate table and linked the MacroID fields, then I realised (as I was posting) that I needed a One-to-One relationship. But, because I had been having trouble getting it to work, my braincells didn't wake up enough to realise that that meant the table was superfluous to needs:bug:, so I had been trying to do the unnecessary - DOH!:doh:
Thanks to your question (and 8 hours sleep!), I now see what I was doing wrong and have removed the subform and added the correct field into my main form, and remade the filters correctly!:whistle:

It was your reference to Me.Filter being linked to the main form that did it OBP - So thanks for the nudge!

Now - with field in main form
Private Sub FilterWord_Click()
Me.FilterOn = False
Me.Filter = "[MacroProgram]= '" & "Word" & "'"
Me.FilterOn = True
End Sub

Sorry for the waste of time there!!

**:motz2:NOTE TO SELF - SLEEP ON IT BEFORE POSTING STOOOPID QUESTIONS!!:motz2:**

Thanks again OBP!

[OBP]

Glad to help, I think we have all been there and done that.