How to Build Two Queries From One List Box

[doyle60]

I have four list boxes on a dialog form that build four separate
queries. These queries are used to filter a chain of queries for a
report. The four queries are:


PrintDialogInventoryClassOfTradeLBqry
PrintDialogInventoryFranchiseLBqry
PrintDialogInventoryProfitCenterLBqry
PrintDialogInventoryPlantLBqry


Everything works fine. Except I noticed that the first filter
(PrintDialogInventoryClassOfTradeLBqry) must actually be placed later
in the chain for 3 of the 12 reports on this dialog form, and earlier
(where it is now) for 9 reports. The 12 reports are in an option group.
Not wanting to build another chain and not wanting to have two list
boxes on the form for the same list, I want to perform the task in
code.


So instead of building one query from the list box
(ClassOfTradeChosen), I want to build two: one with all chosen and one
filtered, or the reverse, one filtered and the other all, depending on
what report is chosen. It's a basic flip-flop.


In other words, I want something like this, (with the new query being
PrintDialogInventoryClassOfTradeLBqry2):


If ReportChosen = 1 through 9 Then
PrintDialogInventoryClassOfTradeLBqry: filter
PrintDialogInventoryClassOfTradeLBqry2: all records
Else
PrintDialogInventoryClassOfTradeLBqry: all records
PrintDialogInventoryClassOfTradeLBqry2: filter
End If


The full code that needs to be amended is below. It's only the first
part of 4 that needs the If-Else statement.

Thanks so much,

Matt

PS: By the way, just so one doesn't get confused, if nothing is chosen
in the list boxes, the code actually interprets this as all chosen.
This is how I want it. Thanks.


_____________________________________________


Private Sub Command19_Click()


Dim Q As QueryDef, db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant


'List Box 1: Class of Trade
Set ctl = Me![ClassOfTradeChosen]


For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm


Set db = CurrentDb()
Set Q = db.QueryDefs("PrintDialogInventoryClassOfTradeLBqry")


If Len(Criteria) = 0 Then
Q.SQL = "Select * From [ClassOfTradetbl]"
Else
Q.SQL = "Select * From [ClassOfTradetbl] Where [ClassOfTrade] In (" &
Criteria & ");"
End If


Set Q = Nothing
Criteria = ""


'List Box 2: Franchise
Set ctl = Me![FranchiseChosen]


For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm


Set db = CurrentDb()
Set Q = db.QueryDefs("PrintDialogInventoryFranchiseLBqry")


If Len(Criteria) = 0 Then
Q.SQL = "Select * From [Brandtbl]"
Else
Q.SQL = "Select * From [Brandtbl] Where [FranchiseName] In (" &
Criteria & ");"
End If


Set Q = Nothing
Criteria = ""


'List Box 3: Profit Center
Set ctl = Me![ProfitCenterChosen]


For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm


Set db = CurrentDb()
Set Q = db.QueryDefs("PrintDialogInventoryProfitCenterLBqry")


If Len(Criteria) = 0 Then
Q.SQL = "Select * From [Brandtbl]"
Else
Q.SQL = "Select * From [Brandtbl] Where [ProfitCenterNumber] In (" &
Criteria & ");"
End If


Set Q = Nothing
Criteria = ""


'List Box 4: Plant
Set ctl = Me![PlantChosen]


For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm


Set Q = db.QueryDefs("PrintDialogInventoryPlantLBqry")


If Len(Criteria) = 0 Then
Q.SQL = "Select * From [Planttbl]"
Else
Q.SQL = "Select * From [Planttbl] Where [Plnt] In (" & Criteria & ");"
End If


Set Q = Nothing
db.Close
Set db = Nothing


DoCmd.RunMacro "InventoryDialogPrintfrmMacro.PrintPreview"


End Sub