Loop through filter and output report with filter in filename

J

JohnK

Hello, I have a query that has all the fields required in it. I then made a
report from that query and grouped it on a field "Processor". What I would
loke to do is run the report for each processor in the query and have it save
the file as an excel document with the name "REKICK_[the processor
field]_today's date.xls". I am trying to make it loop through and leave a
file for each processor that has data in the query. I am trying the code
below but it errors in the spot marked. I am new to loops and somewhat new
to vba.

My attempt:

Private Sub Command220_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryRekicks")

With rst

If Not (.EOF And .BOF) Then
..MoveLast
..MoveFirst
Do
DoCmd.OpenReport "qryRekicks", acViewPreview, , "[Processor] = " &
..Fields![Processor]
--STOPS ON NEXT LINE--
DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & [Processor] &
Format(Date, "mmddyy") & ".xls", False
DoCmd.Close acOutputReport, "qryRekicks", False
..MoveNext

Loop Until .EOF
End If
..Close
End With
Set rst = Nothing
Set db = Nothing

End Sub

Thank you for your help.
John
 
A

Andy Hull

Hi John

With this code you step through the recordset and create a report for every
row.
So if processor 1 is a group of 5 rows you'll get 5 reports (all containing
all 5 rows) for processor 1.

What you really need to do is to step through a distinct list of processors.

To do this, set your recordset as follows...

Set rst = db.OpenRecordset("select distinct Processor from qryRekicks")


Now, for the line in question to work you need to get [Processor] from the
recordset.

Try the line as...

DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & .Fields![Processor] &
Format(Date, "mmddyy") & ".xls", False


Also, you don't need to open the report in order to output it so unless you
really want to see them on screen you can remove the lines...

DoCmd.OpenReport

and

DoCmd.Close acOutputReport, "qryRekicks", False

Note that if this line were to be kept, acOutputReport should be acReport
(if I'm correct in assuming this was intended to close the report preview).

If you do keep these I don't believe they will be of use as there won't be a
pause between the reports opening and closing.

hth

Andy Hull
 
J

JohnK

Thanks Andy. This works up to a point. I get the report with each
processor's name in the filename but I was trying to filter their report as
well, so that Johnny only gets Johnny's records.

Am I missing a "For each" type piece?

Thanks you for your explanation the last time, it was quite valuable for me.
I print and save these in a notebook (as well as many others I find while
searching).

John

Andy Hull said:
Hi John

With this code you step through the recordset and create a report for every
row.
So if processor 1 is a group of 5 rows you'll get 5 reports (all containing
all 5 rows) for processor 1.

What you really need to do is to step through a distinct list of processors.

To do this, set your recordset as follows...

Set rst = db.OpenRecordset("select distinct Processor from qryRekicks")


Now, for the line in question to work you need to get [Processor] from the
recordset.

Try the line as...

DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & .Fields![Processor] &
Format(Date, "mmddyy") & ".xls", False


Also, you don't need to open the report in order to output it so unless you
really want to see them on screen you can remove the lines...

DoCmd.OpenReport

and

DoCmd.Close acOutputReport, "qryRekicks", False

Note that if this line were to be kept, acOutputReport should be acReport
(if I'm correct in assuming this was intended to close the report preview).

If you do keep these I don't believe they will be of use as there won't be a
pause between the reports opening and closing.

hth

Andy Hull


JohnK said:
Hello, I have a query that has all the fields required in it. I then made a
report from that query and grouped it on a field "Processor". What I would
loke to do is run the report for each processor in the query and have it save
the file as an excel document with the name "REKICK_[the processor
field]_today's date.xls". I am trying to make it loop through and leave a
file for each processor that has data in the query. I am trying the code
below but it errors in the spot marked. I am new to loops and somewhat new
to vba.

My attempt:

Private Sub Command220_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryRekicks")

With rst

If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
Do
DoCmd.OpenReport "qryRekicks", acViewPreview, , "[Processor] = " &
.Fields![Processor]
--STOPS ON NEXT LINE--
DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & [Processor] &
Format(Date, "mmddyy") & ".xls", False
DoCmd.Close acOutputReport, "qryRekicks", False
.MoveNext

Loop Until .EOF
End If
.Close
End With
Set rst = Nothing
Set db = Nothing

End Sub

Thank you for your help.
John
Click to expand...
Click to expand...
 
J

JohnK

OK, I tried adding this back into the mix:

DoCmd.OpenReport "qryRekicks", acViewPreview, , "[Processor] = " &
".Fields![Processor]"
DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS,
"\\vo-alb-fs03\data\DailyReports\current\" & "REKICK_" & .Fields![Processor]
& Format(Date, "mmddyy") & ".xls", False
DoCmd.Close acOutputReport, "qryRekicks", False

I get an error (runtime 3075) on the line here: DoCmd.OpenReport
"qryRekicks", acViewPreview, , "[Processor] = " & ".Fields![Processor]"

I have tried variants of quotes and can't quite get it. The Processor field
is text.

Andy Hull said:
Hi John

With this code you step through the recordset and create a report for every
row.
So if processor 1 is a group of 5 rows you'll get 5 reports (all containing
all 5 rows) for processor 1.

What you really need to do is to step through a distinct list of processors.

To do this, set your recordset as follows...

Set rst = db.OpenRecordset("select distinct Processor from qryRekicks")


Now, for the line in question to work you need to get [Processor] from the
recordset.

Try the line as...

DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & .Fields![Processor] &
Format(Date, "mmddyy") & ".xls", False


Also, you don't need to open the report in order to output it so unless you
really want to see them on screen you can remove the lines...

DoCmd.OpenReport

and

DoCmd.Close acOutputReport, "qryRekicks", False

Note that if this line were to be kept, acOutputReport should be acReport
(if I'm correct in assuming this was intended to close the report preview).

If you do keep these I don't believe they will be of use as there won't be a
pause between the reports opening and closing.

hth

Andy Hull


JohnK said:
Hello, I have a query that has all the fields required in it. I then made a
report from that query and grouped it on a field "Processor". What I would
loke to do is run the report for each processor in the query and have it save
the file as an excel document with the name "REKICK_[the processor
field]_today's date.xls". I am trying to make it loop through and leave a
file for each processor that has data in the query. I am trying the code
below but it errors in the spot marked. I am new to loops and somewhat new
to vba.

My attempt:

Private Sub Command220_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryRekicks")

With rst

If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
Do
DoCmd.OpenReport "qryRekicks", acViewPreview, , "[Processor] = " &
.Fields![Processor]
--STOPS ON NEXT LINE--
DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & [Processor] &
Format(Date, "mmddyy") & ".xls", False
DoCmd.Close acOutputReport, "qryRekicks", False
.MoveNext

Loop Until .EOF
End If
.Close
End With
Set rst = Nothing
Set db = Nothing

End Sub

Thank you for your help.
John
Click to expand...
Click to expand...
 
J

JohnK

Got it!! It is "messy" in that it opens each instance and closes it, but it
works (filters, and saves each file by processor. Here is the right quotes
and such...whew

DoCmd.OpenReport "qryRekicks", acViewPreview, , "Processor = '" &
..Fields![Processor] & "'"
DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS,
"\\vo-alb-fs03\data\DailyReports\current\" & "REKICK_" & .Fields![Processor]
& Format(Date, "mmddyy") & ".xls", False
DoCmd.Close acOutputReport, "qryRekicks", False

Always learning,
John
Andy Hull said:
Hi John

With this code you step through the recordset and create a report for every
row.
So if processor 1 is a group of 5 rows you'll get 5 reports (all containing
all 5 rows) for processor 1.

What you really need to do is to step through a distinct list of processors.

To do this, set your recordset as follows...

Set rst = db.OpenRecordset("select distinct Processor from qryRekicks")


Now, for the line in question to work you need to get [Processor] from the
recordset.

Try the line as...

DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & .Fields![Processor] &
Format(Date, "mmddyy") & ".xls", False


Also, you don't need to open the report in order to output it so unless you
really want to see them on screen you can remove the lines...

DoCmd.OpenReport

and

DoCmd.Close acOutputReport, "qryRekicks", False

Note that if this line were to be kept, acOutputReport should be acReport
(if I'm correct in assuming this was intended to close the report preview).

If you do keep these I don't believe they will be of use as there won't be a
pause between the reports opening and closing.

hth

Andy Hull


JohnK said:
Hello, I have a query that has all the fields required in it. I then made a
report from that query and grouped it on a field "Processor". What I would
loke to do is run the report for each processor in the query and have it save
the file as an excel document with the name "REKICK_[the processor
field]_today's date.xls". I am trying to make it loop through and leave a
file for each processor that has data in the query. I am trying the code
below but it errors in the spot marked. I am new to loops and somewhat new
to vba.

My attempt:

Private Sub Command220_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryRekicks")

With rst

If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
Do
DoCmd.OpenReport "qryRekicks", acViewPreview, , "[Processor] = " &
.Fields![Processor]
--STOPS ON NEXT LINE--
DoCmd.OutputTo acOutputReport, "qryRekicks", acFormatXLS, "\\My Server Name
Goes Here\data\DailyReports\current\" & "REKICK_" & [Processor] &
Format(Date, "mmddyy") & ".xls", False
DoCmd.Close acOutputReport, "qryRekicks", False
.MoveNext

Loop Until .EOF
End If
.Close
End With
Set rst = Nothing
Set db = Nothing

End Sub

Thank you for your help.
John
Click to expand...
Click to expand...
 

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

Loop through Table records 7
stuck in a loop 5
Email select page of report -- need help 0
Email Report 2
Recordset is not filtering Report- version 2 6
Output Multiple Reports from Recordsource 2
Add password to an exported report 0
Filter report in VBA loop not working 4

Top