Filtering records from a table/query

[mikenelena]

I am changing record sources of a subform based on the selection of a combo box in a parent subform. My overall structure looks like this: Main form is Clients. Embedded into that is a subform for Branch Offices. Embedded into that subform is a subform container featuring tab controls. One of those tabs is for a schedule of fees.

A combo box on the branch level subform holds the options Standard & Custom. Depending on which is selected, the subform container changes record sources between the 2 tables mentioned.

The filtering for the Custom fee schedule currently works based on Branch_ID. However, some companies have dozens of branch offices and with about 30+ items on the fee schedule, I don't want to repeat data in the table unnecessarily. I would like to filter based on Client_ID instead of on Branch_ID, as this seems easiest. Client_ID is a numeric field in the table, and a matching value exists in a control in my form, and on both subforms. I can't seem to get this to work though.

The filter fields in my form are blank. I'm trying to handle this with VBA. I've tried this line, and all kinds of variations on it.

Me.Filter = "Client_ID = " & Client_ID
Me.filterOn = True

I know I have the code wrong, but am I at least going about this the right way? Should I be filtering at the form instead of in VBA?

The second part of my question is this. Can I filter on the Branch_ID and avoid a table full of repeating data somehow?

Thank you all for any assistance you might be able to provide me!

...Mike

 

[Micron]

I've tried this line, and all kinds of variations on it.

And that would be where exactly?
If data is available to these forms then you should be able to filter by it. Your issue is probably not having the correct reference for the method you've chosen. This also means that you could build the filter clause in vba and apply it. Either way, you need to know the hierarchy of the forms and how to refer to the controls involved. For this I usually go to the immediate window to get the reference right. The Name property is always (?) a property of any control so I'll start with (using the names as I know them)
?Forms!frmMain.subformControlNameHere.Name and hit return. This would give me the name of the subform control on the main form. Then it's a matter of drilling down while referring to the name of an object or even a property value as in
?Forms!frmMain.subformControlNameHere.ControlSource (once you know the reference is correct because .Name worked).
Note - you cannot cut corners by using Forms! and starting with the subform name because IIRC, loaded subforms are not members of the Forms collection.

 

[ranman256]

underscores & spaces don't work well as object names. Use brackets if unsure.
if you uses the BUILDER to select object off a form, you'll see access 'renames' them into a usable form. Its probably:
Me.Filter = "Client_ID = " & me.[Client_ID]

 

[mikenelena]

Thanks Micron, and ranman256... I've had to put this particular challenge on brief hold as others with higher priority have cropped up in this database that I've "inherited"... I will be circling back to this however, and will mark a solution.