Really Stuck . . Form structure guidance please

[buckwheat]

inherited database . . . there is a form that has at least 10 cbos for providers and 15 cbos for groups. Currently each cbo opens the same form containing many subforms, the source is a query that references the form and subform names, etc., Forms!Search!cboDrID . . . etc.

I want to create one form with all cbos and a subform for values (subform will be hidden until cmd is clicked). I'm familiar with VBA for an AfterUpdate Event that references one cbo and an OnCurrent Event that references one cbo. Can anyone advise please what I need to do with the AfterUpdate and Current Events when there are so many cbos??


Private Sub providername_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[GroupNo] = '" & Me![providername] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub




Private Sub Form_Current()
cboGroup = GroupNo

End Sub

[June7]

Why are there 10 cbos for providers and 15 cbos for groups? Makes me suspect non-normalized data structure.

I don't understand "the source is a query that references the form and subform names, etc." - source of what references which form and subforms?

I don't use dynamic parameterized queries as recordsource for forms and reports - I use VBA to construct filter criteria and pass it to the opening form or report.

[buckwheat]

I probably should have communicated a little clearer . . . sorry . . . the previous developer created one form "Search Options" with contains 10 cbo boxes to filter 10 different ways for provider data, etc., cboID, cboProviderName, cboTitle, etc. Same for the 15 that represents 15 different ways to sort by group, etc., cbogroupname, cbogrouplocation, etc.

currently one form opens "frmProvider" regardless of which cbo is selected. The "frmProvider" source is a query "qryActive2" The query is a consolidation of all tables . . providers, groups, etc. This query references the cbo boxes . . Forms!SearchOption!cboProviderName

[June7]

So you still want parameterized query but instead of opening another form, just have records show on the form with the comboboxes?

Not sure how all the subforms fit into this filtering. Maybe just put comboboxes into the form header section of the other form.

[buckwheat]

That was my plan, but I need to be able to modify the VBA code I provided in the initial thread to accommodate all the combo boxes so data values returning to the form focuses on the cbo selection. Would I repeat this segment of the AfterUpdate Event for each cbo? rs.FindFirst "[GroupNo] = '" & Me![providername] & "'" and the same question for the Current Event cboGroup = GroupNo

[June7]

Don't understand why you are wanting that VBA code when you have a parameterized query. If you want multiple criteria, options are the parameterized query or VBA that constructs one long criteria string from all the combobox inputs. Can't have each combobox set the filter individually, only the last executed event would apply.

[buckwheat]

too much time on this one subject. I'll move on to another approach. Thanks for your comments.