Performing multiples searches on a list box while retaining the original search (1 Viewer)

Finance

Registered User.
Local time
Yesterday, 19:39
Joined
Jul 11, 2018
Messages
59
Hi,

I have created a Text box to search and select the contents of my list box.
I will be searching for multiple costs and would like to save the original selection when the search column goes blank.

Eg: if i type china in the text box, the list box should list everything with china in it and then if i erase china and type australia, it should retain the selection i made when i typed china and allow for an additional selection when i type australia.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:39
Joined
Aug 30, 2003
Messages
36,118
You want to select items in the listbox, or affect which items are displayed (change the row source)? What's your code now? I'd envision using a form level variable to track previous input.
 

Finance

Registered User.
Local time
Yesterday, 19:39
Joined
Jul 11, 2018
Messages
59
The list box having nature of fees is dependent on a selection from the list box country.
The row source of the list box ‘nature of fees’ is a table.
When I select a country from that table the list box for nature of fees is populated based on that country selection. I want the search box to allow me to further select from the filtered list, and to keep the previous selection if I decide to add to the search criteria. How do you use a form level variable to track a previous input?
 
Last edited:

Finance

Registered User.
Local time
Yesterday, 19:39
Joined
Jul 11, 2018
Messages
59
You want to select items in the listbox, or affect which items are displayed (change the row source)? What's your code now? I'd envision using a form level variable to track previous input.
Following is the code I have used to sort the nature of fees list box based on the country selection.
Code:
Private Sub ListCountrySE_AfterUpdate()
    Dim varItem As Variant
    Dim strSQL As String
        
       If Me.ListCountrySE.ItemsSelected.Count Then
        strSQL = "Select ID_Number, Nature_of_Fees from TotalCostsSmallEntity where [Country] in ('"
        For Each varItem In Me.ListCountrySE.ItemsSelected
            strSQL = strSQL & Me.ListCountrySE.ItemData(varItem) & "','"
        Next varItem
        strSQL = Left(strSQL, Len(strSQL) - 2) & ") ORDER BY ID_Number"
    Else
        strSQL = "Select ID_Number, Nature_of_Fees from TotalCostsSmallEntity ORDER BY ID_Number"
    End if 
Me.ListNOFSE.RowSource = strSQL
End Sub

I have attached a copy of the database as well
 

Attachments

  • Forecasting Tool - Database.zip
    542.6 KB · Views: 41

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:39
Joined
Aug 30, 2003
Messages
36,118
Well, there is no textbox to further filter the result. You can add a criteria based on a textbox to the criteria applied by the listbox. Instead of having the For Each code add to the full SQL, just have it create a WHERE clause and then add the textbox to it.
 

Finance

Registered User.
Local time
Yesterday, 19:39
Joined
Jul 11, 2018
Messages
59
Well, there is no textbox to further filter the result. You can add a criteria based on a textbox to the criteria applied by the listbox. Instead of having the For Each code add to the full SQL, just have it create a WHERE clause and then add the textbox to it.

Could you please give me an example ? I don’t understand how to create the Where clause and add a textbox
 

Finance

Registered User.
Local time
Yesterday, 19:39
Joined
Jul 11, 2018
Messages
59
And how do I keep all selections highlighted during one use of a form and not the most recent selections?
 

Users who are viewing this thread

Top Bottom