locked
Add an "All" option to a combo box in Access 2013 RRS feed

  • Question

  • Thank you in advance for your valuable assistance.

    I created a single file form with a subform, then was able to add a combo box that would display the records for any owner I selected.  I was able to use DISTINCT to show owners only once in the combo box.  Now I am trying to add an option to show all the records.  I have not been successful with any of the suggestions I found on-line.

    Again, thank you for your help!

    Friday, December 4, 2015 12:42 AM

Answers

  • Hi. Try changing this line:

    myPE = "Select * FROM tblData WHERE ([PI_Product Engineer] = '" & Me.cboPE & "')"

    to this:

    myPE = "Select * FROM tblData WHERE ([PI_Product Engineer] Like '" & Me.cboPE & "')"

    Hope that helps...

    • Marked as answer by Novice Lois Wednesday, December 9, 2015 4:36 AM
    Wednesday, December 9, 2015 2:22 AM

All replies

  • Hi. Not sure what help you need. Were you having difficulty adding the ALL option or on what to do when the use selects all?
    Friday, December 4, 2015 1:13 AM
  • I cannot get the "All" option to appear on the combo box so it will display all the records.
    Friday, December 4, 2015 2:15 AM
  • Please post the Row Source of your Combo box.
    Friday, December 4, 2015 2:27 PM
  • Also post the WHERE statement that you use the combo as criteria.

    Build a little, test a little

    Friday, December 4, 2015 5:45 PM
  • When I worked on this today, I found some help that had me put in this code in the Row Source.

    SELECT [PI_ProductEngineer] As Filter, [PI_ProductEngineer] FROM tblData UNION SELECT "*" As Filter," All" as [PI_ProductEngineer] FROM tblData;

    With Bound Column set to 1, Column Count set to 2 and Column widths set to 0,2

    But I got an error of "The ORDER BY expression ([PI_ProductEngineer]) includes fields that are not selected by the query.  Only those fields requested in the first query can be included in an ORDER BY expression."  Removing the ORDER BY part of the statement only served to trigger a "Enter Parameter Value" box request.  If I enter a value, that value and the word All appear.

    This is an Unbound combo box with a Row Source Type of Table/Query.

    In an earlier attempt I could get the list of Product Engineers to display when I put this code in the After Update. And it worked fine, except I need to be able to return to the full list.

    Private Sub cboProdEng_AfterUpdate()
    Dim myengineer As String
    myengineer = "Select * from tblData where ([PI_Product Engineer] = '" & Me.cboProdEng & "')"
    Me.frmDatasubform.Form.RecordSource = myengineer
    Me.frmDatasubform.Requery
    End Sub

    What I thought might be simple and straightforward, isn't.  If you need more information, I will gladly provide it.

    Thank you again for your time and thoughtful assistance.

    Saturday, December 5, 2015 3:24 AM
  • Uh-oh. I can't find a single WHERE statement that I am using. I'm not terribly learned in SQL and hope for a small education.

    Thank you!

    Saturday, December 5, 2015 3:29 AM
  • In your Sub cboProdEng_AfterUpdate, if the value of the combo box is empty or null then the record source should just be Select * From tblData, without any where clause. The user can leave the combo box unselected, or delete an existing selection, and all rows would be shown in the form.

    If you want to include a dropdown record for All then you could use a union query, something like: Select engineerId, engineerName From Engineers Union All Select Top 1 null as engineerId, "All Engineers" as engineerName From Engineers. Or you could use code when the form opens to add an equivalent item to the combo box rows.

    Here's one link I found searching on the web:

    https://support.microsoft.com/en-us/kb/210290


    Paul

    Saturday, December 5, 2015 12:54 PM
  • Thank you for the suggestion of the link.  I had found that site, but still cannot get the code to load so that the AddAllToList appears in the RowSourceType.

    When I tried the Union query method I got the error that the fields are not selected by the query.

    Thank you for your gracious help.

    Sunday, December 6, 2015 3:03 AM
  • Hi. We might have to see a copy of your db to help you figure this out.
    Sunday, December 6, 2015 3:27 PM
  • You'll find an example in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the RowSource of the combo box is:

    SELECT DISTINCT LastName, 1 As SortColum
    FROM Contacts
    UNION
    SELECT "All Names", 0 FROM Contacts
    ORDER BY SortColum, LastName;

    and the code in its AfterUpdate event procedure is:

        Const MESSAGETEXT = "No matching records found."
        Dim ctrl As Control
        Dim strFilter As String
        
        Set ctrl = Me.ActiveControl
        
        strFilter = "LastName = """ & ctrl & """"
        
        If ctrl = "All names" Then
            ' turn off filter
            Me.FilterOn = False
            ' synchronize filter by name combo box
            ' with current record
            ctrl = Me.LastName
      Else
            If Not IsNull(DLookup("ContactID", "Contacts", strFilter)) Then
                ' flter form to name selected in combo box
                Me.Filter = strFilter
                Me.FilterOn = True
            Else
                ' inform user if no matching records found and show all records
                MsgBox MESSAGETEXT, vbInformation, "Warning"
                Me.FilterOn = False
                Me.Requery
            End If
        End If

    Ken Sheridan, Stafford, England

    Sunday, December 6, 2015 6:22 PM
  • Hi, DB guy!

    I made some progress today.  I can now get "All" to display in the combo box along with the names of the Product Engineers, but when I click on "All", no records are returned.

    In the Row Source I used:

    SELECT DISTINCT "*" as Cat, " All" as Dog from tblData UNION SELECT  [PI_Product Engineer] as Cat, [PI_Product Engineer] as Dog FROM tblData;

    (I know...not very professional, but it made it easier to keep track of names for the time being.  I can always change it or clean in up later when I understand better how it all works.)

    The Row Source Type is Table/Query, the Bound Column is 1, the Limit to List is Yes, the Column Count is 2, the Column Widths is 0",1" and the After Update [Event Procedure] is:

    Dim myPE As String
    myPE = "Select * FROM tblData WHERE ([PI_Product Engineer] = '" & Me.cboPE & "')"
    Me.frmDatasubform2.Form.RecordSource = myPE
    Me.frmDatasubform2.Form.Requery
    End Sub

    This is actually a very simple database with one table that contains the fields (it was taken from an Excel spreadsheet) and I doubt it would pass the normalization test.  If you really need the database, please offer instructions so I can post it correctly.

    With great appreciation!

    Wednesday, December 9, 2015 2:14 AM
  • Hi. Try changing this line:

    myPE = "Select * FROM tblData WHERE ([PI_Product Engineer] = '" & Me.cboPE & "')"

    to this:

    myPE = "Select * FROM tblData WHERE ([PI_Product Engineer] Like '" & Me.cboPE & "')"

    Hope that helps...

    • Marked as answer by Novice Lois Wednesday, December 9, 2015 4:36 AM
    Wednesday, December 9, 2015 2:22 AM
  • YOU ARE BRILLIANT!!  That answer was so obvious it makes me hit my forehead and yelp "DOH!"

    Thanks to everyone for their input (though the complexity of the code was over my head).  :-)

    I so appreciate your generosity of time and education.

    Happiest of holidays to all!!

    Wednesday, December 9, 2015 4:40 AM
  • Hi. You're welcome! Glad to hear you got it to work. We're all happy to assist. Good luck with your project and Merry Christmas!
    Wednesday, December 9, 2015 3:45 PM