Hi Duane,
When I try to run the reports, I receive an error:
Run Time error 3075
Syntax error (missing operator) in query expression '(BkAgt="AK And
Office="Group")'.
AK is the first BKAgt and Group is the first Office in my List Box.
The code looks like this:
Private Sub cmdBkAgtBrReports_Click()
Dim intItem As Integer
Dim lbo As ListBox
Dim strReport As String
Dim strOpenReport As String
Dim strWhere As String
Set lbo = Me.lboBkAgt
strReport = "Monthly Total - Agent per Branch"
For intItem = 0 To lbo.ListCount - 1
Debug.Print lbo.Column(0, intItem), lbo.Column(1, intItem)
strWhere = "BkAgt=""" & lbo.Column(0, intItem) & _
" AND Office =""" & lbo.Column(1, intItem) & """"
DoCmd.OpenReport strReport, , , strWhere
Next
End Sub
I beleive that I have the code as your example. The only difference is the
name of the report. I think that I am missing somehting that's probably
obvious but I cannot figure it out.
Thanks for your help,
Zoe
Duane Hookom said:
Your listbox can have two columns and then use code like:
Dim intItem As Integer
Dim lbo As ListBox
Dim strReport As String
Dim strOpenReport As String
Dim strWhere As String
Set lbo = Me.lboBkAgt
strReport = "Monthly Total - Individual Agent"
For intItem = 0 To lbo.ListCount - 1
Debug.Print lbo.Column(0, intItem), lbo.Column(1, intItem)
strWhere = "BkAgt=""" & lbo.Column(0, intItem) & _
" AND Office =""" & lbo.Column(1, intItem) & """"
DoCmd.OpenReport strReport, , , strWhere
Next
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane,
Thanks again - it works perfectly.
I have another situation that I would like to handle similarly but I'm not
sure if or how I can.
I have a table that I would like to creat a List Box for.
There are 2 fields: BKAgt and Office
BkAgt is listed as many times as there are unique Offices for that agent.
Example: BkAgt ZN is listed twice - once in Office Downtown and once in
Office Uptown.
BkAgt and Office are both fields in the underlying query for the reports
that I want to run.
I'd like to automatically run 2 reports for the example. One for ZN -
Downtown and one for ZN - Uptown.
Can this be done?
Thanks,
Zoe
:
You can set the list box to invisible. I only used the list box because it is
fairly simple to use it to create a recordset. The other solution would be to
create an ADO or DAO recordset and step through it.
The Where Condition of the DoCmd.OpenReport will limit the report to a
single PkAgt value.
My code had a line that began with an apostrophe. This makes that line into
a comment as the code doesn't get run. By removing the apostrophe at the
beginning of the line (uncommenting), the line will actually run.
I assume your BkAgt field is a text field so you must use:
'DoCmd.OpenReport "Monthly Total - Individual Agent" _
, , , "BkAgt=""" & lbo.ItemData(intItem) & """"
BTW: if you leave your BkAgt list box named "List20", I may refuse any
further assistance ;-) Find and use a naming convention so you don't have
Text1, List20, Label4,... If you are going to reference a control in code or
other expression, take the time to give it a name.
--
Duane Hookom
Microsoft Access MVP
:
Thanks Duane for your response.
Of course, I have questions.
There will be about 50 unique BkAgt codes. Can I hide the list box on the
form so the user doesn't see it?
In the underlying query in the BkAgt field, do I reference the list box in
the criteria?
Example: [Forms]![Specs]![List20]
The BKAgt may be a combination of alpha/numeric. Do I use the example that
you show for text?
I'm not sure what you mean by uncomment the DoCmd.OpenReport line. I would
like the reports to print automatically. Do I change this line to something
else?
Thanks again,
Zoe
:
There are many methods for doing this. One solution is to create a list box
on your form that has a row source of the unique BkAgt values. Name the list
box lboPkAgt. Add a command button that runs the following code. Open the
debug window (press Ctrl+G) to see if the results are as expected
Private Sub cmdBkAgtReports_Click()
Dim intItem As Integer
Dim lbo As ListBox
Set lbo = Me.lboPkAgt
For intItem = 0 To lbo.ListCount - 1
Debug.Print lbo.ItemData(intItem)
'DoCmd.OpenReport "Monthly Total - Individual Agent" _
, , , "BkAgt=" & lbo.ItemData(intItem)
Next
End Sub
If the results look good, uncomment the DoCmd.OpenReport line. This code
assume BkAgt is numeric. If the field is text, you will need to use
"BkAgt=""" & lbo.ItemData(intItem) & """"
--
Duane Hookom
Microsoft Access MVP
:
I would like to run the same report multiple times for different individuals
without having to enter the criteria each time.
I have a table "Agents" and the field that I want to use in the table as
criteria is "BkAgt"
I have a report "Monthly Total - Individual Agent" that has a Record Source
from a query "Total PC by Indidual Agent 3" This query has the field
"BkAgt", as does the report.
I'd like to have a command button on a form that the user would click and
the reports would print in the order as in the table. (one report for each
instance of BkAgt)
I've tried to follow similar threads but am very confused.
I'm assuming that I create an event on the command butoon. On Click, I
assume.
Can someone help me with what I need to have in the event and what do I need
to do in the underlying query?
As always, any assistance is greatly appreciated.
Zoe
