none
Filtering Combo Box Options RRS feed

  • Question

  • I would like to filter the underlying query of a combo box.

    A combo box contains 'default' text that was entered on load, based on results from another form.

    The user should be able to see the 'default' text and then select from the dropdown (based on [tblFood].[Fruit]

    cbobox1 should filter as IIf([cbobox1] = "-", , Like "*" & [cbobox1] & "-")

    If the cbobox1 'default' is 'apple', i want all the 'apple' options to show in the dropdown.

    If the cbobox1 'default' is '-', i want nothing to show in the dropdown.

    When I change the code to  Like "*" & [cbobox1] & "*"  I get all the apple entries to choose from

    After I add the IIf statement, I get nothing, regardless of the 'default'.


    MS - Teach me to fish

    Wednesday, May 4, 2016 2:15 PM

Answers

  • Hi lismeta,

    Where did you put IIf([cbobox1] = "-", , Like "*" & [cbobox1] & "-")? I suggest you try to filter the RowSource of combobox according [tblFood].[Fruit].

    Here is a simple code:

    Private Sub Form_Load()
      Combo10.Value = "T"
      If Combo10.Value = "-" Then
        Combo10.RowSource = "SELECT [XYZ].[ID], [XYZ].[NName] FROM [XYZ];"
      Else
        Combo10.RowSource = "SELECT [XYZ].[ID], [XYZ].[NName] FROM [XYZ] where [XYZ].[NName] like " + "'*" + "t*'"
      End If
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by lismeta Friday, May 6, 2016 5:06 PM
    Thursday, May 5, 2016 3:07 AM

All replies

  • Hi. How is the default assigned? Maybe you can use the source of the default for the dropdown. For example: Like "*" & [defaultsource] & "*"

    Just a thought...



    • Edited by .theDBguy Wednesday, May 4, 2016 2:44 PM
    Wednesday, May 4, 2016 2:44 PM
  • Hi lismeta,

    Where did you put IIf([cbobox1] = "-", , Like "*" & [cbobox1] & "-")? I suggest you try to filter the RowSource of combobox according [tblFood].[Fruit].

    Here is a simple code:

    Private Sub Form_Load()
      Combo10.Value = "T"
      If Combo10.Value = "-" Then
        Combo10.RowSource = "SELECT [XYZ].[ID], [XYZ].[NName] FROM [XYZ];"
      Else
        Combo10.RowSource = "SELECT [XYZ].[ID], [XYZ].[NName] FROM [XYZ] where [XYZ].[NName] like " + "'*" + "t*'"
      End If
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by lismeta Friday, May 6, 2016 5:06 PM
    Thursday, May 5, 2016 3:07 AM
  • Thanks Edward.  That is what i was looking for.  with a few tweeks i got it to work.

    i guess i never thought of creating a function to drive the row source for a combo box.


    MS - Teach me to fish

    Friday, May 6, 2016 5:08 PM
  • Thanks DB, 

    I got it working using a sub procedure rathe than defining within access.  The default source was working good, but i am then comparing it to fields in a different table.  The sub procedure is working well, and i appreciate your input.


    MS - Teach me to fish

    Friday, May 6, 2016 5:10 PM