Filtering records from a table/query

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
68
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,983
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 17, 2014
Messages
1,930
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

Board Regular
Joined
Mar 5, 2018
Messages
68
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,014
Messages
5,575,549
Members
412,677
Latest member
Davejf81
Top