Requery Listbox with the following code

[UTLee]

I have limited knowledge on programming Access. I have the following code that populates an access report (rptAllocation) for each client that I select in a list, this works fine:

Private Sub cmdAllocationMonthlyReview_Click()
    Dim i, s As Integer
    Dim strfilter As String
    For i = 0 To Me.List135.ListCount
        If Me.List135.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "Client = '" & Me.List135.Column(1, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "Client = '" & Me.List135.Column(1, i) & "'"
            End If
        End If
    Next
    If s = 0 Then Exit Sub
    If Len(strfilter) > 255 Then
        MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
        Exit Sub
    End If
    
    DoCmd.OpenReport "rptAllocation", acViewPreview, "qryAllocation", strfilter
End Sub

I want to modify this code to requery a separate list box, List6, based on the client I have selected in List135. I'm assuming I need to change:

DoCmd.OpenReport "rptAllocation", acViewPreview, "qryAllocation", strfilter

to perform the requery but I don't know how to reference List6 and include the "strfilter". Any ideas?

[ItsMe]

You can requery a listbox control using the Requery method.
Me.List6.Requery

However, it sounds as though you want to change the RowSource property of List6.

Perhaps you can add your strfilter string to an SQL string and use the result as the RowSource for List6

[UTLee]

Thanks...so, how exactly would I do that? Currently, List6 rowsource is:

SELECT tblMonthlyReview.Manager AS Mgr, tblMonthlyReview.client, tblMonthlyReview.EquityTarget AS Trgt, tblMonthlyReview.PortfolioModel AS Model, tblMonthlyReview.account, tblMonthlyReview.accountID, tblMonthlyReview.CashReview
FROM tblMonthlyReview;

[ItsMe]

Maybe
dim strSQL = "SELECT tblMonthlyReview.Manager AS Mgr, tblMonthlyReview.client, tblMonthlyReview.EquityTarget AS Trgt, tblMonthlyReview.PortfolioModel AS Model, tblMonthlyReview.account, tblMonthlyReview.accountID, tblMonthlyReview.CashReview
FROM tblMonthlyReview"

strSQL = strSQL & " WHERE " & strfilter
dubug.print strSQL

Notice that I removed the semicolon from the original SQL before I concatenated strfilter to it.

[UTLee]

My apologies but I still don't understand. Where do I add the code you suggested above?

[ItsMe]

You can put that code behind the click event of a Command Button, like your other code that is behind a button. View the result from the Debug.Print line of code in the immediate window (keyboard shortcut to Immediate Window Ctrl + G).

[UTLee]

So I created a new button, and added the code you suggested to the On Click event of the button but the code doesn't work. This part shows up in red:

dim strSQL = "SELECT tblMonthlyReview.Manager AS Mgr, tblMonthlyReview.client, tblMonthlyReview.EquityTarget AS Trgt, tblMonthlyReview.PortfolioModel AS Model, tblMonthlyReview.account, tblMonthlyReview.accountID, tblMonthlyReview.CashReview
FROM tblMonthlyReview"

[ItsMe]

Sorry - VBA syntax error on my part. Try...

'Previous code to iterate first listbox
    Dim i, s As Integer
    Dim strfilter As String
    For i = 0 To Me.List135.ListCount
        If Me.List135.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "Client = '" & Me.List135.Column(1, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "Client = '" & Me.List135.Column(1, i) & "'"
            End If
        End If
    Next
    If s = 0 Then Exit Sub
    If Len(strfilter) > 255 Then
        MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
        Exit Sub
    End If
 
'new code for the second listbox RowSource
Dim strSQL As String
strSQL = "SELECT tblMonthlyReview.Manager AS Mgr, tblMonthlyReview.client, tblMonthlyReview.EquityTarget AS Trgt, tblMonthlyReview.PortfolioModel AS Model, tblMonthlyReview.account, tblMonthlyReview.accountID, tblMonthlyReview.CashReview FROM tblMonthlyReview"
strSQL = strSQL & " WHERE " & strfilter
Debug.Print "strfilter = " & strfilter & vbCrLf
Debug.Print "strSQL = " & vbCrLf & strSQL
''This will be for the requery method after testing
'Me.LISTBOXNAME.RowSource = strSQL

[UTLee]

Amazing, it works perfectly! This has made my Form so much more useful! Thanks so much for your patience!

[ItsMe]

Not a problem and I apologize for not answering your question earlier. The forum filters caused me to miss your reply. Glad to hear you have things sorted out.