Reports based on multiple criteria in a query

  • Thread starter Thread starter David Coates
  • Start date Start date
D

David Coates

I have a requirement to print out a report for each team leader. I have a
table with the team leaders listed in it, and they are assigned to a number
of tasks in another table. I have developed a report based on the tasks
table and grouped by team leader and that works fine. The problem is though
that there is only one report header page and I want each team leader's
report to be individual with it's own report header page.

I have written the following code which loops through the various team
leaders and runs the query with the criteria referring to 'teamleader()',
unfortunately I keep getting an error message "Run time error 2486 You can't
carry out this action at the present time" when it reaches the line
"DoCmd.OpenView "Print Preview", acViewPreview". The full code involved is:

Function wrkdiary_report()

DoCmd.OpenView "Print Preview", acViewPreview

DoCmd.PrintOut

response = MsgBox("Next Team Leader?", vbOKCancel) ' This is only to
enable me to control the

output until I get the whole code working correctly.

End Function

Function teamleader()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblteam_leader", dbOpenSnapshot)
Do Until rst.EOF

teamleader = rst!Team_Leader
wrkdiary_report

rst.MoveNext

Loop

End Function

I apologise in advance if this is the wrong newsgroup for this. It seemed as
much a report writing issue as a VBA issue.

Look forward to any assistance.
--
Regards,

David Coates

Progen Ltd.
Mobile : 021 685 607
Phone : +64 7 839 3326
Fax : +64 7 839 3327

CONFIDENTIALITY : This e-mail and any attachments are confidential and
may be privileged. If you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.
 
You can get rid of the Page Header if you want and set your Sorting and
Grouping on TeamLeader and show at least the Group Header. Set the Repeat
Section property of the TeamLeader header to Yes. You can also set up the
group to start each new group on a new page.
 
I agree that is an option, but it does not give me the report Header page
unique for each team leader.

I am developing very specific reports based on a Microsoft project file.
The planning for the project is done in Microsoft project saved as a
database, and the reports will be developed in Access. The main reason for
this, is that the report features in Project are very limited for our
purposes.

I am using the report header page as effectively the cover page. The report
header page will have the same information on all the cover pages, plus the
team leader's name for that report.

The number of tasks for each team leader means that each team leader report
will have a number of pages in it, and this makes the use of the page header
limited for my purposes.

The reports will have the team leader's name on the first 'page', followed
by the details of the report which has in it sub groups. Each team leader
will then get their own report detailing the tasks assigned to them along
with other details of those tasks.

The other option is to run the report manually for each team leader
individually, and manually assigning the team leader's name to the criteria
in the query field using the [] prompt method. This has a disadvantage
though that if the name is forgotten added or changed it will make the
process of getting these reports out more onerous and the reports will be
run daily .

I have refined my code so that it does give me the report for the first team
leader, but whilst it will loop through the other team leader names it does
not give me their report.

Function wrkdiary_report()

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblteam_leader", dbOpenSnapshot)

Do Until rst.EOF

DoCmd.OpenReport "rptworkdiary", acViewPreview

teamleader

Loop

End Function

Function teamleader()

teamleader = rst!Team_Leader
rst.MoveNext

End Function

Any further ideas?

Regards

David Coates
 
My apologies. The data type for team leader is text, generally just
initials. Many thanks for your suggestions I will give it a go now.

Regards

David Coates

Duane Hookom said:
I am not sure what the data type of Team_Leader is. If it is numeric use the
code below:
Function wrkdiary_report()
Dim strWhere as String
Dim db as DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblteam_leader", dbOpenSnapshot)
With rst
Do Until .EOF
strWhere = "[Team_Leader] = " & .Fields![Team_Leader]
DoCmd.OpenReport "rptworkdiary", acViewPreview, , strWhere
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set db = Nothing
End Function
If it is text, then use this line to replace a line above
strWhere = "[Team_Leader] = """ & .Fields![Team_Leader] & """"

--
Duane Hookom
Microsoft Access MVP


David Coates said:
I agree that is an option, but it does not give me the report Header page
unique for each team leader.

I am developing very specific reports based on a Microsoft project file.
The planning for the project is done in Microsoft project saved as a
database, and the reports will be developed in Access. The main reason for
this, is that the report features in Project are very limited for our
purposes.

I am using the report header page as effectively the cover page. The report
header page will have the same information on all the cover pages, plus the
team leader's name for that report.

The number of tasks for each team leader means that each team leader report
will have a number of pages in it, and this makes the use of the page header
limited for my purposes.

The reports will have the team leader's name on the first 'page', followed
by the details of the report which has in it sub groups. Each team leader
will then get their own report detailing the tasks assigned to them along
with other details of those tasks.

The other option is to run the report manually for each team leader
individually, and manually assigning the team leader's name to the criteria
in the query field using the [] prompt method. This has a disadvantage
though that if the name is forgotten added or changed it will make the
process of getting these reports out more onerous and the reports will be
run daily .

I have refined my code so that it does give me the report for the first team
leader, but whilst it will loop through the other team leader names it does
not give me their report.

Function wrkdiary_report()

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblteam_leader", dbOpenSnapshot)

Do Until rst.EOF

DoCmd.OpenReport "rptworkdiary", acViewPreview

teamleader

Loop

End Function

Function teamleader()

teamleader = rst!Team_Leader
rst.MoveNext

End Function

Any further ideas?

Regards

David Coates

Duane Hookom said:
You can get rid of the Page Header if you want and set your Sorting and
Grouping on TeamLeader and show at least the Group Header. Set the Repeat
Section property of the TeamLeader header to Yes. You can also set up the
group to start each new group on a new page.

--
Duane Hookom
MS Access MVP


I have a requirement to print out a report for each team leader. I
Click to expand...
have
a
table with the team leaders listed in it, and they are assigned to a
number
of tasks in another table. I have developed a report based on the tasks
table and grouped by team leader and that works fine. The problem is
though
that there is only one report header page and I want each team leader's
report to be individual with it's own report header page.

I have written the following code which loops through the various team
leaders and runs the query with the criteria referring to 'teamleader()',
unfortunately I keep getting an error message "Run time error 2486 You
can't
carry out this action at the present time" when it reaches the line
"DoCmd.OpenView "Print Preview", acViewPreview". The full code involved
is:

Function wrkdiary_report()

DoCmd.OpenView "Print Preview", acViewPreview

DoCmd.PrintOut

response = MsgBox("Next Team Leader?", vbOKCancel) ' This is
Click to expand...
only
to
enable me to control the

output until I get the whole code working correctly.

End Function

Function teamleader()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblteam_leader", dbOpenSnapshot)
Do Until rst.EOF

teamleader = rst!Team_Leader
wrkdiary_report

rst.MoveNext

Loop

End Function

I apologise in advance if this is the wrong newsgroup for this. It seemed
as
much a report writing issue as a VBA issue.

Look forward to any assistance.
--
Regards,

David Coates

Progen Ltd.
Mobile : 021 685 607
Phone : +64 7 839 3326
Fax : +64 7 839 3327

CONFIDENTIALITY : This e-mail and any attachments are
Click to expand...
confidential
and
may be privileged. If you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another
Click to expand...
person,
use
it for any purpose or store or copy the information in any medium.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top