Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11

    Access filters in the listbox or subform

    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.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,400
    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.

  3. #3
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    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.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,400
    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

  5. #5
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    Click image for larger version. 

Name:	help.png 
Views:	24 
Size:	15.8 KB 
ID:	43518
    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





    Quote Originally Posted by Ajax View Post
    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

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,400
    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.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,400
    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.



  9. #9
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11

    Somenone can help me?

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


    Quote Originally Posted by Ajax View Post
    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.



  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 9 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    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]));



  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,400
    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

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    Thank you vary much Micron!!

  15. #15
    brunotamietto is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    11
    @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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 04-21-2017, 08:23 AM
  2. Apply multiple filters to subform
    By Elwood07 in forum Forms
    Replies: 10
    Last Post: 07-06-2015, 12:17 AM
  3. Listbox filters
    By cbende2 in forum Programming
    Replies: 4
    Last Post: 05-24-2015, 02:02 PM
  4. Replies: 1
    Last Post: 01-31-2015, 09:03 PM
  5. Subform with multi filters
    By Aragon.2009 in forum Forms
    Replies: 0
    Last Post: 08-27-2010, 03:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums