none
Synching two Combo Boxes that filter an Access form RRS feed

  • Question

  • Hi Everyone

    I have two combo boxes that filter an Access form by both year and month. They both use an "embedded macro" after update to run the filter. Both are below.

    The problem is that they do not work together. I cannot get records that meet both a specific year and month value combined. Is there a way to make them both work together to filter the form using both values?

    Paul

    Year

    After Update: Embedded Macro

    Record: First
    Where Condition: ="[ProgramYear] = " & Str(Nz([Screen].[ActiveControl],0))


    Month

    After Update: Embedded Macro

    Record: First
    Where Condition: ="[ProgramMonth] = " & "'" & [Screen].[ActiveControl] & "'"

    Thursday, December 10, 2015 1:24 AM

Answers

  • 1. Do you reference the form and form field by the name you see in the Properties box (ex. Combo123) or by the name of the field in the database? Same thing for the form.

    2. I am assuming you put the below query in the Record Source area of the Form's Property Sheet?

    3. I put the Me.Requery command in the Event Procedure for an AfterUpdate as you indicated and I believe that also threw an error.

    4. When I use the second version of the query below and when the form loads, I get two dialogue prompts for the year and month fields, which is not supposed to happen?

    1.  You reference the controls as the parameters, which you seem to have done in the second query.  However, I would recommend that when you add a control to a form you do not accept the meaningless name like Combo 352 which Access gives it, but immediately change it to something meaningful such as cboYear.

    2.  Correct.

    3.  You need to enter the Me.Requery line twice, once in the AfterUpdate event procedure of the year combo box, and once in the AfterUpdate event procedure of the month combo box.

    4.  With your current control names the query should be:

    SELECT *
    FROM programs
    WHERE (ProgramYear = [Forms]![Programs]![Combo352]
    OR [Forms]![Programs]![Combo352] IS NULL)
    AND (ProgramMonth = [Forms]![Programs]![Combo354]
    OR [Forms]![Programs]![Combo354] IS NULL);

    You might want to add an ORDER BY clause so that the rows are returned in some appropriate order.   As you quite rightly have no spaces or other special characters in the object names you can omit the square brackets if you wish.  The parentheses are essential, though.


    Ken Sheridan, Stafford, England

    • Marked as answer by Paul-NYS Friday, December 18, 2015 2:22 PM
    Tuesday, December 15, 2015 11:54 AM

All replies

  • I have to admint I never use macros but can you put both conditions in one macro joined by an 'and'?

    If not, that would be reason enough for me to abandon the use of macros forever, and learn VBA.

    You could convert these macros to VBA and see what it gives you and then modify the result.

    Thursday, December 10, 2015 1:41 AM
  • Why not reference the combo in the query that feeds your form and have an OnChange event to Refresh?

    Build a little, test a little

    Thursday, December 10, 2015 2:49 AM
  • Hi Paul,

    It is also easy to refresh the data by reset the filter via VBA. We can use VBA get the filter string based on the selection in the combo box.

    Here is a demo for your reference:

    Option Compare Database
    
    Private Sub ComboYear_AfterUpdate()
    refreshData
    End Sub
    
    Private Sub ComboMonth_AfterUpdate()
    refreshData
    End Sub
    
    Sub refreshData()
    
    ComboYear.SetFocus
    strYear = ComboYear.Text
    ComboMonth.SetFocus
    strMonth = ComboMonth.Text
    Me.Filter = ""
    
    If strYear <> "" Then
        Me.Filter = "year='" & strYear & "'"
        
        If strMonth <> "" Then
            Me.Filter = Me.Filter & " and month='" & strMonth & "'"
        End If
    Else
        If strMonth <> "" Then
            Me.Filter = " month='" & strMonth & "'"
        End If
    End If
    Me.FilterOn = True
    End Sub
    
    Regards & Fei


    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.

    Friday, December 11, 2015 2:45 AM
    Moderator
  • I tried adding the macro for one combo box to the other and it didn't work. One gave an error for type mismatch and the other nothing different happened.

    Paul

    Sunday, December 13, 2015 10:37 PM
  • I would suggest that you adopt Karl's solution and base the form on a query which references the two combo boxes as parameters, e.g.

    SELECT *
    FROM [TableNameGoesHere]
    WHERE ([ProgramYear] = Forms![FormNameGoesHere]![YearComboBoxNameGoesHere]
    OR Forms![FormNameGoesHere]![YearComboBoxNameGoesHere] IS NULL)
    AND ([ProgramMonth] = Forms![FormNameGoesHere]![MonthrComboBoxNameGoesHere]
    OR Forms![FormNameGoesHere]![MonthComboBoxNameGoesHere] IS NULL);

    Note that the parentheses in the above are very important, to force the OR operations to evaluate independently of the AND operation.  You can add an ORDER BY clause to the query to sort the rows on one or more columns if you wish.

    Then in each of the combo box's AfterUpdate events all you need to do is Requery the form (NB:  not the Change event and Refresh method as Karl said), which you can do with a macro or a single line of code in each control's AfterUpdate event procedure:

        Me.Requery

    The form will progressively show rows for the selected year and month as values are selected in the combo boxes.  Clearing them to Null will show all rows.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, December 14, 2015 12:01 AM Typo corected.
    Monday, December 14, 2015 12:00 AM
  • Hi Fei

    The below code is what I tried using in each of the two combo boxes (same code for each combo box). I am getting an error for ambiguous name (Combo352_AfterUpdate()). I don't know anything about VB.

    Paul

    Private Sub Combo352_AfterUpdate()
    refreshData
    End Sub

    Private Sub Combo354_AfterUpdate()
    refreshData
    End Sub

    Sub refreshData()

    Combo352.SetFocus
    strYear = Combo352.Text
    Combo354.SetFocus
    strMonth = Combo354.Text
    Me.Filter = ""

    If strYear <> "" Then
        Me.Filter = "year='" & strYear & "'"
        
        If strMonth <> "" Then
            Me.Filter = Me.Filter & " and month='" & strMonth & "'"
        End If
    Else
        If strMonth <> "" Then
            Me.Filter = " month='" & strMonth & "'"
        End If
    End If
    Me.FilterOn = True
    End Sub

    Private Sub Combo352_AfterUpdate()

    End Sub

    Private Sub Combo354_AfterUpdate()

    End Sub
    Monday, December 14, 2015 12:47 AM
  • That sounds like an easy idea, but how do you do it? I put the below in the Form Filter area in the property sheet and it didn't do anything.

    select * from programs where ProgramYear=Form!Combo352 and ProgramMonth=Form!Combo354;

    Also, what am I refreshing for the On Change event?  The form itself? I don't see an On Change event for the form?

    Paul

    Monday, December 14, 2015 1:49 AM
  • Also, what am I refreshing for the On Change event?  The form itself? I don't see an On Change event for the form?

    On change of the combo.

    Build a little, test a little

    Monday, December 14, 2015 2:11 AM
  • 1.  You create the query in the way I described in my earlier post, and set the form's RecodSource property to the query.

    2.  You do NOT use the Change event procedure.  That executes every time you change a character in the control.  You use the AfterUpdate event procedures of each combo box which executes after the value of the control is updated.

    3.  You Requery the form, not Refresh it.  The former reloads the form's recordset, which is what you want to do here, so that it is restricted on the basis of the parameter values you've selected in the combo boxes.

    If you are unfamiliar with entering code into a form's or control's event procedures, this is how it's done in form design view:

    1.  Select the form or control as appropriate and open its properties sheet if it's not already open.

    2.  Select the relevant 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 new lines between these.

    Ken Sheridan, Stafford, England

    Monday, December 14, 2015 11:14 AM
  • Hi Ken

    Thanks for the response and sample code. I am not exact sure how to reference the form and form field in a query, so I tried it both of the ways below. Neither worked. So I have a few more questions:

    1. Do you reference the form and form field by the name you see in the Properties box (ex. Combo123) or by the name of the field in the database? Same thing for the form.

    2. I am assuming you put the below query in the Record Source area of the Form's Property Sheet?

    3. I put the Me.Requery command in the Event Procedure for an AfterUpdate as you indicated and I believe that also threw an error.

    4. When I use the second version of the query below and when the form loads, I get two dialogue prompts for the year and month fields, which is not supposed to happen?

    I am also attaching a screenshot of the DB below.

    Paul

    SELECT *
    FROM programs
    WHERE (ProgramYear = [Forms]![Programs]![ProgramYear]
    OR [Forms]![Programs]![ProgramYear] IS NULL)
    AND (ProgramMonth = [Forms]![Programs]![ProgramMonth]
    OR [Forms]![Programs]![ProgramMonth] IS NULL);


    SELECT *
    FROM programs
    WHERE (ProgramYear = [Forms]![Form]![Combo352]
    OR [Forms]![Form]![Combo352] IS NULL)
    AND (ProgramMonth = [Forms]![Form]![Combo354]
    OR [Forms]![Form]![Combo354] IS NULL);

    Tuesday, December 15, 2015 2:00 AM
  • 1. Do you reference the form and form field by the name you see in the Properties box (ex. Combo123) or by the name of the field in the database? Same thing for the form.

    2. I am assuming you put the below query in the Record Source area of the Form's Property Sheet?

    3. I put the Me.Requery command in the Event Procedure for an AfterUpdate as you indicated and I believe that also threw an error.

    4. When I use the second version of the query below and when the form loads, I get two dialogue prompts for the year and month fields, which is not supposed to happen?

    1.  You reference the controls as the parameters, which you seem to have done in the second query.  However, I would recommend that when you add a control to a form you do not accept the meaningless name like Combo 352 which Access gives it, but immediately change it to something meaningful such as cboYear.

    2.  Correct.

    3.  You need to enter the Me.Requery line twice, once in the AfterUpdate event procedure of the year combo box, and once in the AfterUpdate event procedure of the month combo box.

    4.  With your current control names the query should be:

    SELECT *
    FROM programs
    WHERE (ProgramYear = [Forms]![Programs]![Combo352]
    OR [Forms]![Programs]![Combo352] IS NULL)
    AND (ProgramMonth = [Forms]![Programs]![Combo354]
    OR [Forms]![Programs]![Combo354] IS NULL);

    You might want to add an ORDER BY clause so that the rows are returned in some appropriate order.   As you quite rightly have no spaces or other special characters in the object names you can omit the square brackets if you wish.  The parentheses are essential, though.


    Ken Sheridan, Stafford, England

    • Marked as answer by Paul-NYS Friday, December 18, 2015 2:22 PM
    Tuesday, December 15, 2015 11:54 AM
  • Hi Ken

    This worked perfectly! Thanks a lot! I was referencing the form incorrectly apparently.

    I don't have the database in front of me now, but one quick follow up, to create a button that refreshes the page and restores all records to the form, would be a form level button with 'me.requery' in it?

    Thanks again.

    Paul

    Friday, December 18, 2015 2:22 PM