Solved Multi combo box and textbox search

[mounty76]

Hi All,

I am about to go crazy

Attached is a sample db of what I am doing.

In the Expense search form there is some combo boxes and text boxes that can be used for search criteria, they are linked back to ExpenseQQ query.

Currently I have just set the search criteria for the date, person and department, these work fine when I first setup the ExpenseQQ, it works fine until I close access then reopen it, when I do that it will not work.

When I try to add criteria for all the combo boxes and text boxes it throws a wobbler and won't open. Also the invoice number search textbox I cannot get to work when in conjunction with other combo box search criteria.

Any help with this would be greatly appreciated before I throw my laptop away haha

I am missing something here as I've done this before and it has worked.....grrrr

 

[June7]

I would not try to build parameterized query for this. I have never used dynamic parameterized query objects.
I would use VBA to build criteria and apply to form Filter property. Review http://allenbrowne.com/ser-62.html

 

[isladogs]

See also my example apps

Multiple Group & Filter

This article discusses different ways of filtering and grouping data in Access. It includes two example apps showing the use of multiple combo boxes & toggle buttons to filter and group data.

www.isladogs.co.uk

 

[MajP]

I have written code that makes doing this very simple and far more flexible than parameterized queries.
See example here, and my discussion on why these parameterized queries are just limited

Solved - Between Dates On Keyword Search

Hi All, I've used the code in this thread for searching my databases quite a bit and find it excellent. https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/ I was wondering if anyone is familiar with it, could you tell me is it possible to add a...

www.access-programmers.co.uk

See article here

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[MajP]

However, IMO this is a perfect candidate for a more powerful solution.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[mounty76]

Thank you everyone for replies.....so I am not going crazy then, just approaching it the wrong way, that is at least something!! I'll try and get my head around the VBA and revert.

Thanks for the filter version @MajP, although this is a good solution I'd really like it less generic looking for the end user....that said when I get up to my eye balls in code and it doesn't work then this is an excellent plan B haha.

Thanks again everyone, much appreciated

 

[MajP]

OK here is my entire code if using my module

Public Function FilterForm()
 Dim BetweenFilter As String
 Dim DepFilter As String
 Dim CharterFilter As String
 Dim typeFilter As String
 Dim PersonFilter As String
 Dim supplierFilter As String
 Dim invoiceFilter As String
 Dim invTotalFilter As String
 Dim fltr As String
 
 DepFilter = GetFilterFromControl(Me.DepartmentS, , , , "Department")
 CharterFilter = GetFilterFromControl(Me.CharterS, , , , "Charter")
 typeFilter = GetFilterFromControl(Me.TypeS, , , , "Type")
 PersonFilter = GetFilterFromControl(Me.PersonS, , , , "Person")
 supplierFilter = GetFilterFromControl(Me.SupplierS, , , , "Supplier")
 invoiceFilter = GetFilterFromControl(Me.InvNumberSearch, , , , "[Invoice Number]")
 invoiceTotalFilter = GetFilterFromControl(Me.InvTotal, , sdt_Numeric, , "InvoiceTotal")
 BetweenFilter = GetBetweenFilter(ExDateMinSearch, Me.ExDateMaxSearch, "ExpenseDate")
 fltr = CombineFilters(ct_And, DepFilter, CharterFilter, typeFilter, PersonFilter, supplierFilter, invoiceFilter, invoiceTotalFilter, BetweenFilter)
 If fltr <> "" Then
   Debug.Print fltr
   Me.Filter = fltr
   Me.FilterOn = True
 End If
End Function

 

[mounty76]

MajP.....this is awesome, thank you very much!! Very much appreciated....would have taken me days to try and figure this out.....greatly appreciated

 

[MajP]

MajP.....this is awesome, thank you very much!! Very much appreciated....would have taken me days to try and figure this out.....greatly appreciated

Because this is something people do a lot I wrote the background code to support this. It is pretty extensive and does a lot of the heavy lifting. But to use it is simple and extremely flexible.

No matter how many controls it is always the same code.
A variable for each controls filter, a line to get the filter from the control, and then the function to combine the filters.

 

[mounty76]

Because this is something people do a lot I wrote the background code to support this. It is pretty extensive and does a lot of the heavy lifting. But to use it is simple and extremely flexible.

No matter how many controls it is always the same code.
A variable for each controls filter, a line to get the filter from the control, and then the function to combine the filters.

Works great, I have used it on another search form also One thing..... If I want to search LIKE "*" & xxxxx & "*" for say the Invoice Number how do I put this into theFilterForm code? I've tried several times and cannot seem to get it right!

 

[mounty76]

Also one other thing.....how can I then export the form data (filtered) into a report?

 

[Gasman]

Also one other thing.....how can I then export the form data (filtered) into a report?

Use the form filter as the Where in the open report.

 

[mounty76]

Cheers Gasman

 

[MajP]

The function for getting a filter from a textbox is

Public Function GetFilterFromControl(ctrl As Access.Control, Optional TheFilterType As FilterType = flt_Equal, _
        Optional TheSql_Datatype As SQL_DataType = sdt_boundField, Optional TheColumn As Integer = -1, Optional FieldName As String = "UseColumnName", _
        Optional NotCondition As Boolean = False) As String
  Dim controlType As Long
 ' If Trim(ctrl & " ") = "" Then Exit Function
  controlType = ctrl.controlType

  Select Case controlType
    
    Case acListBox
       If ctrl.MultiSelect = 1 Then
         If ctrl.ListIndex = -1 Then Exit Function
         GetFilterFromControl = GetFilterFromMultiSelect(ctrl, TheFilterType, TheSql_Datatype, TheColumn, FieldName, NotCondition)
      Else
         If Trim(ctrl & " ") = "" Then Exit Function
         GetFilterFromControl = GetFilterFromCombo_ListBox(ctrl, TheFilterType, TheColumn, FieldName, TheSql_Datatype, NotCondition)
      End If
     Case acComboBox
          If Trim(ctrl & " ") = "" Then Exit Function
          GetFilterFromControl = GetFilterFromCombo_ListBox(ctrl, TheFilterType, TheColumn, FieldName, TheSql_Datatype, NotCondition)
     Case acTextBox
          If Trim(ctrl & " ") = "" Then Exit Function
          If TheSql_Datatype = sdt_boundField Then TheSql_Datatype = sdt_text
          If FieldName = "useBound" Then
            MsgBox "Textboxes need an associated field name.", vbInformation
            Exit Function
          End If
          GetFilterFromControl = GetFilterFromTextBox(ctrl, TheSql_Datatype, FieldName, TheFilterType, NotCondition)
    Case Else
  End Select
  
End Function

Public Function GetFilterFromTextBox(ctrl As Access.TextBox, TheSql_Datatype As SQL_DataType, FieldName As String, _
           Optional TheFilterType As FilterType = flt_Equal, Optional NotCondition As Boolean = False) As String
           Dim fltr As String
           If Not Trim(ctrl & " ") = "" Then
             fltr = GetSQL_Filter(TheFilterType, CSql(ctrl.Value, TheSql_Datatype))
             If fltr <> "" Then GetFilterFromTextBox = FieldName & " " & fltr
           End If
           
End Function

ctrl As Access.TextBox
TheSql_Datatype As SQL_DataType
FieldName As String,
Optional TheFilterType As FilterType = flt_Equal
Optional NotCondition As Boolean = False

Ctrl is a reference to the textbox

TheSql_Datatype is what type of value (text, numeric, date, boolean). This is needed to delimit the result with (',#, or nothing)
For a combobox I have code that can figure it out without specifying if the combo is based on a query. If it is a textbox it might get it right or not depending on the format. So with a textbox I specify TheSql_DataType. If the bound field of the combo is the same field to filter it can figure this out.

Public Enum SQL_DataType
  sdt_boundField = -1
  sdt_UseSubType = 0
  sdt_text = 1
  sdt_Numeric = 2
  sdt_date = 3
  sdt_Boolean = 4
  sdt_Null = 5
End Enum

The argument TheFilterType has these values. You can do all these types of filter. If you want a Like filter you can choose a Like from beginning 'Text*' or a Like anywhere in string '*Text*'

Public Enum FilterType
flt_Equal = 0
flt_LikeFromBeginning = 1 'Text*
flt_LikeAnywhere = 2 '*test*
flt_LikeFromEnd = 3 '*Text'
flt_GreaterThan = 4
flt_GreaterThanOrEqual = 5
flt_LessThan = 6
flt_LessThanOrEqual = 7
flt_Between = 8
End Enum

invoiceFilter = GetFilterFromControl(Me.InvNumberSearch, flt_LikeAnywhere, sdt_text, , "[Invoice Number]")

 

[mounty76]

Perfect, thank you very much....everydays a school day....especially with Access lol