Two Combo Boxes to Produce a Filtered Subform Datasheet

Question

Two Combo Boxes to Produce a Filtered Subform Datasheet

Patrick Snyder 5

I have a form that has a subform. The subform has a datasheet on it. There's a Combo box that filters the datasheet, on the subform.

How do I create more than one combo box that allows the user to filter the datasheet by more than one field?

Example: my table has many fields; two of the fields are labeled color and size. Right now, my combo box filters by color but I also want to filter by size. So, I want my form to have two different combo boxes to help filter the datasheet.

It would be nice that the user can filter by color or size or by color than by size.

How can I do this?

0 comments

7 answers

Your answer

Answer 1

Dora-T 13,745 Microsoft External Staff Moderator

Hi @Patrick Snyder

Thank you for your response.

Based on your screenshot, the issue is likely caused by a syntax error in the SQL statement. It appears that some spaces between keywords are missing, so Access is not able to interpret it correctly.

Please try updating it using the Zoom window as follows:

-Open the Material form in Design View.

-Select the second combo box (RFQ).

-Open the Property Sheet and go to the Data tab.

-Right-click in the Row Source field and choose Zoom….

-In the Zoom window, replace the existing text with the corrected SQL, then click OK.

SELECT DISTINCT RFQ_num
FROM Data
WHERE Cty = Forms!Material!Combo5
ORDER BY RFQ_num;

-Save the form, close it, and reopen it.

User's image

This should resolve the syntax error and allow the RFQ combo box dropdown to display the correct values based on the selected Country.

Please try this on your side and let me know if it works.

Patrick Snyder 5 Reputation points

2026-01-21T19:34:16.5533333+00:00

Dora, I am just getting to making those changes and I just tested them and it works! Awesome, thank you.
Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2026-01-22T07:29:06.4066667+00:00

Hi Patrick Snyder

Thank you for your update. I’m glad to hear everything is working now.

If this response was helpful, please consider marking it as the accepted answer. This helps others in the community who may be facing similar challenges, and it can build our platform stronger.

Thank you for your time and understanding!
Patrick Snyder 5 Reputation points

2026-02-12T19:23:07.7366667+00:00

Dora,

How can I take the above and move it to a tab within the form?

I've tried to add a tab and then cut and paste to the actual tab. When I select the first combo box, it works but when I select the second combo box, the drop down is empty and the table doesn't change.
Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2026-02-25T02:46:53.1833333+00:00

Hi Patrick Snyder

Thank you for your question.

It looks like this issue has already been resolved in your other thread, so I’ll close this thread for now.

If you have any further questions or run into any other issues, please feel free to post a new thread anytime.

Best regards.

Answer 2

Ken Sheridan 3,571

Lets assume that the subform's table has columns Color and Size each of which references single column Colors and Sizes tables respectively. The RowSouce properties for the two unbound combo boxes in the subform's header, named cboFindColor and cboFindSize for instance, would thus be:

SELECT Color FROM Colors ORDER BY Color;

and:

SELECT Size FROM Sizes ORDER BY Size;

The RecordSource property of the subform would be along these lines:

SELECT *
FROM Widgets
WHERE (Color = Form!cboFindColor 
    OR Form!cboFindColor IS NULL)
AND (Size = Form!cboFindSize
    OR Form!cboFindSize IS NULL)

Note how, as the combo boxes are in the subform, in the above the Form property is used rather than referencing the subform by name. In the AfterUpdate event procedure of each combo box requery the form with:

Me.Requery

George Hepworth 22,855 Reputation points Volunteer Moderator

2025-12-28T21:25:42.2533333+00:00

This approach, as suggested by Ken, is the cleanest and most straightforward approach to a two filter approach.

However, I'm not sure that you can display a form header on a form in datasheet view, either by itself or when embedded as a subform.

"There's a Combo box that filters the datasheet, on the subform."

Therefore, I think you'll need to switch to continuous view for the subform with the form header where the filtering combo box is placed.

Answer 3

Patrick Snyder 5

Hi, Dora,

I've been out of the office on holiday vacation. I may get a chance to review and try it later this week.

Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2025-12-29T02:36:55.35+00:00

Hi Patrick Snyder
Thank you for your response.

If you have any update, please don’t hesitate to post back.

Hope you have a wonderful holiday.
Patrick Snyder 5 Reputation points

2026-01-16T15:53:19.2133333+00:00

Hello Dana, I created a bogus file. Please understand that there is information that I am unable to share so all of the values in the data table are generic. I also reduced the amount of data but I believe there's enough here to help me, I hope!

Form named "Material": there is a combo box there currently, I would like to know how to add a second combobox so that the first combo is Country and the second combo is RFQ_Number, then the results are filtered down into the subform.

[Moderator note: personal info removed]

File attached is a *.accdb file. I changed it to a *.txt file so that I can attach it to this discussion.

I also deleted a form that is connect to a Marco so when you open the file, there's an error because the macro is looking for a form that was removed. Just ignore it.

I hope I gave you enough to help me.

Thanks,
Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2026-01-19T14:22:41.95+00:00
Hi Patrick Snyder

Thank you for the update and for sharing the file.

I’ve tested it on my side, and to get the two combo boxes working properly (filter by Country only, by RFQ Number only, or by both), you can try the steps below.

1.Remove the existing subform link:

Open the main form in Design View.

Select the Country subform control.

Clear both Link Master Fields and Link Child Fields so the subform is no longer tied to a single combo box.

2.Check the Country form:

Open the Country form in Design View.

Set the Record Source to the base data only (for example: SELECT * FROM Data;).

Make sure there is no filter or WHERE condition applied on this form.

On the Material form:

Keep both combo boxes unbound.

I also added a Clear filter button so it’s easier to reset the results and start a new search.

After that, open the VBA editor (Alt + F11) and add the code below to the code-behind of the Material form:

Option Compare Database Private Sub Combo5_AfterUpdate() RefreshSubform End Sub Private Sub Combo10_AfterUpdate() RefreshSubform End Sub Private Sub btnClear_Click() Me.Combo5 = Null Me.Combo10 = Null RefreshSubform End Sub Private Sub RefreshSubform() Dim strSQL As String Dim strWhere As String strSQL = "SELECT * FROM Data" strWhere = "" If Not IsNull(Me.Combo5) Then strWhere = "[Cty] = '" & Me.Combo5 & "'" End If If Not IsNull(Me.Combo10) Then If strWhere <> "" Then strWhere = strWhere & " AND " strWhere = strWhere & "[RFQ_num] = '" & Me.Combo10 & "'" End If If strWhere <> "" Then strSQL = strSQL & " WHERE " & strWhere End If With Me.Country.Form .RecordSource = strSQL .Requery End With End Sub

With this setup, you should be able to filter by either combo box independently, or by both together, and the Clear filter button will bring the data back to its original state.

Please give it a try on your side and let me know how it goes.
Please note that our forum is a public platform, and we will modify your question to hide your personal information in the description. Kindly ensure that you hide any personal or organizational information the next time you post an error or other details to protect personal data.
Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2026-01-20T13:05:15.5533333+00:00
Hi Patrick Snyder

Thanks for the update.

Just to clarify, the file I moved to the private message is the same file you shared. Since this is a public forum, I moved it there to avoid exposing your file publicly. I didn’t make any changes to that file itself.

Regarding the second combo box, if you want it to update and show only RFQ values related to the selected Country, please follow the steps below. -Open the Material form in Design View.

-Go to Form Design and add a Combo Box.

-Set the Row Source of the second combo box to:

SELECT DISTINCT RFQ_numFROM DataWHERE Cty = Forms!Material!Combo5ORDER BY RFQ_num;
This makes the RFQ combo box dependent on the selected Country.

To add the VBA code to the Material form

-Open the Material form in Design View.

-Select one of the combo boxes on the form.

-Open the Property Sheet, go to the Event tab.

-For After Update, select [Event Procedure], then click the … button.

-Paste the entire code block below into this window:

Option Compare Database Private Sub Combo5_AfterUpdate() Me.Combo10 = Null Me.Combo10.Requery RefreshSubform End Sub Private Sub Combo10_AfterUpdate() RefreshSubform End Sub Private Sub btnClear_Click() Me.Combo5 = Null Me.Combo10 = Null Me.Combo10.Requery RefreshSubform End Sub Private Sub RefreshSubform() Dim strSQL As String Dim strWhere As String strSQL = "SELECT * FROM Data" strWhere = "" If Not IsNull(Me.Combo5) Then strWhere = "[Cty] = '" & Me.Combo5 & "'" End If If Not IsNull(Me.Combo10) Then If strWhere <> "" Then strWhere = strWhere & " AND " strWhere = strWhere & "[RFQ_num] = '" & Me.Combo10 & "'" End If If strWhere <> "" Then strSQL = strSQL & " WHERE " & strWhere End If With Me.Country.Form .RecordSource = strSQL .Requery End With End Sub

-Save and close the code window.

-Close the form and reopen it.

I’ve attached screenshots below showing how I set this up based on the file you shared. Unfortunately, I’m not able to send the file back directly, so please refer to the screenshots for reference.

Please try these steps on your side and let me know how it goes.

Answer 4

Dora-T 13,745 Microsoft External Staff Moderator

Hi Patrick Snyder

Thank you for reaching out to the Microsoft Q&A Forum.

Based on your description, I understand that you’re trying to filter a datasheet subform using more than one combo box, allowing users to filter by color, size, or both.

At the moment, I’m actively testing a solution suggested by Q&A Assist. However, the initial results indicate that this approach does not fully work as expected, so I need a bit more time to continue testing and validating it further before sharing any next steps to avoid providing an inaccurate solution or causing unnecessary support delays.

Thank you for your patience and understanding. I’ll keep you posted whenever I have any confirmed updates.

If you have any additional details you’d like to share in the meantime, please feel free to let me know.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2025-12-24T05:22:00.8766667+00:00

Hi Patrick Snyder

Based on your description, I set up a main form with a datasheet subform and configured multiple combo boxes to filter the data. However, when selecting values from the dropdowns, the subform didn’t return the expected results.

If possible, could you please share the sample file you’re currently working with for the combo box setup? That would help me better understand your scenario and continue testing more accurately.

To avoid exposing any personal or organizational information, please share the sample file via private message.

Thank you for your patience and cooperation.
Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2025-12-25T06:07:39.16+00:00

Hi Patrick Snyder
I wanted to follow up to see if you’ve had a chance to review my previous reply. When you have a moment, please share the sample file so I can continue testing and assist you further.
Patrick Snyder 5 Reputation points

2026-01-09T16:26:55.58+00:00

Hello Dora, i'm just now catching up on all of my many emails!!!!

I'm sorry that I don't have a sample file that I can share.
Dora-T 13,745 Reputation points Microsoft External Staff Moderator

2026-01-12T03:11:20.38+00:00

Hi @Patrick Snyder

Thank you for getting back to me.

Since a sample file is not available, I recommend first trying the approach suggested by Ken Sheridan to see whether it works in your scenario.

If the issue still persists or the behavior is not as expected, I recommend raising a support ticket with the Microsoft Support team so they can review your setup in more detail and provide more targeted assistance.

-For Business accounts, please ask your administrator to raise a support ticket through the Microsoft 365 Admin Center. You can refer to this article: Get support - Microsoft 365 admin | Microsoft Learn

-For Personal accounts, you can contact Microsoft Support directly via: Contact - Microsoft Support

Please note that as a moderator, my role is to support the discussion and help point you to relevant resources. However, I don’t have direct access to backend systems or your environment, so contacting Microsoft Support would be the most effective next step.

Thank you for your patience and understanding.

Answer 5

To create two combo boxes that allow users to filter a datasheet in a subform by multiple fields (like color and size), you can follow these general steps:

Add Combo Boxes: Add two combo boxes to your main form. One will be for filtering by color and the other for filtering by size.
Set Up Row Sources: For each combo box, set the Row Source to the appropriate field in your table. For example, the first combo box should have a Row Source that pulls distinct values from the color field, and the second combo box should pull values from the size field.
Create a Filter Query: You will need to create a query that combines the filters from both combo boxes. This can be done using VBA or by setting the filter property of the subform based on the selections in the combo boxes.
Implement Filtering Logic: In the AfterUpdate event of each combo box, write code that applies the filter to the subform. For example:
- If both combo boxes have values, filter by both fields.
- If only one combo box has a value, filter by that field only.
- If neither combo box has a value, show all records.
Refresh the Subform: After applying the filter, refresh the subform to display the filtered results.

Here is a simple example of what the VBA code might look like in the AfterUpdate event of the combo boxes:

Private Sub cboColor_AfterUpdate()
    ApplyFilter
End Sub

Private Sub cboSize_AfterUpdate()
    ApplyFilter
End Sub

Private Sub ApplyFilter()
    Dim strFilter As String
    strFilter = ""

    If Not IsNull(Me.cboColor) Then
        strFilter = strFilter & "Color = '" & Me.cboColor & "'"
    End If

    If Not IsNull(Me.cboSize) Then
        If strFilter <> "" Then strFilter = strFilter & " AND "
        strFilter = strFilter & "Size = '" & Me.cboSize & "'"
    End If

    Me.subformName.Form.Filter = strFilter
    Me.subformName.Form.FilterOn = True
End Sub

This code constructs a filter string based on the selections in the combo boxes and applies it to the subform.

By following these steps, users will be able to filter the datasheet by color, size, or both, enhancing the usability of your form.

References:

Share via

Two Combo Boxes to Produce a Filtered Subform Datasheet

7 answers

Your answer