Multi list box selection to run query

[aellistechsupport]

Hello,
How can you set it so that the user starts entering the city and it comes up to the close match?
It's take awhile to scroll through it. it only works for the 1st letter.

Also, I am using this

Option Compare Database

Private Sub NumberOfEmailsOnFile_Click()
On Error GoTo Err_NumberOfEmailsOnFile_Click
    Dim MyDB As Database
    Dim qdf As QueryDef
    Dim i As Integer, strSQL As String
    Dim strWhere As String, strIN As String
    Dim flgAll As Boolean
    
    Set MyDB = CurrentDb()
    
    strSQL = "SELECT * FROM CityState"
    
    'create the IN string by looping thru the listbox
    For i = 0 To CityStateSelector.ListCount - 1
        If CityStateSelector.Selected(i) Then
            If CityStateSelector.Column(0, i) = " All" Then
                flgAll = True
            End If
            strIN = strIN & "'" & CityStateSelector.Column(0, i) & "',"
        End If
     Next i
     
    'create the WHERE string, stripping off the last comma of the IN string
    strWhere = " WHERE [City,State]in (" & Left(strIN, Len(strIN) - 1) & ")"
    
    'if "All" was selected, don't add the WHERE condition
    If Not flgAll Then
        strSQL = strSQL & strWhere
    End If
    
    MyDB.QueryDefs.Delete "City-State"
    Set qdf = MyDB.CreateQueryDef("City-State", strSQL)
    
    OpenQuery.NumberOfEmailsOnFile
    
Exit_NumberOfEmailsOnFile_Click:
    Exit Sub
    
Err_NumberOfEmailsOnFile_Click:
    If Err.Number = 3265 Then   '*** if the error is the query is missing
        Resume Next             '*** then skip the delete line and resume on the next line
    ElseIf Err.Number = 5 Then
        MsgBox "You must make at least one selection"
        Resume Exit_NumberOfEmailsOnFile_Click
    Else
        MsgBox Err.Description      '*** write out the error and exit the sub
        Resume Exit_NumberOfEmailsOnFile_Click
    End If

End Sub

Not sure how to implement the "All" selection?

And I am getting an Object required prompt when i click on the button after making my selection(s) from the List Box.

[June7]

Listbox does match on only first letter. That's they way it is.

Disable the error handler so the debugger will go to the line that triggers error.

I don't think OpenQuery as you have is valid syntax.

DoCmd.OpenQuery "NumberOfEmailsOnFile"

Why do you need to open query?

[pbaldy]

On what line (temporarily comment out the "On error..." line if you don't know)? The All is handled here:

    'if "All" was selected, don't add the WHERE condition
    If Not flgAll Then
        strSQL = strSQL & strWhere
    End If

I don't think this line is valid:

OpenQuery

It would be

DoCmd.OpenQuery

[aellistechsupport]

On what line (temporarily comment out the "On error..." line if you don't know)? The All is handled here:

    'if "All" was selected, don't add the WHERE condition
    If Not flgAll Then
        strSQL = strSQL & strWhere
    End If

I don't think this line is valid:

OpenQuery

It would be

DoCmd.OpenQuery

Ah, right on the

Do.Cmd.OpenQuery.

On the "All", yes, but how do I show that option in the List Box?

[aellistechsupport]

Listbox does match on only first letter. That's they way it is.

Disable the error handler so the debugger will go to the line that triggers error.

I don't think OpenQuery as you have is valid syntax.

DoCmd.OpenQuery "NumberOfEmailsOnFile"

Why do you need to open query?

What do you suggest for multiple selections from a long list? Scrolling through even after getting to the first letter is cumbersome.


Ah, yes on the DoCmd.
Need to see the results from the selections made on Form

thank you

[June7]

I've never needed multi-select listbox.

Aren't your items sorted alphabetically? How long is this list?

Use a UNION query to include a non-table value. https://www.accessforums.net/access/...box-36116.html

Users should not work directly with tables and queries, only forms and reports.

[aellistechsupport]

I've never needed multi-select listbox.

Aren't your items sorted alphabetically? How long is this list?

Use a UNION query to include a non-table value. https://www.accessforums.net/access/...box-36116.html

Users should not work directly with tables and queries, only forms and reports.

Yes, but it's long. It lists ALL cities and state combination.

Ok, thanks on the UNION query.

[June7]

You might find cascading (dependent) comboboxes useful.

[aellistechsupport]

Ok, I have tried working with the cascade option and going to limit it to at State level and want the list box to show all the Cities based on the State selected from the combo box.

Running into an issue.

Please see attached.Memberships.zip

Not sure how to make it work properly.

[June7]

RowSource for the Cities listbox:

SELECT qryCitiesAndStates.City, qryCitiesAndStates.State, qryCitiesAndStates.StateCode FROM qryCitiesAndStates WHERE (((qryCitiesAndStates.[StateCode])=[cboStateCode])) ORDER BY qryCitiesAndStates.[City], qryCitiesAndStates.[State];

I had to delete the listbox and rebuild. I have run into this before. No idea why but the dependent combobox or listbox becomes corrupted and just won't work no matter how much I edit its properties.

Be aware the combobox AfterUpdate event only triggers when the box loses focus - so must hit Tab or Enter, not just select item from list.

I removed all the code to do test and used only the listbox GotFocus event for the requery.

Set the state combobox to null after requerying the listbox but I wouldn't bother.

Then no need to build the SQL with VBA. However, if you want to build with VBA, filter parameters for text fields need apostrophe delimiters.

strRS = strRS & " WHERE StateCode = '"& Me.cboStateCode & "'"

[aellistechsupport]

Humh, still not working.

updated code

Private Sub FilterCitiesList()
  
  Dim strRS As String
  
  ' Filter the list box appropriately based on the combo box selection(s)
  strRS = "SELECT qryCitiesAndStates.City, qryCitiesAndStates.State, qryCitiesAndStates.StateCode FROM qryCitiesAndStates WHERE (((qryCitiesAndStates.[StateCode])=[cboStateCode])) ORDER BY qryCitiesAndStates.[State], qryCitiesAndStates.[City];"

' If Not IsNull(Me.cboCity) Then
'    strRS = strRS & " WHERE StateCode = " & Me.cboCity
 If Not IsNull(Me.cboStateCode) Then
        strRS = strRS & " WHERE StateCode = " & Me.cboStateCode & ""
 End If
  
  strRS = strRS & " ORDER BY qryCitiesAndState.State, qryCitiesAndState.City;"
 
  Me.lstCities.RowSource = strRS
  
  Me.lstCities.Requery
  
End Sub

[aellistechsupport]

Option Compare Database
Option Explicit

Private Sub cboStateCode_AfterUpdate()

  ' Filter the list of Cities based on the selected State.
  ' Set the City combo box to be limited by the selected State
  Me.cboCity.RowSource = "SELECT Cities.StateCode, Cities.City FROM Cities " & _
     " WHERE StateCode = " & Nz(Me.cboStateCode) & _
     " ORDER BY City"
  Me.cboStateCode = Null
  
  FilterCitiesList
  
End Sub

Private Sub FilterCitiesList()
  
  Dim strRS As String
  
  ' Filter the list box appropriately based on the combo box selection(s)
  strRS = "SELECT qryCitiesAndStates.City, qryCitiesAndStates.State, qryCitiesAndStates.StateCode FROM qryCitiesAndStates"

' If Not IsNull(Me.cboCity) Then
'    strRS = strRS & " WHERE StateCode = " & Me.cboCity
 If Not IsNull(Me.cboStateCode) Then
        strRS = strRS & " WHERE StateCode = " & Me.cboStateCode & ""
 End If
  
  strRS = strRS & " ORDER BY qryCitiesAndStates.State, qryCitiesAndStates.City;"
 
  Me.lstCities.RowSource = strRS
  
  Me.lstCities.Requery
  
End Sub


Private Sub Form_Load()
  ' When the form loads, enable/disable the combo boxes. Combo boxes are only enabled if the preceeding combo box has a value.
'  EnableControls
  ' Show all Cities in the list until filters are selected from the State combo box.
  FilterCitiesList
  
End Sub

[June7]

Why don't you do the same steps I did? Do on a test on copy of your db. When you get that working then build code from there.

[aellistechsupport]

I tried those steps and copied and pasted what you provided.

There were some other issues

this is the code now

Option Compare Database
Option Explicit

Private Sub cboStateCode_AfterUpdate()
  ' Filter the list of Cities based on the selected State.
 
  FilterCitiesList
  
End Sub

Private Sub FilterCitiesList()
  
  Dim strRS As String
  
  ' Filter the list box based on the State selected
  strRS = "SELECT qryCitiesAndStates.City, qryCitiesAndStates.State, qryCitiesAndStates.StateCode FROM qryCitiesAndStates"
  strRS = strRS & " WHERE StateCode = " & Me.cboStateCode
  strRS = strRS & " ORDER BY qryCitiesAndStates.City;"
 
  Me.lstCities.RowSource = strRS
  
  Me.lstCities.Requery
  
End Sub

Private Sub Form_Load()
  ' Show all Cities in the list until filters are selected from the State combo box.
  
  FilterCitiesList
  
End Sub

but for some reason I am getting a Parameter Prompt for the StateCode FL when I select Florida??

[June7]

That code doesn't look like my suggestion. Did the simpler structure work?

As noted in post 10, text field filter parameters need apostrophe delimiters:

strRS = strRS & " WHERE StateCode = '" & Me.cboStateCode & "'"