Run query based on multiselect list box 1

[EDGE99]

I have a form in which I have a multiselect list box that contains a list of customer names. I am want to be able to select multiple customers and press a command button to run the query and pass the list box items along with it.

I found this bit of code and tried to make it work but I cannot get it to work. Anyone have any ideas on how to get this to work?

Private Sub Command2_Click()

Dim sWhere As String ' Where condition
Dim lst As ListBox ' multiselect list box
Dim vItem As Variant ' items in listbox
Dim iLen As Integer ' length of string.

'Set lst = Me!lstSource
Set lst = Me!List0
'loop through all items in listbox
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
sWhere = sWhere & "(custNAME = """ & lst.ItemData(vItem) & """) OR"

End If
Next

iLen = Len(sWhere) - 4 ' Without trailing " OR ".
If iLen = 0 Then
sWhere = "(" & Left$(sWhere, iLen) & ")"
End If
'DoCmd.OpenReport "MyReport", acViewPreview, , sWhere
DoCmd.OpenQuery "qry_GetRevenue (2/2)"
Set lst = Nothing
End Sub

 

[TheAceMan1]

How are ya ScottG9905 . . .

Not sure what you want here so question . . .

Is the query used as the [blue]RecordSource[/blue] of the report and is it your intent to [blue]use the names in the listbox as criteria for that query?[/blue]

In any case here's corrected code:

[blue]   Dim Cri As String, lst As ListBox, DQ As String, itm
   
   Set lst = Me!List0
   DQ = """"
   
   For Each itm In lst.ItemsSelected
      If Cri <> "" Then
         Cri = Cri & " OR (custNAME = " & DQ & lst.ItemData(itm) & DQ & ")"
      Else
         Cri = "(custNAME = " & DQ & lst.ItemData(itm) & DQ & ")"
      End If
   Next
   
   [green]'DoCmd.OpenReport "MyReport", acViewPreview, , sWhere[/green]
   DoCmd.OpenQuery "qry_GetRevenue (2/2)"
   
   Set lst = Nothing[/blue]

See Ya! . . . . . .

 

[EDGE99]

Hey Ace,

Thanks for the reply. I have to run this particular query often and always have request for a different subset of customers each time. So I want to be able to choose 1 or N customer names from the list box and then run the query where custNAME = listbox selection.

But I am not sure if you can pass the list box output to an existing query.

I hope this all makes sense.

 

[TheAceMan1]

Roger That ScottG9905 . . .

The idea I have is too [blue]add a checkbox[/blue] in the underlying table of the query that gets updated by the listbox. Then its a simple matter of [blue]adding criteria to only show records where the checkbox is true![/blue] This also allows for [blue]large selection counts[/blue] without the SQL of the query becoming unwieldly or a flter string that stretches around the block.

[blue]Post the SQL of the query so we can continue . . .[/blue]

See Ya! . . . . . .

 

[PHV]

In your original post:
iLen = Len(sWhere) - [!]3[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EDGE99]

Ace,

Here is the SQL you requested:

SELECT [qry_GetRevenue (1/2)].custNAME, [qry_GetRevenue (1/2)].productID, Sum([qry_GetRevenue (1/2)].QTY) AS SumOfQTY, Sum([qry_GetRevenue (1/2)].WPP) AS SumOfWPP, Sum([qry_GetRevenue (1/2)].NET) AS SumOfNET, Sum([qry_GetRevenue (1/2)].COST) AS SumOfCOST, Sum([qry_GetRevenue (1/2)].[Ext COST]) AS [SumOfExt COST], Sum([qry_GetRevenue (1/2)].DISCOUNT) AS SumOfDISCOUNT, Avg([qry_GetRevenue (1/2)].MARGIN) AS AvgOfMARGIN
FROM [qry_GetRevenue (1/2)]
GROUP BY [qry_GetRevenue (1/2)].custNAME, [qry_GetRevenue (1/2)].productID;

 

[EDGE99]

Ace,

Do you need any additional information?

 

[TheAceMan1]

ScottG9905 . . .

Info is fine . . . but before we continue you need to check out [blue]PHV's[/blue] suggestion. If it doesn't fix it . . . it will alter my post!

See Ya! . . . . . .

 

[EDGE99]

Ace,

This doesn't help with the problem I am having. The problem I am having is that OpenQuery command doesn't allow me to pass in my criteria.

 

[TheAceMan1]

ScottG9905 . . .

Sorry for the delay . . . big errand!

Anyway . . . believing your query or SQL resides in the [blue]RecordSource[/blue] property of the report, try this:

[blue]   Dim LBx As ListBox, Cri As String, DQ As String, itm

   Set LBx = Me!List0
   DQ = """"
   
   If LBx.ItemsSelected.Count > 0 Then
      For Each itm In LBx.ItemsSelected
         If Cri <> "" Then
            Cri = Cri & ", " & DQ & LBx.Column(1, itm) & DQ
         Else
            Cri = DQ & LBx.Column(1, itm) & DQ
         End If
      Next
      
      Cri = "custNAME In(" & Cri & ")"
      Debug.Print Cri
   End If
   
   DoCmd.OpenReport "rptNames", acViewPreview, , Cri
   
   Set LBx = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[EDGE99]

Ace,

No problem. I appreciate all of the help that you have provided.

I actually got this working earlier today. I used my original code and added the "ApplyFilter" command.

Dim sWhere As String ' Where condition
Dim lst As ListBox ' multiselect list box
Dim vItem As Variant ' items in listbox
Dim iLen As Integer ' length of string.

Set lst = Me!List0

'loop through all items in listbox
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
    sWhere = sWhere & "(custNAME = """ & lst.ItemData(vItem) & """) OR "
End If
Next

iLen = Len(sWhere) - 4 ' Without trailing " OR ".
If iLen = 0 Then
  sWhere = "(" & Left$(sWhere, iLen) & ")"
End If

' remove the trailing OR
sWhere = Left(sWhere, Len(sWhere) - 3)

DoCmd.OpenQuery "qry_GetRevenue2"
DoCmd.ApplyFilter , sWhere

Set lst = Nothing

Now my query runs when I select one or many items out of the listbox.