Passing Multiselect Listbox Values to a Query with Replace() doesn't Update Results on the Form-CodePudding

I have a continuous form with a multiselect listbox, called StatusListBox, and I pass all the selected values from the box to an SQL query ("FilterQuery") with a Replace function, changing the SQL code of the query. This works well and updates the text of the query and I can see the changes when I open it, but my continuous form doesn't get updated. I suspect the problem is that I change the text of the query's SQL code after the form has already loaded and not with proper parameters (i.e. Forms!FormName!Control)

I use Me.Requery to update the form, which works great with textfields which are parameters in the FilterQuery SQL code, but not in this case. I'm new to Access and I'm very puzzled. Here's the sub:

first I make a string with all the values from the textbox

StatusListValues = ""
For Each StatusItem In StatusLIst.ItemsSelected
StatusListValues = StatusListValues & "'" & StatusLIst.ItemData(StatusItem) & "'" & ","
Next StatusItem
StatusListValues = Left(StatusListValues, Len(StatusListValues) - 1)
Debug.Print StatusListValues

then I save the current SQL code as a string, replace the previous string of selected values with current string, then make current string the "previous" one

SQLstring = CurrentDb().QueryDefs("FilterQuery").SQL
SQLstring = Replace(SQLstring, StatusListValuesPrevious, StatusListValues)
CurrentDb.QueryDefs("FilterQuery").SQL = SQLstring
StatusListValuesPrevious = StatusListValues

CodePudding user response：

You should avoid editing the SQL string of an existing query. Instead, you should filter the form based on the selected items.

Create a helper function that returns the selected statuses (if any) if the form of 'a','b','c' etc.

Private Function SelectedStatus() As String
    Dim StatusListValues As String

    For Each StatusItem In StatusLIst.ItemsSelected
        StatusListValues = StatusListValues & "'" & StatusLIst.ItemData(StatusItem) & "'" & ","
    Next StatusItem

    SelectedStatus= Left(StatusListValues, Len(StatusListValues) - 1)

End Function

Then, on the AfterUpdate() event of the ListBox, get the selected items (if any) and either filter the form, or clear the filter if nothing has been selected.

Private Sub YourListBoxControl_AfterUpdate()

    Dim statuses As String
    statuses = SelectedStatus()

    'if no selection, clear the filter
    If statuses = vbNullString Then
        FilterOn = False
        Filter = vbNullString
        Exit Sub
    End if

    'selection made - apply filter
    Filter = "[Status] In (" & statuses & ")"
    FilterOn = True

End Sub