Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show all in a combo box (2003)

    I use a combo box to filter a form. I would like to add a line Show All to my combo box in order to turn off the filter. How to add this line in the combo that has a query as the row source. I am sure that I have seen the solution somewhere but I am not able to find it now.
    Regards,
    Marcel

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show all in a combo box (2003)

    How big is your query's SQL? Can you post it?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show all in a combo box (2003)

    Very small
    SELECT tblCompanies.ID, tblCompanies.Company FROM tblCompanies ORDER BY [Company];
    Marcel

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show all in a combo box (2003)

    Here is one solution, in VBA code. I know there has to be a way to do this in the query itself.. (perhaps with an IIF statement) but <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I'm not very good with those.

    I'm assuming you open the query on a command button click.

    Private Sub cmdButton_Click()

    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef

    If Me.cboSearch = "Show All" Then

    'Create your query omitting the WHERE clause
    strSQL = "SELECT tblCompanies.ID, tblCompanies.Company FROM tblCompanies ORDER BY [Company]; "
    Else

    'Insert a WHERE clause to refer to your combo box
    strSQL = "SELECT tblCompanies.ID, tblCompanies.Company FROM tblCompanies WHERE " & _
    "(COMPANY = [FORMS]![frmFormName]![cboCOMPANY] ORDER BY [Company]; "
    End If

    db.QueryDefs.Delete "qryQueryName"
    Set qdf = db.CreateQueryDef("qryQueryName", strSQL)

    DoCmd.OpenQuery "qryQueryName", acNormal, acReadOnly

    End Sub

    Make sure you are referencing the Microsoft DAO library.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Show all in a combo box (2003)

    Why don't you just allow Null then in the query that uses the value selected from the combobox simply set a criteria as (Like ComboBoxValue & '*' ).

    Alternatively setup your query as a Union query, then you will require code in the query that uses the value selected from the combobox to substitute a Null where ShowAll has been chosen.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show all in a combo box (2003)

    Pat,
    I thought about providing this IIF statement to allow Nulls to return all:

    (placed in the query criteria)
    IIF(IsNull([forms]![frmName]![cboName]),[company],[forms]![frmName]![cboName])

    You could put a disclaimer above your combo box saying <font color=red>If you wish to view all, leave combo box blank</font color=red>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show all in a combo box (2003)

    Ok, you put me on the right way. Finaly, I uae a union query:
    SELECT tblCompanies.ID, tblCompanies.Company FROM tblCompanies ORDER BY [Company] UNION SELECT "Show All","Show All" FROM tblCompanies ;
    and some lines of code:
    Private Sub cboCompany_AfterUpdate()

    Dim myFilter As String


    If cboCompany.Value = "Show All" Then
    Me.FilterOn = False
    Else
    myFilter = "[Company]=" & cboCompany.Value
    Me.Filter = myFilter
    Me.FilterOn = True
    End If
    Me.Requery

    End Sub

    Thank you for your help,
    Marcel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •