Running a report with a user defined variable in a query

[Graham]

Hi

I have a Report who's data source is a query which requires a manager's name
to be entered.

I am using the code below to run the report

stDocName = "rptTimes"
DoCmd.OpenReport stDocName, acViewNormal

What do I need to enter to insert the value in to the query.

Thanks in advance for any help

Graham

 

[Steve Schapel]

Graham,

The easiest way to do this is to print your report from a form where
there is a textbox or combobox for the entry of the manager, and then
make a query where this textbox or combobox is referred to in the
Criteria of the query, using syntax such as...
[Forms]![NameOfForm]![NameOfTextboxOrCombobox]
....and then use this query as the Record Source of the report.

 

[Graham]

Thanks Steve

But there are only 4 managers and I want to run the same report four times
without any input from the users. I know that if one of the managers change
I will need to change the code. I was hoping to use one query and one report
but just change the variable criteria with a macro each time it is run

Graham

Graham,

The easiest way to do this is to print your report from a form where
there is a textbox or combobox for the entry of the manager, and then
make a query where this textbox or combobox is referred to in the
Criteria of the query, using syntax such as...
[Forms]![NameOfForm]![NameOfTextboxOrCombobox]
....and then use this query as the Record Source of the report.

--
Steve Schapel, Microsoft Access MVP

Hi

I have a Report who's data source is a query which requires a manager's name
to be entered.

I am using the code below to run the report

stDocName = "rptTimes"
DoCmd.OpenReport stDocName, acViewNormal

What do I need to enter to insert the value in to the query.

Thanks in advance for any help

Graham

 

[Steve Schapel]

Graham,

Ah, ok. I didn't quite understand the question. The easiest way to do
this is to use the Sorting and Grouping dialog in the design view of the
report, to insert a Manager Footer section in the report. (You might
also want a Manager Header section, as this may help make the manager's
name on the report look better). Set the Force New Page property of the
Manager Footer section to After Section. Then just print the report,
and you will get a separate report for each manager. This will not
require anything to be changed if you get a new manager.

 

[Graham]

Hi Steve

THanks I have doen that and this now works fine but the next twist is that
the managers want their own report e-mailed to them.
I am using the following code to e-mail the whole report as a snapshot
attachment.

stDocName = "rptTimesAm"
DoCmd.SendObject acReport, stDocName, "snapshot format " , (e-mail address removed)" , ,
, "Vehicle Reports Mornings", "See attachment", False

How can I code to run the report to run for each individual manager then
e-mail it to them?
Or is this pushing the bounds too far??

Thanks
Graham

 

[Steve Schapel]

Graham,

No, this isn't pushing the bounds too far. I guess it's just an
illustration that a solution is a solution to a problem, and if the
problem keeps changing, then the solution needs to keep changing too.

Well, this is a macros newsgroup, and you are not using macros. But
anyway, I'll give it a go (Caution: "air code")...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT ManagerID, Email FROM Managers")
Set qdf = dbs.QueryDefs("YourReportQuery"­)
BaseSQL = qdf.SQL
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE ManagerID = "
& !ManagerID
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptTimesAm", "Snapshot
Format", !Email, , , "Vehicle Reports Mornings", "See attachment", False
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing