Access filters in the listbox or subform

[brunotamietto]

Hello Everyone,




I would like your help, because I have tried to solve this problem and I haven't found it in any forum or video.

When you use the text box to search the list box, there is a possibility to use the various combo box (Dropdown List), such as:

• There is a list with the data of a Consultation;
• I would like to create 3 combo boxes in which, according to what the user chooses, the list data will be filtered;
• This list will have 6 columns in which the boxes will be: User; Status; Email
• The last list that will appear in email, I would like to get all this email information and prepare to send it through outlook, but don't send, just prepare I would like to know if there is a possibility to do this or if we should use a subform to create this routine.

I would like your help, because I have tried to solve this problem and I haven't found it in any forum or video.

[Ajax]

from what you are describing, it is a common question so surprised you have not found anything.

typically you would use a continuous form, with the combos in the header section

the problem is how the combos work together - and you have not really provided any information as to how that might be.

Strongly recommend you provide some example data, and how that data reduces by selecting values in the combos.

[brunotamietto]

Ajax, Thank for answering!

I did this in one form and worked with one seach:

C1-Non sensitive

---

Private Sub StartList()

If Me.TxtNumAVD <> "" Then

Me.ListDebit.RowSource = " SELECT * FROM QRY_DEB WHERE TEST LIKE '*" & Me.TxtDeb & "*'"

Else

Me.ListDebit.RowSource = " SELECT * FROM QRY_DEB"

End If
End Sub

But I want put one combox and make a filter like this: Me.ListDebit.RowSource = " SELECT * FROM QRY_DEB WHERE TEST LIKE '*" & Me.TxtDeb & "*'" + Me.Combobox
I tried you ",", "+" and & and nothing works.

My ideia is be like:

Filter : Country = USA Filter: State= NY

And all data show only USA and NY

And the next Form is to call the data from a query where have the these follow collums:

User, Status, Email (client)

I want to make a Listbox or a subform to show all data, and the filter the user and show only one user

Next filter choose the status and show all status to be treated from that user and de last the client and show the e;mail of the client.

that final information shows in the listbox or subform and I need to prepare a e-mail (Outlook), with the information of e-mail from the listbox or subform.

[Ajax]

repeat: Strongly recommend you provide some example data, and how that data reduces by selecting values in the combos.

this means several rows of data and several examples of what you mean

what you are now describing is what is called cascading combos - choose a country, then choose states/regions/whatever for that country

[brunotamietto]

Thanks, you are right , is cascading combos , I saw some videos now, but I need to show the results in a listbox in down (like the image).

I didnt start to write the code, I can show this image that what I am looking.. like the

repeat: Strongly recommend you provide some example data, and how that data reduces by selecting values in the combos.

this means several rows of data and several examples of what you mean

what you are now describing is what is called cascading combos - choose a country, then choose states/regions/whatever for that country

[Ajax]

lost me - I see no need for cascading combos - at least in the order you are showing. I see no state? How are user/country and client related?

you've provided a very small amount of data but not how that data reduces by selecting values in the combos.

I sort of understand what you want, but there are so many ways of doing this, I don't have time to go through them all.

[Micron]

If that's really a listbox, what you need to research is how to dynamically set the listbox rowsource based on a sql statement, or how to do that with a parameter query. A parameter query gets criteria for its fields based on either a) user input or b) from form controls. You would go with the form controls method if you choose the query approach.

Another method involves writing code for a button click event to set the listbox rowsource when you've finished choosing combo options.

[Ajax]

to combine two or more criteria your sql should look something like this

"SELECT * FROM QRY_DEB WHERE TEST LIKE '*" & Me.TxtDeb & "*' AND anotherfield like '*" & me.anothercombo & "*'"

but perhaps the AND should be an OR? Without a clear explanation of what you are trying to do, code that does not match up with your data etc, it is anyone's guess

and it is very unusual to use Like when you are looking for specific values such as a country or state - normally you would use =

suggest write a query that gets you what you want, then look at the sql to see how it is constructed.

[brunotamietto]

https://drive.google.com/drive/folde...gH?usp=sharing

to combine two or more criteria your sql should look something like this

"SELECT * FROM QRY_DEB WHERE TEST LIKE '*" & Me.TxtDeb & "*' AND anotherfield like '*" & me.anothercombo & "*'"

but perhaps the AND should be an OR? Without a clear explanation of what you are trying to do, code that does not match up with your data etc, it is anyone's guess

and it is very unusual to use Like when you are looking for specific values such as a country or state - normally you would use =

suggest write a query that gets you what you want, then look at the sql to see how it is constructed.

[pbaldy]

Post 9 was moderated, I'm posting to trigger email notifications.

[brunotamietto]

I didnt used code, just rowsource of the listbox:

I did this and worked with one combo:

SELECT Database.Name, Database.Country, Database.City FROM [Database] WHERE (((Database.Name)=[forms]![Form1].[OptionName]));

But I tried put the second and doesnt worked, what[´s wrong?

SELECT Database.Name, Database.Country, Database.City FROM [Database] WHERE (((Database.Name)=[forms]![Form1].[OptionName]))AND (((Database.Country)=[forms]![Form1].
[ListPais]));

[Ajax]

what does doesn't work mean?

what values were in the two form controls?

what record(s) did you expect to be returned?

Note that Database and Name are reserved words, using them as field or table names can cause unexpected and misleading errors

[Micron]

First, compact/repair then zip your db and post here. Usually I won't download from a cloud drive and some here never will.
Second, your db has a LOT of issues, some already mentioned, such as bad names for objects. It's not even close to being normalized.
Third, your db makes no sense. Why would you have controls at the top do do filtering and then show the same fields in a data sheet below? It's pointless. OK perhaps you just want an example of how to do cascading combos.

In that case, use all combos, not some combos and some listboxes or some combos and some textboxes.
Also, you should start with a combo that will contain the most list items and work down from there. I chose Client to be first because (for example) there can be many Facebook sites all over the world. Note the use of the DISTINCT predicate in the rowsource. You'd narrow down what follows by choosing a country because there would be fewer sites in a country than there are all over the world. Then you need to look for all the names that belong to US Facebook sites - not that that makes sense (you could have a John Smith at sites in different states or even cities).

So I have made the first 3 combos cascading and show what you might want to do when the user changes a choice in a combo that comes before any others that have already been selected. Check the after update event on the first combo and see that it removes the values from the combos that come after it, plus it requeries them. That is because if the first one changes, the ones that follow will be out of sync. You would need to do this on combo 2 to change 3. If you had combo 4, you would need to this on combo 2 to change 3 AND 4, and on 3 to change 4 and so on. I leave the rest to you.
Help.zip

[brunotamietto]

Thank you vary much Micron!!

[brunotamietto]

@Micron,

this code is just to leave all empty?

Private Sub Combinação14_Click()
With Me
.Lista8 = ""
.OptionName = ""
.Lista8.Requery
.OptionName.Requery
End With
End Sub