Solved Unbound Combo box need multiple value

[smtazulislam]

Hi, I have a searcher form. It is worked.
But now like to put multiple value to search.

CODE :

Private Sub cmdFilter_Click()

    Dim strWhere As String
    Dim lngLen As Long
    
    If Not IsNull(Me.fltCompanyName) Then
        strWhere = strWhere & "([CompanyName] = """ & Me.fltCompanyName & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.fltBranch) Then
        strWhere = strWhere & "([BranchEn] Like ""*" & Me.fltBranch & "*"") AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.fltCategory) Then
        strWhere = strWhere & "([Category] Like ""*" & Me.fltCategory & "*"") AND "
    End If


    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        Debug.Print strWhere

        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

I try to follow this link

How can do that ?
Any help will be appreciate.

 

[Ranman256]

Combo boxes should not be used for multi select.
use a list box.

 

[June7]

That link specifically indicates it applies to listbox.

A combobox MUST be bound to multi-value field for multi-select feature.

 

[smtazulislam]

Thank you very much both advice
There is no more space to add list box. And user like to dropdown list.
see attached.

In this form appear any others idea to filter "CompanyName", "BranchName", "Category" by multiple value

 

[moke123]

There is no more space to add list box.

You could always use pop up forms with multiselect listboxes and use an "In Clause" to filter.

 

[June7]

Don't dedicate so much space to records display? Rearrange controls more vertically and position listbox next to them? Any other solution would probably require even more space or a popup form.

 

[Pat Hartman]

Several examples in this sample db show how to filter with a multi-select listbox. The sample works for an unbound multi-select combo also.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

 

[June7]

Pat, how do you build an UNBOUND multi-select combobox?

 

[CJ_London]

The way I do it is to use a subform or popup form positioned and sized as required that looks like a multi value combo and uses an ado disconnected recordset populated with a checkbox and relevant values. Mine also includes a select/deselect all checkbox.

On exiting the form, code similar to that used to obtain the selected values from a multi select listbox is used to to display the selected values.

 

[Pat Hartman]

Pat, how do you build an UNBOUND multi-select combobox?

Easy, you drag the wrong control type to the form and look at its properties I guess I'll clean my glasses.

If vote for CJ's solution

 

[smtazulislam]

The sample works for an unbound multi-select combo also.

I download it, But not found there multi select in Combo box as I required . there is list box in multi.

 

[smtazulislam]

You could always use pop up forms with multiselect listboxes and use an "In Clause" to filter.

If I using the popup form then mainform Unbound Combo box to replace TEXTBOX. Its also good ways, I will try it.

 

[moke123]

here's a quick example of one way to do it with a pop up form

 

[smtazulislam]

here's a quick example of one way to do it with a pop up form

Thank you very much. Nice work. But Im not sure that it is possible with some criteria to print out after filtering data.
I will try and give you a feedback. appreciate your work.

 

[MajP]

Multi select combobox with filltering (not using an MVF control, but a popup subform). Will return the filter as critieria to print or build a SQL string.

Needs some work but relatively easy to deploy if you can figure out all the parameters to pass.
The class allows you to filter on the visible value or on a hidden PK field. In the demo State and Country use a value, and Name uses the PK.

NOTE: Updated version 3 uses ModFormInfo from @isladogs to position the popups correctly. I think the link is here

ResizeForm Me - An Automatic Form Resizing Tutorial

See attached for a detailed article in PDF format about automatic form resizing based on the free open source code by Jamie Czernik. Having used this code for over 15 years, I have written the article as it appears many people do not know how to use the feature effectively with their own...

www.access-programmers.co.uk

 

[MajP]

Updated Version. Updates include
1. Handles both numeric and text primary keys or no PK.
2. Handles text, numeric, boolean, date value field
3. demos number 2

 

[smtazulislam]

Updated Version. Updates include
1. Handles both numeric and text primary keys or no PK.
2. Handles text, numeric, boolean, date value field
3. demos number 2

Thank you very much. Its the best for me.
Now facing a problem the two combo box filter others two not working. I don't know where I do mistake.
See the attached

Option Compare Database
Option Explicit

Private WithEvents MSCCompanyName As Form_frmMultiSelectDialogueBox  'Company Name
Private WithEvents MSCBranch As Form_frmMultiSelectDialogueBox       'Branch Name
Private WithEvents MSCCategory As Form_frmMultiSelectDialogueBox     'Category Name
Private WithEvents MSCCountry As Form_frmMultiSelectDialogueBox      'Country Name

Private CompanyFilter As String     'Filter By Company Name
Private BranchFilter As String      'Filter By Branch Name
Private CategoryFilter As String    'Filter By Category Name
Private CountryFilter As String     'Filter By Country Name


Public Sub ApplyFilter()
  Dim formfilter As String
  Dim andOr As CombineFilterType
  If Me.frmAndOr = 1 Then
    andOr = ct_And
  Else
    andOr = ct_Or
  End If
  formfilter = CombineFilters(andOr, CompanyFilter, BranchFilter)
  Me.Filter = formfilter
  Me.FilterOn = True
  Me.txtFilter = formfilter
End Sub

Private Sub ClearFilter()
  Me.cboCompanyName.Tag = ""
  Me.cboCompanyName.Value = Null
  Me.cboBranch.Tag = ""
  Me.cboBranch.Value = Null
  Me.cboCategory.Tag = ""
  Me.cboCategory.Value = Null
  Me.cboCountry.Tag = ""
  Me.cboCountry.Value = Null
 
  CompanyFilter = ""
  BranchFilter = ""
  CategoryFilter = ""
  CountryFilter = ""
 
  ApplyFilter
End Sub

Private Sub CmdClear_Click()
  ClearFilter
End Sub


Private Sub frmAndOr_AfterUpdate()
 ApplyFilter
End Sub


'START Filter By Country Name*****************************************************
Private Sub cboCountry_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  DoCmd.OpenForm "frmMultiSelectDialogueBox"
  Set MSCCountry = Forms("frmMultiSelectDialogueBox")
  MSCCountry.Initialize Me.cboCountry, "QryCountryComboLookup", "Country", "CountryID", , , , , 6
End Sub

Private Sub MSCCountry_FilterApplied(FilterString As String)
  CountryFilter = FilterString
  MsgBox CountryFilter
  ApplyFilter
End Sub
'END Filter By Country Name************************************************************



'START Filter By Category Name*****************************************************
Private Sub cboCategory_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  DoCmd.OpenForm "frmMultiSelectDialogueBox"
  Set MSCCategory = Forms("frmMultiSelectDialogueBox")
  MSCCategory.Initialize Me.cboCategory, "qryWorkCategoryList", "WorkCategory", "WorkCategoryID", , , , , 6
End Sub

Private Sub MSCCategory_FilterApplied(FilterString As String)
  CategoryFilter = FilterString
  MsgBox CategoryFilter
  ApplyFilter
End Sub
'END Filter By Category Name************************************************************


'START Filter By Branch Name*****************************************************
Private Sub cboBranch_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  DoCmd.OpenForm "frmMultiSelectDialogueBox"
  Set MSCBranch = Forms("frmMultiSelectDialogueBox")
  MSCBranch.Initialize Me.cboBranch, "qryBranchesList", "BranchEn", "BranchID", , , , , 6
End Sub

Private Sub MSCBranch_FilterApplied(FilterString As String)
  BranchFilter = FilterString
  MsgBox BranchFilter
  ApplyFilter
End Sub
'END Filter By Branch Name************************************************************


'START Filter By Company Name*****************************************************
Private Sub cboCompanyName_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  DoCmd.OpenForm "frmMultiSelectDialogueBox"
  Set MSCCompanyName = Forms("frmMultiSelectDialogueBox")
  MSCCompanyName.Initialize Me.cboCompanyName, "QryCompaniesName", "CompanyName", "CompID", , , , , 8, "Select Names..."


End Sub

Private Sub MSCCompanyName_FilterApplied(FilterString As String)
  CompanyFilter = FilterString
  MsgBox CompanyFilter
  ApplyFilter
End Sub
'END Filter By Company Name************************************************************

Also, can you explain attached second screenshot which you have a TAG " 2, 3, 9, 10, 14" What is propose ? Why you added multi numbers ?


In my FORM i dont have added any TAG. Thank you.

 

[MajP]

Also, can you explain attached second screenshot which you have a TAG " 2, 3, 9, 10, 14" What is propose ?

The way the code works is that after you select Values it stores the location (the recordset.absoluteposition). So if you select the 1st, second, and fifth choice the pop up form returns 1, 3, 5 and stores it in the tag. Next time you pop open the form (multi select combobox) it populates the selections in the form based on the values in the tag property previously selected.

GetSelectionsFromTag reads the tag and converts it into a collection. Then this code loops those items which are positions and selects them.

 Set TagSelections = GetSelectionsFromTag
 For I = 1 To TagSelections.Count
  ' MsgBox i
   If InMemoryRS.RecordCount > I Then
     InMemoryRS.AbsolutePosition = TagSelections(I)
     InMemoryRS.Fields("Selected") = True
   Else
     MsgBox "Error Loading the Tag values into the InMemory recordset.  Likely this is caused by the values in the Tag not in the Recordset. Check the Tag Property of the Combo", vbCritical, "Error in Loading"
     Exit Sub
   End If
 Next I

In design view you can remove all previous tags, they will get cleared out by code regardless.

 

[MajP]

You forgot to combine your filters.

  formfilter = CombineFilters(andOr, CompanyFilter, BranchFilter)

you should have

  formfilter = CombineFilters(andOr, CompanyFilter, BranchFilter,CategoryFilter,CountryFilter)

 

[MajP]

Also not sure what version you are using, but Version 6 made some changes to the parameters. It is a little clearer and easy to use, but the parameters are in a little different order. So if you choose that one you have to change your Initialize call.

In this version in most cases you only have to provide the
Domain (sql recordsource)
Name of the Field to display (ValueField)
Name of the Primary Key field if it exists( PK_Field)
And which field to use to build the filter. Either the Pk field or the Value field.