Filtering to a sub-sub-form record based on listbox selection

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
68
Our client data is maintained through a form (For the company), a subform (for branch offices), and an unbound subform container which binds with various forms depending on a selected tab control. The default tab is for Individual clients.

I have a search form that is opened from a button click event on the main form. The search form is for individuals. I would like to double click to filter directly to the selected individual, but being 3 levels deep, I'm struggling with the code. I have enough that the main form (Company level) will filter. I thought a series of successive code lines moving from company ---->Branch ----->Individual would work. Am I on the right track? Can anyone help me straighten out the coding to filter at the necessary level? The names are Clients3, frmBranches, and then the unbound subform container binding to frmAdjusters.

This code works for filtering to the correct company, but my efforts to filter farther have fallen flat.

Much thanks in advance to anyone that can help me out!

VBA Code:
Private Sub ctlFilter_DblClick(Cancel As Integer)

Forms.Clients3.RecordsetClone.FindFirst "[Client_ID] = " & Me.ctlFilter.Column(1, Me.ctlFilter.ItemsSelected)
Forms.Clients3.Bookmark = Forms.Clients3.RecordsetClone.Bookmark

End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,087
Office Version
  1. 365
Platform
  1. Windows
Suggest you use Forms! not Forms. - not that that is your problem. You don't say why you want to refer to a subform container, but no matter. The bigger issue for me is clarity. The 2nd subform is on the main form also or the other subform? If the latter then perhaps:
Forms!frmMainName.SubformControlName.Form.SubSubformControlName

Substitute your own object names and note that I'm referring to the subform controls by pseudo names and not the subforms themselves. Your subforms and their subform containers might be named the same - I don't allow that.
EDIT - don't alter the bold parts.
 
Solution

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
68
Suggest you use Forms! not Forms. - not that that is your problem. You don't say why you want to refer to a subform container, but no matter. The bigger issue for me is clarity. The 2nd subform is on the main form also or the other subform? If the latter then perhaps:
Forms!frmMainName.SubformControlName.Form.SubSubformControlName

Substitute your own object names and note that I'm referring to the subform controls by pseudo names and not the subforms themselves. Your subforms and their subform containers might be named the same - I don't allow that.
EDIT - don't alter the bold parts.
Micron,

Thanks for the suggestion. It worked! I can't tell you how much I appreciate it! I'm posting the full sub here in hopes that it might help someone else in the future.

VBA Code:
Private Sub ctlFilter_DblClick(Cancel As Integer)

Application.Echo False

Forms.Clients3.RecordsetClone.FindFirst "[Client_ID] = " & Me.ctlFilter.Column(1, Me.ctlFilter.ItemsSelected)
Forms.Clients3.Bookmark = Forms.Clients3.RecordsetClone.Bookmark

Forms!Clients3.frmBranches.Form.RecordsetClone.FindFirst "[Branch_ID] = " & Me.ctlFilter.Column(2, Me.ctlFilter.ItemsSelected)
Forms!Clients3.frmBranches.Form.Bookmark = Forms!Clients3.frmBranches.Form.RecordsetClone.Bookmark

Forms!Clients3.frmBranches.Form.SubformContainer.Form.RecordsetClone.FindFirst "[Adjuster_ID] = " & Me.ctlFilter.Column(0, Me.ctlFilter.ItemsSelected)
Forms!Clients3.frmBranches.Form.SubformContainer.Form.Bookmark = Forms!Clients3.frmBranches.Form.SubformContainer.Form.RecordsetClone.Bookmark

Forms!Clients3!frmBranches.Form.[BranchList].Requery

Application.Echo True

End Sub
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,087
Office Version
  1. 365
Platform
  1. Windows
Lucky guess by me, I suppose. Seems like there ought to be an easier way than making all those clones and such. Setting the sub-sub control source object and the linked master/child fields in code maybe? After all, bound forms usually eliminate a lot of code. But you got it working, so might as well stay the course I guess.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,733
Messages
5,638,048
Members
417,000
Latest member
JasonWilliam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top