none
Filtering in query using multiple combo boxen? "No results populated? RRS feed

  • Question

  • I have created a query using combo boxed.

    So when I select in the first Combo box a value I expect to see in the query all results, because for the other two combo boxes I didn't make a selection? But even when I select a value that exist in Table MAP, using a values in all three combo boxes exactly the same results?  Can anyone tell me what is missing in commands below? The values in combo boxes are text strings

    The command behind the button in Form 1

    Private Sub Command28_Click()

       Dim strFilter As String

    If Len(Me.cboStatus & "") > 0 Then

       strFilter = strFilter & " AND Status =" & Chr(34) & Me.cboStatus & Chr(34)

    End If

    If Len(Me.cboVendor & "") > 0 Then

       strFilter = strFilter & " AND Status =" & Chr(34) & Me.cboVendor & Chr(34)

    End If

    If Len(Me.cboStatus & "") > 0 Then

       strFilter = strFilter & " AND Class =" & Chr(34) & Me.cboClass & Chr(34)

    End If

    strFilter = Mid(strFilter, 5)

    If Len(strFilter) > 0 Then

        Me.Filter = strFilter

        Me.FilterOn = True

    Else

        Me.Filter = ""

        Me.FilterOn = False

    End If

    DoCmd.OpenQuery "Query1", acViewNormal, acEdit

    End Sub

     


    Anri

    Sunday, May 14, 2017 12:43 PM

Answers

  • Hi Ken,

    Excellent!!!!  

    Yes, you are right I missed two in the formula.  

    The names of the Combo boxes used, need to  have the exact same names as in the Formula, and needs to be the same as the column headers  in table "Map", otherwise problems will come.

     A strange thing I noticed in the list button, it was the following: Table 20 columns and you select for example the columns "Status", "Vendor","Class", they are listed some where in the middele of the table. So for some reason you get as well other unrequested columns added that you didn't select first?  I think it's may expecting that the selected columns needs to be at the front of the table?

      


    Anri

    • Marked as answer by Anri2018 Tuesday, May 16, 2017 7:25 PM
    Tuesday, May 16, 2017 7:25 PM
  • Excellent Support Best Regards,  Anri  Yes...yes



    Anri

    • Marked as answer by Anri2018 Tuesday, May 16, 2017 7:26 PM
    Tuesday, May 16, 2017 7:26 PM

All replies

  • Private Sub Command28_Click()

       Dim strFilter As String

    If Len(Me.cboStatus & "") > 0 Then

       strFilter = strFilter & " AND Status =" & Chr(34) & Me.cboStatus & Chr(34)

    End If

    If Len(Me.cboVendor & "") > 0 Then

       strFilter = strFilter & " AND Status =" & Chr(34) & Me.cboVendor & Chr(34)

    End If

    If Len(Me.cboStatus & "") > 0 Then

       strFilter = strFilter & " AND Class =" & Chr(34) & Me.cboClass & Chr(34)

    End If

    Hi Anri,

    I see a few mistakes (I think), I have Bolded them in the above example.

    To check if a value is chosen, personally I prefer the syntax:  If (Me.cboStatus > "") Then ...  Both the Null value and the Zerolength string are skipped.

    Imb.

    Sunday, May 14, 2017 1:27 PM
  • You seem to be attempting to both restrict the query by parameters, and at the same time, filter the form.  You should do one or the other, but not both.  To make each parameter optional the query would be:

    SELECT *
    FROM Map
    WHERE (cboStatus = Forms!Form1!cboStatus
        OR Forms!Form1!cboStatus IS NULL)
    AND (cboVendor = Forms!Form1!cboVendor
        OR Forms!Form1!cboVendor IS NULL)
    AND (cboClass = Forms!Form1!cboClass
        OR Forms!Form1!cboClass IS NULL);

    The only code you then need in the button's event procedure is:

        Me.Requery

    Or you can omit the button completely and progressively restrict the form's recordset by putting this line of code in each combo box's AfterUpdate event procedure.

    Note BTW that you do not need to include the three referenced table's in the form's RecordSource query as you are only returning columns from the referencing table, Map.

    Note also that, in the above query, the parameters are not declared as a particular data type as would normally be done.  If a parameter is declared as a non-variant data type it cannot evaluate to Null, with the exception of a parameter declared as DATETIME, which is unusual in this respect.

    PS:  With a query like the above always write the WHERE clause in SQL view and save the query in SQL view.  If you switch to design view before saving the query Access will move things around.  At best the logic will be obscured, at worst the query might become too complex to open.
    • Edited by Ken Sheridan Sunday, May 14, 2017 2:48 PM Postscript added.
    Sunday, May 14, 2017 2:43 PM
  • I have tried you suggestion:

    Private Sub Command28_Click()

      Select * From MAp WHERE (cboStatus = Forms!Form1!cboStatus Or Forms!Form1!cboStatus Is Null) And (cboVendor = Forms!Form1!cboVendor Or Forms!Form1!cboVendor Is Null) And (cboClass = Forms!Form1!cboClass Or Forms!Form1!cboClass Is Null);

     DoCmd.OpenQuery "Query1", acViewNormal, acEdit

     End Sub

    ACCESS is now complaining about the *???  Should I declare  the cboXXXXX in a Dim strFilter As String  expresssion  to get this working? 

     

    Anri

    Sunday, May 14, 2017 5:23 PM
  • This is SQL not VBA code.  First copy the SQL statement below to the clipboard:

    SELECT *
    FROM Map
    WHERE (cboStatus = Forms!Form1!cboStatus
        OR Forms!Form1!cboStatus IS NULL)
    AND (cboVendor = Forms!Form1!cboVendor
        OR Forms!Form1!cboVendor IS NULL)
    AND (cboClass = Forms!Form1!cboClass
        OR Forms!Form1!cboClass IS NULL);

    Then:

    1.  Open the form in design view.
    2.  Select the form's RecordSource property in its properties sheet.
    3.  Right click on the property and select Zoom from the shortcut menu.
    4.  In the dialogue which appears paste in the above SQL statement in place of whatever is in the dialogue at present.  Then click OK.
    5.  In the Click event procedure of the button you need just one line only:

         Me.Requery

    This requeries the form and reloads its recordset on the basis of the values entered in the combo boxes.

    6.  Save the form and open it in form view.


    Ken Sheridan, Stafford, England

    Sunday, May 14, 2017 6:12 PM
  • I should perhaps make clear, in case you do not realise this already, that the form should include controls bound to the relevant columns returned by the query, and should be in continuous forms view.  The three unbound combo boxes should be in the form header.  The image below is an example of such a form from one of my demo files:


    Ken Sheridan, Stafford, England

    Sunday, May 14, 2017 8:20 PM
  • Ihave followed the advice  : SELECT *
    FROM Map
    WHERE (cboStatus = Forms!Form1!cboStatus
        OR Forms!Form1!cboStatus IS NULL)
    AND (cboVendor = Forms!Form1!cboVendor
        OR Forms!Form1!cboVendor IS NULL)
    AND (cboClass = Forms!Form1!cboClass
        OR Forms!Form1!cboClass IS NULL);

    The selection of only one field will not generate a list with all the values of the other two columns.

    The selection of all three fields will only populate the list??  The same when using the button and open the map

     



    Anri

    Monday, May 15, 2017 11:43 AM
  • I followed the same with on click. So for me it's still not clear how i can run the query with not selecting for all three fields a value, only for on or two.


    Anri

    Monday, May 15, 2017 11:49 AM
  • You appear to be trying to fill a list box in an unbound form rather than using a bound form.  In this case the SQL statement I gave you should be the RowSource property of the list box, not the form's RecordSource property.  

    The code in the command button's Click event procedure should requery the list box not the form:

        Me.[NameOfYourListBox].Requery

    The code goes in the *event procedure*, not in the properties sheet, and should go in the button's Click event procedure, not the form's as you have attempted.  If you are unfamiliar with entering code into a control's event procedures, this is how it's done in form design view:

    1.  Select the command button and open its properties sheet if it's not already open.

    2.  Select the On Click event property and select the 'build' button (the one on the right with 3 dots).

    3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

    4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as a new line between these.


    Ken Sheridan, Stafford, England

    Monday, May 15, 2017 12:43 PM
  • It worked very well with three combo boxes.  

    When I try with more using ZOOM to enter the text below.

    SELECT *

    FROM Map

    WHERE (Combo0 = Forms!Questionair! Combo0

        OR Forms!Questionair! Combo0 IS NULL)

    AND (Combo4 = Forms!Questionair! Combo4

        OR Forms!Questionair! Combo4 IS NULL)

    AND (Combo72= Forms!Questionair! Combo72

        OR Forms!Questionair! Combo72 IS NULL)

    AND (Combo22= Forms!Questionair! Combo22

        OR Forms!Questionair! Combo22 IS NULL)

    AND (Combo28= Forms!Questionair! Combo28

        OR Forms!Questionair! Combo28 IS NULL)

    AND (Combo12= Forms!Questionair! Combo12

        OR Forms!Questionair! Combo12 IS NULL)

    AND (Combo81= Forms!Questionair! Combo81

        OR Forms!Questionair! Combo81 IS NULL)

    AND (Combo91= Forms!Questionair! Combo91

        OR Forms!Questionair! Combo91 IS NULL)

    AND (Combo95= Forms!Questionair! Combo95

        OR Forms!Questionair! Combo95 IS NULL)

    AND (Combo97= Forms!Questionair! Combo97

        OR Forms!Questionair! Combo97 IS NULL);

    I get the following warning????

    In the zoom window it looks like see below.  Could it be that the string of commands is to long?


    Anri

    Monday, May 15, 2017 2:42 PM
  • You have a space between the form name and the control name in each parameter, e.g.

        Forms!Questionair! Combo0

    This is not legitimate, so you will need to delete all the spaces.

    Ken Sheridan, Stafford, England

    Monday, May 15, 2017 4:20 PM
  • I have taken out the spaces but No difference see below.

    I get still the same warning

     

    Anri

    Monday, May 15, 2017 5:00 PM
  • You did not look hard enough, there is still a space.  If you look at the lines in the dialogue you'll see that there is a space at the end of the sixth line before it wraps to the next line:

    …….Forms!Questionair!<space>
    Combo28……


    Ken Sheridan, Stafford, England

    Monday, May 15, 2017 5:19 PM
  • Hi Ken,

    Excellent!!!!  

    Yes, you are right I missed two in the formula.  

    The names of the Combo boxes used, need to  have the exact same names as in the Formula, and needs to be the same as the column headers  in table "Map", otherwise problems will come.

     A strange thing I noticed in the list button, it was the following: Table 20 columns and you select for example the columns "Status", "Vendor","Class", they are listed some where in the middele of the table. So for some reason you get as well other unrequested columns added that you didn't select first?  I think it's may expecting that the selected columns needs to be at the front of the table?

      


    Anri

    • Marked as answer by Anri2018 Tuesday, May 16, 2017 7:25 PM
    Tuesday, May 16, 2017 7:25 PM
  • Excellent Support Best Regards,  Anri  Yes...yes



    Anri

    • Marked as answer by Anri2018 Tuesday, May 16, 2017 7:26 PM
    Tuesday, May 16, 2017 7:26 PM
  • A strange thing I noticed in the list button, it was the following: Table 20 columns and you select for example the columns "Status", "Vendor","Class", they are listed some where in the middele of the table. So for some reason you get as well other unrequested columns added that you didn't select first?  I think it's may expecting that the selected columns needs to be at the front of the table?
    Which columns are returned by a query is entirely dependent on the SELECT clause, so if you use the asterisk character, the query will return all columns.  Otherwise you can return selcted columns, and can also return the columns in whatever order you wish.

    I very rarely open a query as a datasheet, but as a bound form or report.  You can then show whichever columns you wish in bound controls in the form or report.  If you want to return a datasheet, a bound form can of course be opened in datasheet view, but I would nearly always use a form in continuous forms view rather than a datasheet..


    Ken Sheridan, Stafford, England

    Tuesday, May 16, 2017 7:40 PM