K
Kevin Sprinkel
I've never done this quite the way you describe, although
in concept it's similar to presenting a form to filter a
query or report. The general strategy is to use the
selections in the combo box(es) to build an SQL string,
and then set the subform's row source to this string.
The way I build such a string is to create a general
procedure that loops through each form control, adding
another piece of the SQL string with each relevant control
(you'll want to ignore labels, and other controls that
aren't to be used to filter the records. Then at the end
of the procedure, set the Record Source property of the
subform, and requery. Call this procedure in the
AfterUpdate event of each filtering control.
The following snippet loops through a report-filtering
form, writing the SQL string to a hidden form control
named txtFilterString:
Me!txtFilterString = Null
For Each ctl In Me.Controls
' Use the Controls collection of the current form
If ctl.ControlType = acComboBox
' Here I'd named each control the name of the
' field it represents plus the "cbo" prefix,
' conveniently generating the latter from the
' former using LTrim. You could also build it
' explicitly for each control.
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)
HTH
Kevin Sprinkel
appear in a subform (in a datasheet view). For example:
In the sub form there is list of records in a datasheet
view. Once of the fields in each record is "dept". If I
have a "dept" combo box in the main form and select a
value from the combo box, it then would only list the
records in the subform with the specified department. I
would like to have a couple combo boxes in the main form
and any combination of use of these combo boxes
would "filter" the list of records in the subform.
in concept it's similar to presenting a form to filter a
query or report. The general strategy is to use the
selections in the combo box(es) to build an SQL string,
and then set the subform's row source to this string.
The way I build such a string is to create a general
procedure that loops through each form control, adding
another piece of the SQL string with each relevant control
(you'll want to ignore labels, and other controls that
aren't to be used to filter the records. Then at the end
of the procedure, set the Record Source property of the
subform, and requery. Call this procedure in the
AfterUpdate event of each filtering control.
The following snippet loops through a report-filtering
form, writing the SQL string to a hidden form control
named txtFilterString:
Me!txtFilterString = Null
For Each ctl In Me.Controls
' Use the Controls collection of the current form
If ctl.ControlType = acComboBox
' Here I'd named each control the name of the
' field it represents plus the "cbo" prefix,
' conveniently generating the latter from the
' former using LTrim. You could also build it
' explicitly for each control.
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)
HTH
Kevin Sprinkel
used as "selection criteria" for a list of records that
appear in a subform (in a datasheet view). For example:
In the sub form there is list of records in a datasheet
view. Once of the fields in each record is "dept". If I
have a "dept" combo box in the main form and select a
value from the combo box, it then would only list the
records in the subform with the specified department. I
would like to have a couple combo boxes in the main form
and any combination of use of these combo boxes
would "filter" the list of records in the subform.