Synching two Combo Boxes that filter an Access form

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] & "'"

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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);

Answer 11

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

Answer 12

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