update list box after main form is filtered

[Slush]

Hello,



I have a continuous form which displays patient information. The form contains a list box with the names of all patients. Users can click on a name in the list box and the selected patient's information will be displayed on the form. The form and the list box are based on the same query. When users apply filters to the form (using the Access sort and filter tools in the toolbar) how do I update the list box so only patients meeting the new criteria are displayed in the list box?

Basically, users open the form and see all patients. Then they filter the form to only see patients with a specific birth year or due date or sample, etc. I want the list box to update and only show the names of patients meeting the form's filter criteria.

How do I update the list box to display patients meeting the filter criteria?

[isladogs]

When the filter is applied, requery the listbox

Me.MyListboxName.Requery

[Slush]

I added this to the On Filter event of the form, but that does not apply the filter to the list box. It reruns the query, but with no filter. For example filtering by age >90, the list box still shows hundreds of patients, but the form only has 4 records.

[isladogs]

I added this to the On Filter event of the form, but that does not apply the filter to the list box. It reruns the query, but it with no filter.

That's not what I meant.
You use the OnFilter property together with filter on load to filter the records appearing on your form when it is opened

Here is a similar example
When I enter text in the textbox, code in the textbox after update event filters the listbox accordingly by changing the listbox row source



In this case I don't even need to requery ... though you MAY need to do so.
HTH

[Slush]

I appreciate your help, but I am confused. See image of form below. I need to filter the form after it loads based on any of the fields marked in blue. The unbound list box is on the left circled in red. I need the list box to reflect the patients meeting the filters applied after loading.

Are you saying I use the OnFilter event of the form? How? I tried adding the requery code, but it did not apply the form's new filter to the list box.
And I need to set the filter on load property of the form to yes. I tried doing this and requiring the list box in the onfilter event, but it did not work. Please clarify.

If I had a dedicated text box I understand how to apply the filter, but there are many fields that can be used to filter the form and I am using the Access filter tools. How do I pass these on-the-fly filters to the unbound list box?

Thanks for taking the time to explain this.

[isladogs]

Are you saying I use the OnFilter event of the form? How? I tried adding the requery code, but it did not apply the form's new filter to the list box.
And I need to set the filter on load property of the form to yes. I tried doing this and requiring the list box in the onfilter event, but it did not work. Please clarify.

No - perhaps I worded it badly
I was trying to explain that the purpose of the OnFilter event is to filter the data on the form as a whole.
That's irrelevant to your listbox which you want to filter depending on the data entered in your various textboxes

So for example, you need an After_Update event for the Sex field to filter the listbox for females only by altering its recordsource
Similarly for each of your other textboxes that can filter the listbox.

The next question is do you want to have just one filter or multiple filters e.g. female AND Black AND Study Status = 60
That can also be done but its getting more complicated
See posts 3 & 4 in this thread https://www.accessforums.net/showthread.php?t=69916

[Slush]

Several of the fields I want to filter on are read only, so would an after update event work? The user is just clicking in the field and selecting filter from the toolbar and entering the criteria. Data is not changing. Many of the fields are locked.

I am trying to keep this as simple as possible, so only filtering on one criteria.

Could I just change the list box row source to the main form record set when the filter is applied? Something along these lines https://www.experts-exchange.com/que...;s-filter.html

Using something like this:

If (Len(Me.Filter) > 0) And Me.FilterOn = True Then
Me!ListPatName.RowSource = "SELECT * FROM " & Me.RecordSource & " WHERE " & Me.Filter
Else
Me!ListPatName.RowSource = Me.RecordSource
End If

I tried variations of this code in the on load event and the on filter, but I could not get it to work. I tried also requiring the list box in the events, but no luck.

If I can't get the list box row source to update, could I make the list box blank if a filter is applied? It confuses users when they see patients listed who don't exist on the filtered form.

[isladogs]

Apply the filter on the locked fields from the start as these will always apply

Just use the After_Update event on the fields where the user can select using a combobox or by typing in a textbox

I keep saying the same point about Me.Filter & Me.FilterOn - these apply to the form recordsource when it is opened / loaded
The listbox record source is a separate filter of the form recordsource based on filtering via combobox or textbox

Say your unfiltered listbox has a row source

"SELECT * FROM MyQueryName;"

Then the After_Update event code for a combobox Me.cboGender might be something like this

Private Sub cboGender_AfterUpdate()

If Me.cboGender<>"" Then
      Me.ListPatName.RowSource = "SELECT * FROM MyQueryName WHERE Gender = '" & Me.cboGender & "';"
Else
      Me.ListPatName.RowSource = "SELECT * FROM MyQueryName;"
End If

End Sub

You will be able to update the listbox so your final question is immaterial

If you haven't yet done so, do look at the link in my last answer.
There are lots of other examples elsewhere on the forum - search for filter listbox or similar

[Slush]

I'm not getting it. I've read your posts and the links. I'm not a complete newbie, but I am not understanding your suggestions.

"Apply the filter on the locked fields from the start as these will always apply"
What does this mean? Write the code for all fields I want to filter on using the afterupdate event of each control?

"I keep saying the same point about Me.Filter & Me.FilterOn - these apply to the form recordsource when it is opened / loaded
The listbox record source is a separate filter of the form recordsource based on filtering via combobox or textbox"

I understand that the listbox source is separate from the form recordsource. What do you mean by "I keep saying the same point about Me.Filter & Me.FilterOn"?
You said "You use the OnFilter property together with filter on load to filter the records appearing on your form when it is opened", but when I set the form property to "filter on load" = yes and the OnFilter event. It was applying a filter when the form loaded which is not what I want. I am confused about what you are trying to tell me about these two items.

Just to verify I understand using the AfterUpdate Event for this. No data is being changed. No combo box is used. Users just right click in the field they want to filter on and using the built in Access filtering options. If the user right clicks in the due date field, they select "date filters" and then equals, does not equal, etc. and enters the date they want to filter by. Nevertheless, use the AfterUpdate event to fire the filter?

How do I write the code like your example when I don't know if the user wants "between" or > or <, etc:
strWhere = " WHERE (([Incident Date] Between #01/21/2016# And #01/08/2018#) And [Incident Type] = 'TAWG' And Location = 'Cockfosters')"

I tried altering some of your other code, but I couldn't get it to work. Do I need the me.filter and me.filteron in the AfterUpdate Event code or in the apply filter or on filter events or in the form property and events or ?
Private Sub txtAge_AfterUpdate()

If Me.txtAge<>"" Then
Me.ListPatName.RowSource = "SELECT PatientName FROM q_Patient WHERE txtAge = '" & Me.txtAge & "';"
Else
Me.ListPatName.RowSource = "SELECT PAtientNAme FROM DueDate;"
End If

End Sub

I also tried the apply filter event with the following code

Dim strsource as string
Strsource = “select * from “ & me.recordsource & “ where “ & me.filter
Me.listpatname.rowsource = strsource




Me.listpatname = vbnullstring

[deepakg27]

waiting for the answer helps me also with my database.

[davegri]

I got the below to work, called from the form_current event. The red line will adapt the me.filter string to work as the criteria for the listbox 'lstRes' rowsource.
This works in conjunction with the filter funnel on the ribbon.
Adjust your data names as appropriate.

Sub subPopulateLstRes()
    Dim sSQL As String
    Select Case Me.FilterOn
        Case True
            sSQL = "Select LastName" & ", " & "FirstName from tblResidents Where " & Me.Filter
            sSQL = Replace(sSQL, "[" & Me.Name & "].", "")
        Case False
            sSQL = "Select LastName" & ", " & "FirstName from tblResidents"    'to show all
            'sSQL = ""                                                                   'to show none
    End Select
    lstRes.RowSource = sSQL
     'Debug.Print lstRes.RowSource
End Sub

[isladogs]

Hi

So you have two possible approaches to choose from, here is an example based on a school pastoral incident system - all data has been sanitised.
I've had to disable many features of the form so it works as a standalone form.

Hopefully the idea is clear & its not too complicated to understand how it works

Click any record in the list to view the record details
Filter the list using one or more of the combo boxes at the top right

It also includes a 'mouse move' feature where text explains different items as you move over them

Let me know if you want anything explained

[Slush]

Thank you both for your help!! I was able to get the code from DaveGri to work for most fields. I just have 2 remaining issues.

1. When I filter using the Study Status combo box, I get repeat pop-up message requesting a parameter value. The Study Status combo box has the first row (bound column) hidden, so users are filtering based on the second column (displayed). I'm not sure if that is causing the issue. The row source for the combo box is a query. What is the best way to handle this? Prior to applying the list box code it could be filtered.

2. I have command buttons on the form that filter the form by assigning new record and row sources to the main form and list box. In the code provided, if Case is False and the form has not been filtered I would like the list box row source to be the same as the form's record source (which could vary). If the form is not filtered by the user, is there a way to assign the list box row source to the main form record source (whatever that may be)? Something list lstRes.rowsource = me.recordsource?

[davegri]

1. What specific field is the pop-up asking for and what is the rowsource for the combobox? Probably some fieldname is wrong in that SQL and Access cannot identify it.
2.

Something list lstRes.rowsource = me.recordsource?

would work, except that the form_current event is overriding whatever those buttons do.
So we need to devise some code to avoid the form_curren call from happening when the buttons are clicked. Maybe a tempvar or a global variable could be set/reset as a switch. I don't have time to devise code
right now, but I can make an effort tomorrow, or someone else could jump in...

OK, back later.
More info would be helpful. Could you explain why modifying the recordsource would be desirable instead of filtering?

[Slush]

Your suggestions have been very helpful. I think I have the filter issue solved except for the combo box. Prior to writing code to update the list box after filtering it worked fine, but now I get the parameter error show below.

The combo box is named "ComboStudyStatus" and row source is the select query listed below. It just pulls all values from a look up table. The control source is Study Status, which is hidden, but is bound and the value entered is saved. Study Status Grp is what the users see and the value they filter on.