Access 2013 - Embedded query filtered by combo box on form

Question

I'm new to Access and this is the problem I'm suffering: I have four tables - Task, Person, Role, and TaskPerson (mapping table). I have a form that at the top has a unbound combo box displaying a list of people from Person. In the body of the form I have a query pulling from the Task and TaskPerson tables that is embedded as a datasheet. The fields from TaskPerson perform a lookup on Person and Role to display the actual values. Each task can have multiple people assigned to it and each person can have multiple roles. I am looking to pick a name from the combo box with the datasheet updating to only show the tasks associated with that person (i.e. matching the name from the combo box to the name in the person field (which is a lookup) on the form and only showing those tasks).

I have tried adjusting the Record Source for the query so the person field criteria would pull from the combo box using

'Forms![Task Form]![Combo11]'

but that hasn't worked. I have also tried a version of this answer:

Private Sub Form_SelectionChange()

' If the combo box is cleared, clear the form filter.
  If Nz(Form) = "" Then
  Me.Form.Filter = ""
  Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo11.ListIndex <> -1 Then
  Me.Form.Filter = "[Combo11] = '" & _
             Replace(Me.Combo11.Text, "'", "''") & "'"
  Me.FilterOn = True
  End If

End Sub

While the code is not balking, it also isn't grabbing the selected name from the combo box, so it doesn't update. A likely factor is when I type Me.Combo11.Text, it doesn't actually display Combo11 as an option. I tried typing it in, in hopes of working, but I know that is a bit foolish.

Any detailed answers would be appreciated. I'm still learning my way around and I get lost a bit easily.

Steve.

Yes, I see that now. Do you have a suggestion on how I can make this work? For that matter, is it an event on the embedded query that I am applying the event to, or is it to one of the fields in the query (i.e. the person field) or somewhere else, for that matter? — Steven Kanberg
Wow. I must have done a terrible job at explaining myself. Here it is in short: I want to select a name from the combo box, pass that name into the query, and the query then list only the tasks that belong to that name. How can I do that? I'll provide any details you ask for. — Steven Kanberg
What is the record source for your combo box? how many columns does it have? which column is the bound column? When you say your first attempt "didn't work" what does that mean? what result did you get vs the result you expected? did it produce an error? Can you show the SQL for your query that didn't work, and the SQL for the recordsource of the combo box (if available) along with indicating which column is the bound column? — Gene

Andre Andre · Accepted Answer · 2015-09-02T14:40:00

The first method is the easier one.
In the query you have

WHERE TaskPerson = Forms![Task Form]![Combo11]

Note that there are no ' around the combo reference. With 'Forms![Task Form]![Combo11]' the whole thing is interpreted as string, so it doesn't work.

Then in Combo11_AfterUpdate you simply have

Me.Requery

Disadvantage of this method: you always have to select a person, or the form will be empty.

The second method:

Your query lists all record, the combobox applies a filter. Or removes it, if the user clears the combobox.

I suggest going back to the answer you used, and only replace
Combo_Reported_LOB_Selection by Combo11
and
[ReportedLOB] by [TaskPerson]

And the code doesn't go into Form_SelectionChange(), but into Combo11_AfterUpdate()

Access 2013 - Embedded query filtered by combo box on form

1 Answers