3 Combo Box Filtering of 1 table.

  • Thread starter Thread starter gtslabs
  • Start date Start date
G

gtslabs

I have a table called Log that has many fields.
I set up a form and put the info from Log in the subform.
At the top of my main form I have 3 unbound combo boxes that I want to
filter down the data in the subform.
I was able to set the "link master fields" with Combo_Project_Filter
and the child with the field Projects from Log. And I was able to
write a SQL to show only the data remaining in the second combo box
Combo_Date_Filter. But when I select that date nothing happens. SQL
from Combo_Date_Filter is
SELECT log.Pour_Date
FROM log
WHERE (((log.Project)=[Forms]![frmMain]![Combo_Project_Filter]))
ORDER BY log.Pour_Date;

So do I need to make a union between these 2 checkboxes?
I have tried a few statements with no luck?

And then what if I filter first on the Date_Filter? How would the
Project Filter and 3rd filter Combo_Material_Filter be filtered?

I could not find any examples of how this is done. My VBA skills are
somewhat limited.

Thanks
S.
 
Hi,

It's nort necessary to use subforms. Put the comboboxes in the form header
Then you could put a button on your form and build the filterstring with a
function like:

Public Function GetFilterString() As String
On Error GoTo Err_GetFilterString

Dim strFilter As String

If Len(Me.cbo1) > 0 Then
strFilter = strFilter & " and field1= " & Me.cbo1
End If
If Len(Me.cbo2) > 0 Then
strFilter = strFilter & " and field2= " & Me.cbo2
End If
If Len(Me.cbo3) > 0 Then
strFilter = strFilter & " and field3= " & Me.cbo3
End If


If Len(strFilter) > 0 Then
strFilter = " 1 = 1 " & strFilter
End If
GetFilterString = strFilter

Exit_GetFilterString:
Exit Function

Err_GetFilterString:
GetFilterString = ""
Resume Exit_GetFilterString
End Function


After building the filterstring you can filter the data form by setting its
filter property:

Use the function to get the filterstring

strMyFilter = GetFilterstring()
Me.filter = strMyFilter
Me.filteron = true
 
Back
Top