Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: How to include “ALL” in a Combo Box

    I have a “OwnerName” Query which lists all the Owner Names.
    I have a Combo Box with a Row Source linked to this same Query.
    The user has the choice to click on ONE of the Owners Names.

    But how can I add the option to choose “ALL” the Owners?

    I have researched the internet but I am not very good at SQL and I am not able to find an easy way to do this or where to put it!!!???
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Thanks for the link, Sinndho. I will be working on this today.

    It is a shame they didn't build this functionality into Access 2010.
    What would you attempt to do if you knew you would not fail?

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    This links appears to be discussing how to add as a "null" field.
    My bound field is ContractID from a select statement, and so I don't think the method described will work for my purpose?
    I want the User to see the ContractID and then to choose ONE of them or ALL of them.
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You add whatever you want to the extra row in the list of the Combo, not only Null. When this special case is selected ("All" in your case), you change the way the SQL expression is built. For selecting all rows, you omit the WHERE clause. It's hard to be more precise without knowing how the query is built.
    Have a nice day!

  6. #6
    Join Date
    Nov 2009
    Posts
    223
    SELECT Customers.[CustomerID], Customers.[CustomerName] FROM Customers UNION Select Null as AllChoice, "ALL" as Bogus FROM Customers;
    Thanks for the guidance, Sinndho.

    I have changed the SQL as above but the ALL choice is a BLANK space.
    How would i change the above SQL to show the word ALL above the CustomerID number?
    What would you attempt to do if you knew you would not fail?

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    SELECT Customers.[CustomerID], Customers.[CustomerName] FROM Customers UNION SELECT '(All)', null FROM Customers;
    I fiddled around with it, researched again and found this code.
    I guess this is correct.
    Whehey.
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    But then I get an error message about 'the number of columns in the two selected tables or queries of a union query do not match'.

    SELECT qryFindContract.ContractID, qryFindContract.StartDate, qryFindContract.EndDate, qryFindContract.OwnerName1
    FROM qryFindContract UNION SELECT '(All)', null FROM qryFindContract
    What would you attempt to do if you knew you would not fail?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In a UNION query, the number of columns on both sides of the UNION operator must be the same. Try:
    Code:
    SELECT Customers.[CustomerID], Customers.[CustomerName] 
    FROM Customers 
    UNION Select 'ALL' , 'ALL'  
    FROM Customers;
    Or:
    Code:
    SELECT qryFindContract.ContractID, qryFindContract.StartDate, qryFindContract.EndDate, qryFindContract.OwnerName1
    FROM qryFindContract 
    UNION SELECT '(All)', '(All)', '(All)', '(All)' 
    FROM qryFindContract
    Or:
    Code:
    SELECT qryFindContract.ContractID, qryFindContract.StartDate, qryFindContract.EndDate, qryFindContract.OwnerName1
    FROM qryFindContract 
    UNION 
    SELECT '(All)', Null, Null, Null 
    FROM qryFindContract
    Now, I suppose that the selected value of the combo is used in a query, such as:
    Code:
    SELECT * 
    FROM <Some Table or Query>
    WHERE Customers.CustomerID = Forms!Form1!Combo1
    For working with the combo with the "(All)" row, you change the query like this:
    Code:
    SELECT * 
    FROM <Some Table or Query>
    WHERE Customers.CustomerID Like IIf(Forms!Form1!Combo1 = '(All)', '*', Forms!Form1!Combo1)
    Have a nice day!

  10. #10
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Your explanation is fantastic. I don't think there is a simpler explanation on the internet?
    Thanks for taking the time to explain about it with Tables and Queries.

    I am very close to finalising it. And I can get (All) showing in the ComboBox. The only thing is I get a TypeMismatch error when I choose the (All) option (but it is Ok when I choose any of the CustomerID options??

    SELECT tblCustomers.CustomerID FROM tblCustomers UNION SELECT '(All)' FROM tblCustomers;
    This brings about a Type Mismatch error due to an embedded Macro?

    frmChoose : cboChoose : AfterUpdate : Embedded Macro
    Action name: SearchForRecord
    Arguments: -1, , First, ="[CustomerID] = "& Str(Nz([Screen.[ActiveControl], 0))
    What would you attempt to do if you knew you would not fail?

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use a VBA procedure instead of a macro. When the form is in design view:
    - select the Combo (I suppose it's name is cboChoose),
    - open the Properties window (Alt+Enter),
    - in the Properties window select the Events tab,
    - select the After Update event line,
    - open the tiny combo (down arrow at the right of the line),
    - select [Event Procedure],
    - click on the Assistant button (...) at the right of the tiny combo,
    - the VBA Editor will open with the skeleton of a procedure for handling the AfterUpdate event for the combo:
    Code:
    Private Sub cboChoose_AfterUpdate()
        
    End Sub
    - between the Private Sub and End Sub lines, add the following code:
    Code:
        Const c_Criteria As String = "[CustomerID] Like '@V'"
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        Set rst = Me.RecordsetClone
        strCriteria = Replace(c_Criteria, "@V", Me.cboChoose.Value)
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        Set rst = Nothing
    - compile and save the module.
    Have a nice day!

  12. #12
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    I am so pleased you managed to reply before I go to sleep!

    By the way, I did not use a macro - Access 2010 seemd to automatically insert the Macro for me??!!

    Whehey, your VBA code has worked perfectly. I can now select "All" without any problems at all.

    I think this thread will be looked at by many other coders - coz the instructions on the web are not as clear as yours.

    I am off to sleep now (I am running in a marathon tomorrow)!
    What would you attempt to do if you knew you would not fail?

  13. #13
    Join Date
    Nov 2009
    Posts
    223
    I am using the Query to open a Report.

    In the Report I have inserted the following Criteria in the ID Field.

    [Forms]![frmChoose]![cboChoose]
    Which should filter all Records to be only the ID chosen in the Combo.

    It works perfectly when the ID is a number.

    But the Report is blank whenever the "All" option is chosen.
    What would you attempt to do if you knew you would not fail?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can open the report and pass a criteria to it using VBA code:
    - Add a button to open the Report and create a VBA event handler for its Click property, or change the event handler of the already existing button (same technique as for the AfterUpdate event handler for the combo),
    - Use this code in the VBA procedure:
    Code:
        Const c_Criteria As String = "[CustomerID] Like '@V'"
        Dim strCriteria As String
        
        strCriteria = Replace(Me.cboChoose.Value, "(All)", "*")
        strCriteria = Replace(c_Criteria, "@V", Replace(Me.cboChoose.Value, "(All)", "*"))
        DoCmd.OpenReport "Report1", acViewPreview, , "[CustomerID] Like"
    Note: The is something wrong in the example in my previous post: you need to change the "(All)" value to "*" when it is selected in the combo.
    Here's the corrected VBA code I used in the module of my test form (Command_OpenReport1 is a command button that opens a form named Rpt_CF_Data1, while CF_Data1is the source data table for the form and for the report):
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cboChoose_AfterUpdate()
    
        Const c_Criteria As String = "[CustomerID] Like '@V'"
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        Set rst = Me.RecordsetClone
        strCriteria = Replace(Me.cboChoose.Value, "(All)", "*")
        strCriteria = Replace(c_Criteria, "@V", Replace(Me.cboChoose.Value, "(All)", "*"))
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        Set rst = Nothing
        
    End Sub
    
    Private Sub Command_OpenReport1_Click()
    
        Const c_Criteria As String = "[CustomerID] Like '@V'"
        Dim strCriteria As String
        
        strCriteria = Replace(Me.cboChoose.Value, "(All)", "*")
        strCriteria = Replace(c_Criteria, "@V", Replace(Me.cboChoose.Value, "(All)", "*"))
        DoCmd.OpenReport "Rpt_CF_Data1", acViewPreview, , strCriteria
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        Me.cboChoose.RowSource = "SELECT CustomerID FROM CF_Data1 UNION SELECT '(All)' FROM CF_Data1;"
        
    End Sub
    You can also chose to modify the query (Frm_CF_Data1 is the name of my test form):
    a) In Design View:
    - Field: CustomerID
    - Criteria:
    Like IIf([forms]![Frm_CF_Data1]![cboChoose]="(All)";"*";[Forms]![Frm_CF_Data1]![cboChoose])
    b) or in SQL View:
    Code:
    SELECT CF_Data1.*
    FROM CF_Data1
    WHERE (((CF_Data1.CustomerID) Like IIf([forms]![Frm_CF_Data1]![cboChoose]="(All)","*",[Forms]![Frm_CF_Data1]![cboChoose])));
    Another alternative would consist in changing the SQL statement of the query used by the report as its RecordSource.
    Have a nice day!

  15. #15
    Join Date
    Nov 2009
    Posts
    223
    Whehey - fantastic. Everything works like a dream now.

    Thanks so much to Sinndho who explained everything in simple terms (that is necessary for me) and gave excellent examples/samples to follow.

    My database looks sooooooo professional now (well, I think anyway).
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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