How to Flip-Flop Two Queries Built From Only One List box

D

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
 
J

John Nurick

As they say in Ireland, I wouldn't start from where you are but would
write VBA code that examines the listbox selections and the selected
report, and then constructs the appropriate SQL statement or statements
(I don't understand what you mean by using "four separate queries ... to
filter a chain of queries for a report"). With 12 reports, I'd probably
use a "settings" table to store the rules about which report needs which
things in which order. And I'd give my listboxes and commandbuttons
descriptive names.

But as it is, surely all you have to do is change this
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
Click to expand...

to something like this:

Dim strLBQryFilter As String
Dim strLBQryAll As String

Select Case ReportChosen
Case 1 to 9
strLBQryFilter = PrintDialogInventoryProfitCenterLBqry
strLBQryAll = PrintDialogInventoryProfitCenterLBqry2
Case 10 to 12
strLBQryFilter = PrintDialogInventoryProfitCenterLBqry2
strLBQryAll = PrintDialogInventoryProfitCenterLBqry
End Select

Set Q = dbQueryDefs(strLBQryFilter) 'query that need to be filtered
If Len(Criteria) = 0 Then
Q.SQL = "SELECT * FROM [Planttbl];"
Else
Q.SQL = "SELECT * FROM [Planttbl] WHERE [Plnt] IN (" & Criteria & ");"
End If

Set Q = dbQueryDefs(strLBQryAll) 'query with no filter
Q.SQL = "SELECT * FROM [Planttbl];"



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
Click to expand...
 
D

doyle60

Thanks John.

I will test your code shortly. It is certainly more elegant than what
I had in mind. I didn't even know about the "Case" method. Thanks.

John Nurick wrote:
< (I don't understand what you mean by using "four separate queries ...
to
filter a chain of queries for a report"). >

By "chain of queries" I mean the query tree I build to get the one
query that the report is based on. I'm not trained in Access so I am
not sure of the terminology. I certainly can't write just one query to
get the correct data in this case, nor in many cases. The chain here
is about 10 queries and even uses a union query and queries with
special type links between the queries and tables in the design view.
It's complicated.

By "four separate queries" I mean that I have four list boxes on a
dialog form. These are filters for a report and for the subform on the
dialog form. When I press the print button or an update button, I have
code that runs and updates those four queries, filtering them to match
the selections in the list boxes. I just wanted to point out that they
are four different queries and not one query with these four fields.

I know no other way to filter using list boxes than to write code that
creates a query matching the selections in the list boxes. Combo boxes
are easy to filter but I find list boxes a pain. They are so easy to
build but so hard to implement.

I hope this helps.

The code was initially built for two list boxes, by the way. I amended
it to four and am now trying to amend it, with you, to four with an
option on one (so, in a way, five).

It's a life!

Matt
 
J

John Nurick

I know no other way to filter using list boxes than to write code that
creates a query matching the selections in the list boxes.
Click to expand...

That's the only way I know. I've got a search form somewhere whose
Execute button calls VBA that inspects about eight multiselect listboxes
and the same number of other controls and assembles a single SQL SELECT
statement ready to be used as the recordsource for the search results
form and for exporting to Word or Excel.
 
D

doyle60

Hi John,

The patch you gave me below didn't work (after I corrected some
names---you copied and amended the wrong section). I tried tweaking it
but couldn't make it happen.

Here is the code with corrected names:
_______

Dim strLBQryFilter As String
Dim strLBQryAll As String

Select Case ReportChosen
Case 1 to 9
strLBQryFilter = PrintDialogInventoryClassOfTradeLBqry
strLBQryAll = PrintDialogInventoryClassOfTradeLB2qry
Case 10 to 12
strLBQryFilter = PrintDialogInventoryClassOfTradeLB2qry
strLBQryAll = PrintDialogInventoryClassOfTradeLBqry
End Select

Set Q = dbQueryDefs(strLBQryFilter) 'query that need to be filtered

If Len(Criteria) = 0 Then
Q.SQL = "SELECT * FROM [ClassOfTradetbl];"
Else
Q.SQL = "SELECT * FROM [ClassOfTradetbl] WHERE [ClassOfTrade] IN ("
& Criteria & ");"
End If

Set Q = dbQueryDefs(strLBQryAll) 'query with no filter
Q.SQL = "SELECT * FROM [ClassOfTradetbl];"
_____

Strangely, I get an error on the second line below:

Case 1 to 9
strLBQryFilter = PrintDialogInventoryClassOfTradeLBqry

But I even get this error on this exact line when my case is 10, when
it should be skipping this part altogether. Is there something wrong
with the Case code. I've never used it before.

Remember, there are four sections to this code, making four different
queries, all of which are based on four list boxes on a form.

This is the first of the four sections, and the only one that has to
have some sort of if-else type of thing.

Any suggestions?

Thanks,

Matt



John said:
As they say in Ireland, I wouldn't start from where you are but would
write VBA code that examines the listbox selections and the selected
report, and then constructs the appropriate SQL statement or statements
(I don't understand what you mean by using "four separate queries ... to
filter a chain of queries for a report"). With 12 reports, I'd probably
use a "settings" table to store the rules about which report needs which
things in which order. And I'd give my listboxes and commandbuttons
descriptive names.

But as it is, surely all you have to do is change this
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
Click to expand...

to something like this:

Dim strLBQryFilter As String
Dim strLBQryAll As String

Select Case ReportChosen
Case 1 to 9
strLBQryFilter = PrintDialogInventoryProfitCenterLBqry
strLBQryAll = PrintDialogInventoryProfitCenterLBqry2
Case 10 to 12
strLBQryFilter = PrintDialogInventoryProfitCenterLBqry2
strLBQryAll = PrintDialogInventoryProfitCenterLBqry
End Select

Set Q = dbQueryDefs(strLBQryFilter) 'query that need to be filtered
If Len(Criteria) = 0 Then
Q.SQL = "SELECT * FROM [Planttbl];"
Else
Q.SQL = "SELECT * FROM [Planttbl] WHERE [Plnt] IN (" & Criteria & ");"
End If

Set Q = dbQueryDefs(strLBQryAll) 'query with no filter
Q.SQL = "SELECT * FROM [Planttbl];"



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
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

Doyle,

You don't mention the error number, the error message, or whether it
happens at compile time or run time, so you clearly believe MVPs have
superhuman powers. Thanks for the compliment. Have you tried enclosing
PrintDialogInventoryClassOfTradeLBqry etc in quotes?

Hi John,

The patch you gave me below didn't work (after I corrected some
names---you copied and amended the wrong section). I tried tweaking it
but couldn't make it happen.

Here is the code with corrected names:
_______

Dim strLBQryFilter As String
Dim strLBQryAll As String

Select Case ReportChosen
Case 1 to 9
strLBQryFilter = PrintDialogInventoryClassOfTradeLBqry
strLBQryAll = PrintDialogInventoryClassOfTradeLB2qry
Case 10 to 12
strLBQryFilter = PrintDialogInventoryClassOfTradeLB2qry
strLBQryAll = PrintDialogInventoryClassOfTradeLBqry
End Select

Set Q = dbQueryDefs(strLBQryFilter) 'query that need to be filtered

If Len(Criteria) = 0 Then
Q.SQL = "SELECT * FROM [ClassOfTradetbl];"
Else
Q.SQL = "SELECT * FROM [ClassOfTradetbl] WHERE [ClassOfTrade] IN ("
& Criteria & ");"
End If

Set Q = dbQueryDefs(strLBQryAll) 'query with no filter
Q.SQL = "SELECT * FROM [ClassOfTradetbl];"
_____

Strangely, I get an error on the second line below:

Case 1 to 9
strLBQryFilter = PrintDialogInventoryClassOfTradeLBqry

But I even get this error on this exact line when my case is 10, when
it should be skipping this part altogether. Is there something wrong
with the Case code. I've never used it before.

Remember, there are four sections to this code, making four different
queries, all of which are based on four list boxes on a form.

This is the first of the four sections, and the only one that has to
have some sort of if-else type of thing.

Any suggestions?

Thanks,

Matt



John said:
As they say in Ireland, I wouldn't start from where you are but would
write VBA code that examines the listbox selections and the selected
report, and then constructs the appropriate SQL statement or statements
(I don't understand what you mean by using "four separate queries ... to
filter a chain of queries for a report"). With 12 reports, I'd probably
use a "settings" table to store the rules about which report needs which
things in which order. And I'd give my listboxes and commandbuttons
descriptive names.

But as it is, surely all you have to do is change this
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
Click to expand...

to something like this:

Dim strLBQryFilter As String
Dim strLBQryAll As String

Select Case ReportChosen
Case 1 to 9
strLBQryFilter = PrintDialogInventoryProfitCenterLBqry
strLBQryAll = PrintDialogInventoryProfitCenterLBqry2
Case 10 to 12
strLBQryFilter = PrintDialogInventoryProfitCenterLBqry2
strLBQryAll = PrintDialogInventoryProfitCenterLBqry
End Select

Set Q = dbQueryDefs(strLBQryFilter) 'query that need to be filtered
If Len(Criteria) = 0 Then
Q.SQL = "SELECT * FROM [Planttbl];"
Else
Q.SQL = "SELECT * FROM [Planttbl] WHERE [Plnt] IN (" & Criteria & ");"
End If

Set Q = dbQueryDefs(strLBQryAll) 'query with no filter
Q.SQL = "SELECT * FROM [Planttbl];"



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
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
D

doyle60

John,
Thanks. I put PrintDialogInventoryClassOfTradeLBqry in quotation
marks, as you suggested, and that solved that issue. I got another
error on dbQueryDefs and saw that it should probably have a period in
there. I put it in and solved that issue. I got some other errors but
they were all owing to naming or misremembering the Values for my
Option Group (the Case numbers here). Problem solved. Thanks so much,

Matt
______________

Final code below:

Dim strLBQryFilter As String
Dim strLBQryAll As String

Select Case ReportChosen
Case 1 To 4, 9 To 12
strLBQryFilter = "PrintDialogInventoryClassOfTradeLBqry"
strLBQryAll = "PrintDialogInventoryClassOfTradeLB2qry"
Case 5 To 8
strLBQryFilter = "PrintDialogInventoryClassOfTradeLB2qry"
strLBQryAll = "PrintDialogInventoryClassOfTradeLBqry"
End Select

Set Q = db.QueryDefs(strLBQryFilter) 'query that need to be filtered

If Len(Criteria) = 0 Then
Q.SQL = "SELECT * FROM [ClassOfTradetbl];"
Else
Q.SQL = "SELECT * FROM [ClassOfTradetbl] WHERE [ClassOfTrade] IN (" &
Criteria & ");"
End If

Set Q = db.QueryDefs(strLBQryAll) 'query with no filter
Q.SQL = "SELECT * FROM [ClassOfTradetbl];"

Set Q = Nothing
Criteria = ""
____________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Select List Not Working 1
How to Build Two Queries From One List Box 0
Combo Box / Query 7
How to turn Code Into Function 22
passing a parameter based on multiple selections 7
How to get a List Box with nothing Chosen to be All Chosen 2
Multiselect listbox 0
Multiselect listbox 5

Top