Combo Box Filter

[Guest]

Has anyone ever done this? I have a combo box cboemployid in a form called
employedit; when you select an item in the combo box it should filter all
records associated with cboemployid by account in in a subform called
subform1.....now where I get stuck is, I would like to filter through all the
records in subform1 with another combo box cboaccounts, so that I can see all
records associated by account? Any help is greatly appreciated.

 

[Ken Snell \(MVP\)]

Put cboemployid in the LinkMasterFields property of the subform control (the
control that holds the subform object), and put in the LinkChildFields
property the name of the field that holds the employeeid value in the
subform's RecordSource query. That will link the subform's records to the
value selected in the combo box.

 

[Guest]

Yes Ken, that takes care of part one (form to suform filter) however part two
is where I am really having the problem with.....each employee has about 10
accounts I would like to filter through each account using a combo box in the
subform. kind of like this.
step 1. locate employeeid
step 2. locate account within employeeid
As you scroll through each account within each employee id, all records
accociated with each account should change in accordance to which account you
are looking at within the employeeid. Any ideas? Thanks!

 

[Ken Snell \(MVP\)]

I'd be inclined to add a combo box to the Form Header section of the combo
box that would allow you to select an account number. Then, add a WHERE
clause to the subform's RecordSource query that filters the recordset by
that combo box:

WHERE AccountNumberField = Forms!SubformName!ComboNameOnSubform OR
Forms!SubformName!ComboNameOnSubform Is Null


Then put a requery action on the AfterUpdate event of the subform's combo
box to always requery the subform after you make a selection.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken, are you saying that I should put that code in the row source field?

 

[Ken Snell \(MVP\)]

No. The WHERE clause that I posted would be added to the query that is the
subform's RecordSource. The combo box in the subform's Form Header section
would have a Row Source query that returns the Account Numbers.

 

[Guest]

Thanks for all of your help Ken....it worked =) You're awesome!

 

[Ken Snell \(MVP\)]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thanks for all of your help Ken....it worked =) You're awesome!