Select from two list boxes

[valkyry]

Hi,
I can't figure out how to edit this module to process from two separate list boxes.

This module runs from selection(s) from a single list box.
I also need it to process selection(s) from another list box named "RepSelector" from the query "SalesRep"

Can someone help me?


Here's the working module for one list box.

Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_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 AR1_CustomerMaster"

'create the IN string by looping thru the listbox
For i = 0 To TerritorySelector.ListCount - 1
If TerritorySelector.Selected(i) Then
If TerritorySelector.Column(0, i) = " All" Then
flgAll = True
End If
strIN = strIN & "'" & TerritorySelector.Column(0, i) & "',"
End If
Next i

'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [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 "Territory"
Set qdf = MyDB.CreateQueryDef("Territory", strSQL)

DoCmd.RunMacro "Process"

Exit_cmdRunReport_Click:
Exit Sub

Err_cmdRunReport_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_cmdRunReport_Click
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunReport_Click
End If

End Sub

 

[TheAceMan1]

Roger That valkyry . . .

Believing you made the proper substitions of Listbox Names . . . did the additional line in [purple]purple[/purple] of . . .

[blue][purple][b]On Error Resume Next[/b][/purple]
   DB.QueryDefs.Delete "Territory"
   Set qdf = DB.CreateQueryDef("Territory", SQL)
   
   DoCmd.RunMacro "Process"

End Sub[/blue]

. . . work?

See Ya! . . . . . .

 

[valkyry]

yes, this seemed to work, expect now it's not doing anything with the Rep data.

thanks again!

 

[TheAceMan1]

valkyry . . .

Are you talking the other listbox RepSelector?

Be aware . . . [blue]the code is dependent on data of interest residing in the first column (column 0) of both listboxes.[/blue] If this is not the case then the code has to be modified!

See Ya! . . . . . .

 

[valkyry]

Sorry for any confusion but when listing the post for help I thought i indicated that I need to make selection(s) from TWO list boxes for the result and that my current code is for only one.

Example:
Territory query will have two columns (list box)
All and All Territories
CA and California, etc ..

The SalesRep query will have two columns (list box)
All and All SalessReps
Rep1 and Rep1Name, etc ...


The current code only works with ONE of the list box (which ever I edit in the code.



Hope that clears it up!


original post starts:
"Hi,
I can't figure out how to edit this module to process from two separate list boxes.

This module runs from selection(s) from a single list box.
I also need it to process selection(s) from another list box named "RepSelector" from the query "SalesRep""

 

[TheAceMan1]

valkyry . . .

Postback the columns of interest for each listbox ([blue]considering column index starts at zero![/blue] . . . (apparently the [blue]State[/blue] field! . . . in both . . .)

See Ya! . . . . . .

 

[valkyry]

??

I'm not sure what you're asking here.

Two list boxes.
1 is named Territory (which is a list of States) - TerritorySelector list box name
2 is named SalesRep (which is a list of Reps) - RepSelector list box name

 

[TheAceMan1]

valkyry . . .

What a difference a day makes!

It was my impression you were concatenating additional states from [blue]RepSelector[/blue] in you [blue]In[/blue] clause!

Are you saying your look for something like:

[blue]WHERE ([State ]IN (State1, State2, . . .) AND ([REP] IN (Rep1, Rep2, . . .)[/blue]

You never indicated what you wanted to do with [blue]RepSelector[/blue] except include it! . . .

See Ya! . . . . . .

 

[valkyry]

LOL!

Not sure. Big reason why I posted.

So in my example I have two list boxes with two columns each.

Territory will have two columns (list box)
All and All Territories
CA and California
NV and Nevada, etc ...

SalesRep will have two columns (list box)
All and All SalessReps
Rep1 and Rep1Name
Rep2 and Rep2Name


Say the user selects All from Territory and All from SalesRep, the result should be all of the states and all of the salesreps.

Or if they select one from each or two and so on ...

The result should be from each list box.

 

[TheAceMan1]

[blue]Not sure. Big reason why I posted.[/blue]

That leaves too many questions to ask.
[ol][li]Whats the [blue]Row Type[/blue] of the listboxes?
[ol a][li]If table or query are the [blue]RecordSource's[/blue] of the listboxes based on the same table [blue]AR1_CustomerMaster[/blue]?[/li][/ol][/li]
[li]Do you want:

[blue]WHERE ([State ]IN (State1, State2, . . .) [purple][b]AND[/b][/purple] ([REP] IN (Rep1, Rep2, . . .)[/blue]

or:

[blue]WHERE ([State ]IN (State1, State2, . . .) [purple][b]OR[/b][/purple] ([REP] IN (Rep1, Rep2, . . .)[/blue]

[/li][/ol]
In either case there's enough example in this thread for you too piece together what you want when you decide what that is! . . .

See Ya! . . . . . .

 

[valkyry]

I belive it's the AND

 

[TheAceMan1]

valkyry . . .

[blue] . . . and have you tried anything?[/blue]

See Ya! . . . . . .