Thanks for the link, I think it could be a part of the solution.
What I've done so far:
I was able to filter when I was just using one textbox. I did it through folowing code:
Textbox On Change:
'code that happens when users types in the textbox
Dim strFullist As String
Dim strFilteredlist As String
If blnSpace = False Then
Me.Refresh 'refresh to make sure the textbox changes are actually available to use
'specify the default/full rowsource for the control
strFullist = "SELECT tbl_certificaatnr.cer_Jaartal AS Jaar, tbl_certificaatnr.cer_Certificaatnummer AS Nummer, tbl_certificaatnr.cer_Certificaattype AS Type, tbl_certificaatnr.cer_Taalcertificaat, tbl_klanten.kla_ID AS [ID Klant], tbl_klanten.kla_Maatschappij AS Maatschappij, tbl_klanten.kla_afdeling AS Afdeling, tbl_TypeToestel.tpt_NaamToestelNL AS [Naam toestel], tbl_Merk.mrk_naam_merk_toestel AS Merk, tbl_certificaatnr.cer_Aanvrager AS Aanvrager, tbl_Prestatie.pre_fait AS Uitgevoerd, tbl_certificaatnr.cer_Kalibratiedatum AS Kalibratiedatum, tbl_certificaatnr.cer_ID AS [ID Cert] "
strFullist = strFullist & "FROM tbl_Offerte RIGHT JOIN (tbl_klanten INNER JOIN (((tbl_Prestatie RIGHT JOIN tbl_certificaatnr ON tbl_Prestatie.pre_ID = tbl_certificaatnr.cer_IDPrestation) LEFT JOIN (tbl_Toes LEFT JOIN tbl_Merk ON tbl_Toes.toe_IDMerk = tbl_Merk.mrk_id) ON tbl_Prestatie.pre_Toestel = tbl_Toes.toe_ID) LEFT JOIN tbl_TypeToestel ON tbl_Toes.toe_IDType = tbl_TypeToestel.tpt_ID) ON tbl_klanten.kla_ID = tbl_certificaatnr.cer_IDKlant) ON tbl_Offerte.off_ID = tbl_Prestatie.pre_IDOfferte "
strFullist = strFullist & "ORDER BY tbl_certificaatnr.cer_Jaartal DESC , tbl_certificaatnr.cer_Certificaatnummer DESC; "
strFilteredlist = "SELECT tbl_certificaatnr.cer_Jaartal AS Jaar, tbl_certificaatnr.cer_Certificaatnummer AS Nummer, tbl_certificaatnr.cer_Certificaattype AS Type, tbl_certificaatnr.cer_Taalcertificaat, tbl_klanten.kla_ID AS [ID Klant], tbl_klanten.kla_Maatschappij AS Maatschappij, tbl_klanten.kla_afdeling AS Afdeling, tbl_TypeToestel.tpt_NaamToestelNL AS [Naam toestel], tbl_Merk.mrk_naam_merk_toestel AS Merk, tbl_certificaatnr.cer_Aanvrager AS Aanvrager, tbl_Prestatie.pre_fait AS Uitgevoerd, tbl_certificaatnr.cer_Kalibratiedatum AS Kalibratiedatum, tbl_certificaatnr.cer_ID AS [ID Cert] "
strFilteredlist = strFilteredlist & "FROM tbl_Offerte RIGHT JOIN (tbl_klanten INNER JOIN (((tbl_Prestatie RIGHT JOIN tbl_certificaatnr ON tbl_Prestatie.pre_ID = tbl_certificaatnr.cer_IDPrestation) LEFT JOIN (tbl_Toes LEFT JOIN tbl_Merk ON tbl_Toes.toe_IDMerk = tbl_Merk.mrk_id) ON tbl_Prestatie.pre_Toestel = tbl_Toes.toe_ID) LEFT JOIN tbl_TypeToestel ON tbl_Toes.toe_IDType = tbl_TypeToestel.tpt_ID) ON tbl_klanten.kla_ID = tbl_certificaatnr.cer_IDKlant) ON tbl_Offerte.off_ID = tbl_Prestatie.pre_IDOfferte "
strFilteredlist = strFilteredlist & "WHERE [kla_Maatschappij] like ""*" & Me.MaatschappijTextbox & "*"" "
strFilteredlist = strFilteredlist & "ORDER BY tbl_certificaatnr.cer_Jaartal DESC , tbl_certificaatnr.cer_Certificaatnummer DESC; "
fLiveSearchMaatschappij Me.MaatschappijTextbox, Me.Liste, strFullist, strFilteredlist
End If
Textbox On Got Focus:
On Error Resume Next
If Me.MaatschappijTextbox.Value = "" Then
Me.MaatschappijTextbox.Value = ""
End If
Textbox On Lost Focus:
Private Sub MaatschappijTextbox_LostFocus()
On Error Resume Next
If Me.MaatschappijTextbox.Value = "" Then
Me.MaatschappijTextbox.Value = ""
End If
End Sub
and a public function:
Function fLiveSearchMaatschappij(MaatschappijTextbox As TextBox, Liste As Control, strFullist As String, strFilteredlist As String)
'==================================================================================
' THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
' ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
' FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
' SQL (ROWSOURCE) SHOULD BE.
'
' ctlSearchBox THE TEXTBOX THE USER TYPES IN TO SEARCH
'
' ctlFilter THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
'
' strFullSQL THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
' RESULTS ARE RETURNED
'
' strFilteredSQL THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
' YOU WOULD WANT TO USE '...like ""*" & me.txtsearch.value & "*"""
' TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
'
' ctlCountLabel (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
' COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
'=====================================================================================
'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
Const blnEmptyOnNoMatch = False 'Set to true if you want nothing to appear if nothing matches their search
On Error GoTo err_handle
'restore the cursor to where they left off
MaatschappijTextbox.SetFocus
MaatschappijTextbox.SelStart = Len(MaatschappijTextbox.Value) + 1
If MaatschappijTextbox.Value <> "" Then
'Only fire if they've input more than two characters (otherwise it's wasteful)
If Len(MaatschappijTextbox.Value) > iSensitivity Then
Liste.RowSource = strFilteredlist
If Liste.ListCount > 0 Then
MaatschappijTextbox.SetFocus
MaatschappijTextbox.SelStart = Len(MaatschappijTextbox.Value) + 1
Else
If blnEmptyOnNoMatch = True Then
Liste.RowSource = ""
Else
Liste.RowSource = strFullist
End If
End If
Else
Liste.RowSource = strFullist
End If
Else
Liste.RowSource = strFullist
End If
Exit Function
err_handle:
Select Case Err.Number
Case 91 'no txtcount
'exit
Case 94 'null string
'exit
Case Else
MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl
End Select
End Function
I found this code on this forum I think and it works good.
Now I want to extend this code. I want to enter a Start date in one textbox and an End Date in another textbox, this date refers to the calibration date of the equipment.
I also have a Combo box where I can select te type of certificate and another one where I can select the language of the certificate. However, untill now I didn't used the 2 comboboxes because they don't work together with the code above.
I've tried to extend the basic code above like this:
Private Sub MaatschappijTextbox_Change()
'code that happens when users types in the textbox
Dim strFullist As String
Dim strFilteredlist As String
If blnSpace = False Then
Me.Refresh 'refresh to make sure the textbox changes are actually available to use
'specify the default/full rowsource for the control
strFullist = "SELECT tbl_certificaatnr.cer_Jaartal AS Jaar, tbl_certificaatnr.cer_Certificaatnummer AS Nummer, tbl_certificaatnr.cer_Certificaattype AS Type, tbl_certificaatnr.cer_Taalcertificaat, tbl_klanten.kla_ID AS [ID Klant], tbl_klanten.kla_Maatschappij AS Maatschappij, tbl_klanten.kla_afdeling AS Afdeling, tbl_TypeToestel.tpt_NaamToestelNL AS [Naam toestel], tbl_Merk.mrk_naam_merk_toestel AS Merk, tbl_certificaatnr.cer_Aanvrager AS Aanvrager, tbl_Prestatie.pre_fait AS Uitgevoerd, tbl_certificaatnr.cer_Kalibratiedatum AS Kalibratiedatum, tbl_certificaatnr.cer_ID AS [ID Cert] "
strFullist = strFullist & "FROM tbl_Offerte RIGHT JOIN (tbl_klanten INNER JOIN (((tbl_Prestatie RIGHT JOIN tbl_certificaatnr ON tbl_Prestatie.pre_ID = tbl_certificaatnr.cer_IDPrestation) LEFT JOIN (tbl_Toes LEFT JOIN tbl_Merk ON tbl_Toes.toe_IDMerk = tbl_Merk.mrk_id) ON tbl_Prestatie.pre_Toestel = tbl_Toes.toe_ID) LEFT JOIN tbl_TypeToestel ON tbl_Toes.toe_IDType = tbl_TypeToestel.tpt_ID) ON tbl_klanten.kla_ID = tbl_certificaatnr.cer_IDKlant) ON tbl_Offerte.off_ID = tbl_Prestatie.pre_IDOfferte "
strFullist = strFullist & "ORDER BY tbl_certificaatnr.cer_Jaartal DESC , tbl_certificaatnr.cer_Certificaatnummer DESC; "
strFilteredlist = "SELECT tbl_certificaatnr.cer_Jaartal AS Jaar, tbl_certificaatnr.cer_Certificaatnummer AS Nummer, tbl_certificaatnr.cer_Certificaattype AS Type, tbl_certificaatnr.cer_Taalcertificaat, tbl_klanten.kla_ID AS [ID Klant], tbl_klanten.kla_Maatschappij AS Maatschappij, tbl_klanten.kla_afdeling AS Afdeling, tbl_TypeToestel.tpt_NaamToestelNL AS [Naam toestel], tbl_Merk.mrk_naam_merk_toestel AS Merk, tbl_certificaatnr.cer_Aanvrager AS Aanvrager, tbl_Prestatie.pre_fait AS Uitgevoerd, tbl_certificaatnr.cer_Kalibratiedatum AS Kalibratiedatum, tbl_certificaatnr.cer_ID AS [ID Cert] "
strFilteredlist = strFilteredlist & "FROM tbl_Offerte RIGHT JOIN (tbl_klanten INNER JOIN (((tbl_Prestatie RIGHT JOIN tbl_certificaatnr ON tbl_Prestatie.pre_ID = tbl_certificaatnr.cer_IDPrestation) LEFT JOIN (tbl_Toes LEFT JOIN tbl_Merk ON tbl_Toes.toe_IDMerk = tbl_Merk.mrk_id) ON tbl_Prestatie.pre_Toestel = tbl_Toes.toe_ID) LEFT JOIN tbl_TypeToestel ON tbl_Toes.toe_IDType = tbl_TypeToestel.tpt_ID) ON tbl_klanten.kla_ID = tbl_certificaatnr.cer_IDKlant) ON tbl_Offerte.off_ID = tbl_Prestatie.pre_IDOfferte "
strFilteredlist = strFilteredlist & "WHERE [kla_Maatschappij] like ""*" & Me.MaatschappijTextbox & "*"" AND (((tbl_certificaatnr.cer_Kalibratiedatum) > Me.DateBegin And < Me.DateEinde Or (tbl_certificaatnr.cer_Kalibratiedatum) Is Null)) and [cer_Certificaattype] like Me.ComboType & AND [cer_Taalcertificaat] like Me.ComboTaal "
strFilteredlist = strFilteredlist & "ORDER BY tbl_certificaatnr.cer_Jaartal DESC , tbl_certificaatnr.cer_Certificaatnummer DESC; "
fLiveSearchMaatschappij Me.MaatschappijTextbox, Me.Liste, strFullist, strFilteredlist
End If
End Sub
Private Sub MaatschappijTextbox_GotFocus()
On Error Resume Next
If Me.MaatschappijTextbox.Value = "" Then
Me.MaatschappijTextbox.Value = ""
End If
End Sub
I've created the same public folders for all the text boxes and combo boxes and edited the function names for the different textboxes and combo's.
The idea is that my collegue can enter a client, the date where between the calibration has been done, eventually the type of certificate for clients with al lot of material and a language for multilanguage clients. I wouls like to use the textboxes and combo's in combination as well as each one individual.
I know it's maybe difficult to read, the tables are written in Dutch.
Thanks for the help
