Populate a sub form from combo box's in main form

F

flyingBlind

I am trying to create a form to view and edit sheduling information which is
generated by running a query.
I would like to create a form that,
1. When opened displays a group of combo box's in the main form.
2. A sub form that shows all values of the sheduling query.

Using the combo box values the user can select values that filters the
information displayed in the sub form.
 
S

Sprinks

Hi, flyingBlind.

The general strategy is to attach code to a command button after the user
has completed entering his criteria that will build a filter string which you
can assign to the subform.

In the AfterUpdate event of each combo box, you would recalculate the filter
string. It's easiest for access if you then write it out to a control on the
form, which can be invisible:

' Each Combo Box AfterUpdate event
Call WriteFilterString

The procedure WriteFilterString loops through the combo boxes on the form
and writes/appends the filterstring to the invisible control on the form.
The way the code is written assumes that the *field* you'd like to filter by
is the same as the name of the control into which you enter the criteria,
less the first three letters, i.e., "cbo":

Private Sub WriteFilterString()

Dim ctl As Control
Dim ctlValFld1 As Control

On Error Resume Next

'Reinitialize invisible control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If ctl.ControlType=acComboBox Then
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) - 3)) _
& "]=" & ctl.Value & " AND "
End If
End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Then, when you wish to filter the subform, add code to a command button:

Me!MySubForm.Form.Filter = Me!txtFilterString
Me!MySubForm.Form.FilterOn = True
Me!MySubForm.Form.Requery

Sprinks
 
Top