Flexible Combo Box Filtering

  • Thread starter Thread starter Jonny
  • Start date Start date
J

Jonny

I am wondering if it is possible to create a form which
contains a subform that is controlled by a system of
combo boxes on the main form. However, I would like it
so that if the combo boxes are blank all employee
entries are displayed but they can be limited by using
the combo boxes in any combination(i.e. cboA+cboB or cboB
+cboC). I am also not sure if this requires the use of
filters since I have had little experience with the use
of filters and if so an explanation of what they are and
what they entail would be helpful.

Also if this message would be better posted in another
newsgroup please let me know.

Thanks,
Jonny
 
-----Original Message-----
I am wondering if it is possible to create a form which
contains a subform that is controlled by a system of
combo boxes on the main form. However, I would like it
so that if the combo boxes are blank all employee
entries are displayed but they can be limited by using
the combo boxes in any combination(i.e. cboA+cboB or cboB
+cboC). I am also not sure if this requires the use of
filters since I have had little experience with the use
of filters and if so an explanation of what they are and
what they entail would be helpful.

Also if this message would be better posted in another
newsgroup please let me know.

Thanks,
Jonny
.
Click to expand...
Hi Jonny,
try this... have the subform recordsource a saved query -
for example named qrySubform.

Possibly have a command button that updates the
recordsource of the subform to match the combobox selection
(s). Use the following example...

dim strSql as string
dim strWhere as string

if not isnull(cboA) then
if len(strSql)>0 then
strWhere=strWhere & " AND "
end if
strWhere=strWhere & "[fieldname]=" & cboA
end if

' repleat for cboB and cboC

if len(strWhere)>0 then
strWhere=" Where (" & strWhere & ")
end if

strSql="select * from qrySubform" & strWhere & ";"

me.subform.form.recordsource=strSQL

Luck
Jonathan
 
Jonny,

I have included some of the code that is called from the
AfterUpdate event of a combo box on a form that also has a
couple of option groups and other option posibilities.
Please note that I have tried to document the items that
are being evaluated when developing the sql statement for
use as the record source for the list box.

'code starts here *****
Private Sub cboOp633_AfterUpdate()

'the next assignment starts the sql statement
varSqlStr = "SELECT AgShuttlePermitLink.PermitNo,
AgShuttlePermitLink.Shuttle, " _
& "AgShuttlePermitLink.Master633,
AgShuttlePermitLink.PrevPermitNo " _
& "FROM AgShuttlePermitLink"

'the case statement below sets a portion of the sql string
'dependent on which option of the three options has been
'selected - one for each case option
Select Case frmShuttleTypeFilter
Case 1
If Me.cboOp633 <> "(No Operator Filter)" Then
varOp633Num = Me.cboOp633
varSqlStr = varSqlStr + " WHERE
(((AgShuttlePermitLink.Status)<9)) AND"
Else
varOp633Num = Me.cboOp633
varSqlStr = varSqlStr + " WHERE
(((AgShuttlePermitLink.Status)<9))"
End If
'just use the value of "varSqlStr" to show all
records
Case 2
If Me.cboOp633 <> "(No Operator Filter)" Then
varSqlStr = varSqlStr + " WHERE ((Left
([AgShuttlePermitLink]![Shuttle],3)=""SCT"")) " _
& "AND
(((AgShuttlePermitLink.Status)<9)) AND"
Else
varSqlStr = varSqlStr + " WHERE ((Left
([AgShuttlePermitLink]![Shuttle],3)=""SCT"")) " _
& "AND
(((AgShuttlePermitLink.Status)<9)) "
End If
Case 3
If Me.cboOp633 <> "(No Operator Filter)" Then
varSqlStr = varSqlStr + " WHERE ((Left
([AgShuttlePermitLink]![Shuttle],3)<>""SCT"")) " _
& "AND
(((AgShuttlePermitLink.Status)<9)) AND"
Else
varSqlStr = varSqlStr + " WHERE ((Left
([AgShuttlePermitLink]![Shuttle],3)<>""SCT"")) " _
& "AND
(((AgShuttlePermitLink.Status)<9))"
End If
End Select

'the next "If" statement adds another section of the
'sql string to the previously developed string,
'based on an option selected from a list box
If Me.cboOp633 <> "(No Operator Filter)" Then
varOp633Num = Me.cboOp633
varSqlStr = varSqlStr + "
((AgShuttlePermitLink.Master633)='" & varOp633Num & "')"
End If

'the assignment below joins all of the pieces together
'to form the sql string
varSqlStr = varSqlStr + " ORDER BY PermitNo"


'the next assignment line just adds the ending semicolon
'to the end of the sql string
varSqlStr = varSqlStr + ";"

'the statements below do the work with the list box
' that will display the results of the sql string
'it assigns the sql string variable to the "RowSource"
'property of the list box, and then requeries it to
'display the records
With Me.lstShuttleLinks
.RowSource = varSqlStr
.Requery
End With

'*****end of code here ******

Hope this helps. If you need more help along this line,
you can email me direct at: (e-mail address removed) and I
will be glad to try to help.

Byron
 
Back
Top