Private Function CheckFilter(Optional Zoekveld As String, Optional Waarde As String)
Dim sFilter As String, [B][COLOR="#0000FF"]tmpFilter As String[/COLOR][/B], sFilters() As String, sTekst() As String
Dim strSQL As String, sAndOr As String, sKeuze As String
Dim ctl As Control
Dim tmpMatrix As Variant, tmp As Variant, itm As Variant
Dim iFltr As Integer, iLst As Integer
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim rst As DAO.Recordset
'-----------------------------------------------------------------------------------------------------------
'Eerst de grootte van de matrix bepalen....
'Dat doen we door alle filters door te lopen, en de inhoud in een matrix te zetten.
'We lopen door de controls heen op basis van het ControlType.
'-----------------------------------------------------------------------------------------------------------
x = 0: iFltr = 0: iLst = 0
For Each ctl In Controls
With ctl
Select Case .ControlType
Case acTextBox
If LCase(Left(.Name, 9)) = "txtFilter" Then
.SetFocus
iFltr = iFltr + 1
On Error Resume Next
If Not .Text = "" Then
x = x + 1
If x = 1 Then
ReDim sTekst(x)
Else
ReDim Preserve sTekst(x)
End If
'-----------------------------------------------------------------------------------
'De matrix wordt gevuld met de Veldnaam, de Filternaam en het Filterveld.
'Deze worden met een scheidingsteken in één string gezet die later gesplitst wordt.
'-----------------------------------------------------------------------------------
sTekst(x) = .Tag & "|" & .Text & "|" & .Name
End If
End If
Case acListBox
'-------------------------------------------------------------------------------------------
'Een listbox kan meerdere items bevatten die geselecteerd worden.
'Die moeten allemaal apart worden uitgelezen.
'-------------------------------------------------------------------------------------------
If LCase(Left(.Name, 9)) = "lstFilter" Then
sKeuze = ""
.SetFocus
iFltr = iFltr + 1
iLst = iLst + 1
On Error Resume Next
If Me("lstFilter" & iLst).ItemsSelected.Count >= 1 Then
x = x + 1
If x = 1 Then
ReDim sTekst(x)
Else
ReDim Preserve sTekst(x)
End If
For Each itm In Me("lstFilter" & iLst).ItemsSelected
If sKeuze & "" <> "" Then sKeuze = sKeuze & "\"
sKeuze = sKeuze & Me("lstFilter" & iLst).ItemData(itm)
Next itm
'-----------------------------------------------------------------------------------
'Ook hier wordt een samengestelde string gemaakt van de filterwaarden.
'-----------------------------------------------------------------------------------
sTekst(x) = .Tag & "|" & sKeuze & "|" & .Name
End If
End If
Case acComboBox
If LCase(Left(.Name, 9)) = "cboFilter" Then
.SetFocus
iFltr = iFltr + 1
On Error Resume Next
If Not .Value = "" Then
x = x + 1
If x = 1 Then
ReDim sTekst(x)
Else
ReDim Preserve sTekst(x)
End If
sTekst(x) = .Tag & "|" & .Text & "|" & .Name
End If
End If
End Select
End With
Next ctl
'-----------------------------------------------------------------------------------------------------------
'Als de tekstfilters leeg zijn, dan filter leegmaken en stoppen....
'-----------------------------------------------------------------------------------------------------------
If x = 0 Then GoTo LeegFilter
'...... anders doorgaan, en matrix herdefiniëren.
'===========================================================================================================
'-----------------------------------------------------------------------------------------------------------
'Dan de variabelen vullen met gegevens
'We doen dat door een extra matrix te maken op basis van het filter
'-----------------------------------------------------------------------------------------------------------
ReDim sFilters(x, 3)
For i = LBound(sFilters) To UBound(sFilters)
tmpMatrix = Split(sTekst(i), "|")
For x = LBound(tmpMatrix) To UBound(tmpMatrix)
sFilters(i, x + 1) = tmpMatrix(x)
Next x
Next i
i = 0
x = 0
'===========================================================================================================
'-----------------------------------------------------------------------------------------------------------
'Dan op basis van de variabelen het filter opbouwen
'-----------------------------------------------------------------------------------------------------------
Select Case Me.fraOptie.Value
Case 1
sAndOr = " AND "
Case 2
sAndOr = " OR "
End Select
[B][COLOR="#0000FF"] sFilter = "": tmpFilter = ""
[/COLOR][/B] For i = LBound(sFilters) To UBound(sFilters)
If LBound(sFilters) = UBound(sFilters) Then
If InStr(sFilters(i, 2), "\") > 0 Then '=,> 0 maakt er altijd text veld van
tmpMatrix = Split(sFilters(i, 2), "\")
For x = LBound(tmpMatrix) To UBound(tmpMatrix)
'-------------------------------------------------------------------------------------------
'Eerst controleren of er getallen in het spel zijn, of tekst.
'-------------------------------------------------------------------------------------------
If IsNumeric(tmpMatrix(x)) Then
sFilter = sFilter & "[" & sFilters(i, 1) & "] =" & tmpMatrix(x)
Else
sFilter = sFilter & "[" & sFilters(i, 1) & "] Like """ & tmpMatrix(x) & "*"""
End If
If x < UBound(tmpMatrix) Then sFilter = sFilter & " OR "
Next x
Else
sFilter = sFilter & "[" & sFilters(i, 1) & "] Like """ & sFilters(i, 2) & "*"""
End If
Else
'---------------------------------------------------------------------------------------------------
'Vervolgens controleren of de listbox meerdere items bevat, die gesplitst moeten worden.
'Die worden dan allemaal apart worden uitgelezen en in het filter gezet.
'---------------------------------------------------------------------------------------------------
If InStr(sFilters(i, 2), "\") > 0 Then '=,> 0 maakt er altijd text veld van
tmpMatrix = Split(sFilters(i, 2), "\")
[B][COLOR="#0000FF"] tmpFilter = ""[/COLOR][/B]
For x = LBound(tmpMatrix) To UBound(tmpMatrix)
'-------------------------------------------------------------------------------------------
'Uiteraard ook hier weer controleren of er getallen in het spel zijn, of tekst.
'-------------------------------------------------------------------------------------------
If IsNumeric(tmpMatrix(x)) Then
[B][COLOR="#FF0000"]If InStr(sFilters(i, 1), "[") > 0 Then[/COLOR][/B]
[B][COLOR="#0000FF"]tmpFilter = tmpFilter & sFilters(i, 1) & "=" & tmpMatrix(x)
ElseIf InStr(sFilters(i, 1), "[") = 0 And InStr(sFilters(i, 1), " ") > 0 Then
tmpFilter = tmpFilter & "[" & sFilters(i, 1) & "] =" & tmpMatrix(x)
End If
Else
tmpFilter = tmpFilter & "[" & sFilters(i, 1) & "] """ & tmpMatrix(x) & "*"""
End If
If x < UBound(tmpMatrix) Then tmpFilter = tmpFilter & " OR "
Next x
Else
If IsNumeric(sFilters(i, 2)) Then
tmpFilter = "[" & sFilters(i, 1) & "] = " & sFilters(i, 2)
Else
tmpFilter = "[" & sFilters(i, 1) & "] Like """ & sFilters(i, 2) & "*"""
End If
End If
sFilter = sFilter & "(" & tmpFilter & ")"
If i < UBound(sFilters) Then
sFilter = sFilter & sAndOr
End If
End If[/COLOR][/B]
Next i
'===========================================================================================================
'-----------------------------------------------------------------------------------------------------------
'Filter vervolgens op formulier toepassen.
'-----------------------------------------------------------------------------------------------------------
Set qdef = CurrentDb.QueryDefs("Query1")
strSQL = "SELECT * FROM blad1 WHERE " & sFilter
qdef.SQL = strSQL
Me.Filter = sFilter
Me.FilterOn = True
MyDB.QueryDefs.Delete "query1"
strSQL = "SELECT * FROM blad1" & Me.Filter & ""
Set qdef = MyDB.CreateQueryDef("query1", strSQL)
If Not Zoekveld = "" Then
Me(Zoekveld).SetFocus
End If
'===========================================================================================================
'-----------------------------------------------------------------------------------------------------------
'En als laatste functie om de verticale scrollbar aan- of uit te zetten.
'-----------------------------------------------------------------------------------------------------------
CheckScrollbar
'===========================================================================================================
Exit Function
LeegFilter:
Me.Filter = ""
Me.FilterOn = False
On Error Resume Next
Me(Zoekveld).SetFocus
End Function