can I pass a parameter to a report using a macro?

[Guest]

I'm trying to run a variety of reports that have parameters attached to the
underlying query. Each report must be run with 18 different parameters and
I'm trying to automate the task to run the report, output it to the webserver
and then run the next. What should I do?

 

[Ken Snell \(MVP\)]

Macros cannot provide a parameter to a query. That can be done with VBA
programming by modifying the SQL statement of a stored query to concatenate
the parameter value into the SQL statement and then run the report that is
based on that query; repeat for each report.

 

[Guest]

Thanks. Any idea where I can go to get assistance on just exactly how to do
that?

 

[Ken Snell \(MVP\)]

I don't find any specific examples on my "well traveled" web sites, but here
are some newsgroup postings that give some ideas:

http://groups.google.com/group/micr...rydef+".sql+="&rnum=44&hl=en#0e19cfcd606cf0c0

http://groups.google.com/group/micr...rydef+".sql+="&rnum=54&hl=en#54d702bd8dc0282c

http://groups.google.com/group/micr...rydef+".sql+="&rnum=19&hl=en#b5a4f2e29e68cfe0

There are many more posts that discuss this too.

The essence of this is to build an SQL string in code, assign it to the SQL
property of a stored query, then run the report that is bound to that query,
then loop back to build another string and assign to the query and so on.

Do you have experience with VBA? If not, post back with some details about
how the programming would know what parameters are to be provided, etc.

 

[Guest]

I have no experience w/ VBA. I am providing a list of store branch numbers
out of another query. For example the query under a sample report is:

SELECT TOP 100 [Branch List].[Branch Number], [Top Customer Accounts Sales
Base].SAccountNum, [Top Customer Accounts Sales Base].SAccountName, [Top
Customer Accounts Sales Base].SSalesman, [Top Customer Accounts Sales
Base].SName, [Top Customer Accounts Sales Base].NYTDSold, [Top Customer
Accounts Sales Base].NYTDSoldPrev
FROM [Top Customer Accounts Sales Base] INNER JOIN [Branch List] ON [Top
Customer Accounts Sales Base].[Acct Prefix] = [Branch List].[Branch Prefix]
WHERE ((([Branch List].[Branch Number])=[Enter Branch]))
ORDER BY [Top Customer Accounts Sales Base].NYTDSold DESC;

And I want to run the parameter for =[Enter Branch] from the following query
results:

SELECT [Branch List].[Branch Number]
FROM [Branch List]
WHERE ((([Branch List].[Branch Number]) Between "1000" And "2999" And
([Branch List].[Branch Number]) Not Like "1000Store" And ([Branch
List].[Branch Number]) Not Like "1000StoreYard"));

Then I need to take those reports that are generated and output them using a
macro to a folder on our sharepoint site (I know how to do this). Whatever
this module turns out to be, can it be executed as part of a macro or what is
the general jist of modules? I'm a bit confused by how they implement within
the database and how to call them to make them happen. I'm probably putting
the cart before the horse anyway.

Thanks Ken for your insight.

Jim

 

[Ken Snell \(MVP\)]

Have run out of time tonite, so can't post tonite. Will reply as soon as my
work schedule gives me a breather....
--

Ken Snell
<MS ACCESS MVP>

I have no experience w/ VBA. I am providing a list of store branch numbers
out of another query. For example the query under a sample report is:

SELECT TOP 100 [Branch List].[Branch Number], [Top Customer Accounts Sales

< snipped >

 

[Guest]

No problem. Thanks!

Have run out of time tonite, so can't post tonite. Will reply as soon as my
work schedule gives me a breather....
--

Ken Snell
<MS ACCESS MVP>

I have no experience w/ VBA. I am providing a list of store branch numbers
out of another query. For example the query under a sample report is:

SELECT TOP 100 [Branch List].[Branch Number], [Top Customer Accounts Sales

< snipped >

 

[Ken Snell \(MVP\)]

What I envision is the creation of a function in VBA code that will be able
to generate the appropriate query strings where the criterion value has been
incorporated into the SQL statement.

From where would one get the various values for [Enter Branch] parameter? Do
you have a table of branches that could be used?

What is the RecordSource query's name that you use for the reports? Are you
using a saved query, or is the SQL statement directly stated in the report's
RecordSource propety?

I am not familiar with SharePoint usage at this time; could you tell me how
your macro outputs a report to a SharePoint site? What macro action is used?
If you can tell me that, and the other info that I've asked about, I believe
we can put the finishing touches on your setup.
--

Ken Snell
<MS ACCESS MVP>

I have no experience w/ VBA. I am providing a list of store branch numbers
out of another query. For example the query under a sample report is:

SELECT TOP 100 [Branch List].[Branch Number], [Top Customer Accounts Sales
Base].SAccountNum, [Top Customer Accounts Sales Base].SAccountName, [Top
Customer Accounts Sales Base].SSalesman, [Top Customer Accounts Sales
Base].SName, [Top Customer Accounts Sales Base].NYTDSold, [Top Customer
Accounts Sales Base].NYTDSoldPrev
FROM [Top Customer Accounts Sales Base] INNER JOIN [Branch List] ON [Top
Customer Accounts Sales Base].[Acct Prefix] = [Branch List].[Branch
Prefix]
WHERE ((([Branch List].[Branch Number])=[Enter Branch]))
ORDER BY [Top Customer Accounts Sales Base].NYTDSold DESC;

And I want to run the parameter for =[Enter Branch] from the following
query
results:

SELECT [Branch List].[Branch Number]
FROM [Branch List]
WHERE ((([Branch List].[Branch Number]) Between "1000" And "2999" And
([Branch List].[Branch Number]) Not Like "1000Store" And ([Branch
List].[Branch Number]) Not Like "1000StoreYard"));

Then I need to take those reports that are generated and output them using
a
macro to a folder on our sharepoint site (I know how to do this).
Whatever
this module turns out to be, can it be executed as part of a macro or what
is
the general jist of modules? I'm a bit confused by how they implement
within
the database and how to call them to make them happen. I'm probably
putting
the cart before the horse anyway.

Thanks Ken for your insight.

Jim

I don't find any specific examples on my "well traveled" web sites, but
here
are some newsgroup postings that give some ideas:

http://groups.google.com/group/micr...rydef+".sql+="&rnum=44&hl=en#0e19cfcd606cf0c0

http://groups.google.com/group/micr...rydef+".sql+="&rnum=54&hl=en#54d702bd8dc0282c

http://groups.google.com/group/micr...rydef+".sql+="&rnum=19&hl=en#b5a4f2e29e68cfe0

There are many more posts that discuss this too.

The essence of this is to build an SQL string in code, assign it to the
SQL
property of a stored query, then run the report that is bound to that
query,
then loop back to build another string and assign to the query and so on.

Do you have experience with VBA? If not, post back with some details
about
how the programming would know what parameters are to be provided, etc.

 

[Guest]

Ok Ken,
I think I have what you need:
I have a query named: [Branch List Query] based off of a table called
[Branch List] with a field called [Branch Number], this is due to the fact
that we have some branch #s that I do not want to run.

Report Recordsource:
Saved query titled [Top 200 Customer Accounts Based on LY Sales by Branch*]

Sharepoint:
Macro used is OutputTo with the following settings:
Type: Report
Report Name: Top 200 Customer Accounts based on LY Sales by Branch
Output Format: Rich Text Format
Output File: //Sps1/clc/FOCUS/1001/Shared%20Documents/Top 200 Customer
Accounts based on LY Sales by Branch.rtf

The sharepoint outputting works just like kicking a file out to a folder--so
it's pretty basic.

Thanks for your help and let me know if I've missed anything.

Jim

What I envision is the creation of a function in VBA code that will be able
to generate the appropriate query strings where the criterion value has been
incorporated into the SQL statement.

From where would one get the various values for [Enter Branch] parameter? Do
you have a table of branches that could be used?

What is the RecordSource query's name that you use for the reports? Are you
using a saved query, or is the SQL statement directly stated in the report's
RecordSource propety?

I am not familiar with SharePoint usage at this time; could you tell me how
your macro outputs a report to a SharePoint site? What macro action is used?
If you can tell me that, and the other info that I've asked about, I believe
we can put the finishing touches on your setup.
--

Ken Snell
<MS ACCESS MVP>

I have no experience w/ VBA. I am providing a list of store branch numbers
out of another query. For example the query under a sample report is:

SELECT TOP 100 [Branch List].[Branch Number], [Top Customer Accounts Sales
Base].SAccountNum, [Top Customer Accounts Sales Base].SAccountName, [Top
Customer Accounts Sales Base].SSalesman, [Top Customer Accounts Sales
Base].SName, [Top Customer Accounts Sales Base].NYTDSold, [Top Customer
Accounts Sales Base].NYTDSoldPrev
FROM [Top Customer Accounts Sales Base] INNER JOIN [Branch List] ON [Top
Customer Accounts Sales Base].[Acct Prefix] = [Branch List].[Branch
Prefix]
WHERE ((([Branch List].[Branch Number])=[Enter Branch]))
ORDER BY [Top Customer Accounts Sales Base].NYTDSold DESC;

And I want to run the parameter for =[Enter Branch] from the following
query
results:

SELECT [Branch List].[Branch Number]
FROM [Branch List]
WHERE ((([Branch List].[Branch Number]) Between "1000" And "2999" And
([Branch List].[Branch Number]) Not Like "1000Store" And ([Branch
List].[Branch Number]) Not Like "1000StoreYard"));

Then I need to take those reports that are generated and output them using
a
macro to a folder on our sharepoint site (I know how to do this).
Whatever
this module turns out to be, can it be executed as part of a macro or what
is
the general jist of modules? I'm a bit confused by how they implement
within
the database and how to call them to make them happen. I'm probably
putting
the cart before the horse anyway.

Thanks Ken for your insight.

Jim

I don't find any specific examples on my "well traveled" web sites, but
here
are some newsgroup postings that give some ideas:

http://groups.google.com/group/micr...rydef+".sql+="&rnum=44&hl=en#0e19cfcd606cf0c0

http://groups.google.com/group/micr...rydef+".sql+="&rnum=54&hl=en#54d702bd8dc0282c

http://groups.google.com/group/micr...rydef+".sql+="&rnum=19&hl=en#b5a4f2e29e68cfe0

There are many more posts that discuss this too.

The essence of this is to build an SQL string in code, assign it to the
SQL
property of a stored query, then run the report that is bound to that
query,
then loop back to build another string and assign to the query and so on.

Do you have experience with VBA? If not, post back with some details
about
how the programming would know what parameters are to be provided, etc.
--

Ken Snell
<MS ACCESS MVP>



Thanks. Any idea where I can go to get assistance on just exactly how
to
do
that?

:

Macros cannot provide a parameter to a query. That can be done with
VBA
programming by modifying the SQL statement of a stored query to
concatenate
the parameter value into the SQL statement and then run the report
that
is
based on that query; repeat for each report.


--

Ken Snell
<MS ACCESS MVP>


I'm trying to run a variety of reports that have parameters attached
to
the
underlying query. Each report must be run with 18 different
parameters
and
I'm trying to automate the task to run the report, output it to the
webserver
and then run the next. What should I do?

 

[Ken Snell \(MVP\)]

I assume that the [Top 200 Customer Accounts Based on LY Sales by Branch*]
query contains the parameter that asks for the [Branch Number] value, right?
If yes, modify that query to eliminate that criterion parameter from the
query. The query now should be this:

SELECT TOP 100 [Branch List].[Branch Number], [Top Customer Accounts Sales
Base].SAccountNum, [Top Customer Accounts Sales Base].SAccountName, [Top
Customer Accounts Sales Base].SSalesman, [Top Customer Accounts Sales
Base].SName, [Top Customer Accounts Sales Base].NYTDSold, [Top Customer
Accounts Sales Base].NYTDSoldPrev
FROM [Top Customer Accounts Sales Base] INNER JOIN [Branch List] ON [Top
Customer Accounts Sales Base].[Acct Prefix] = [Branch List].[Branch
Prefix]
ORDER BY [Top Customer Accounts Sales Base].NYTDSold DESC;


(Parenthetical note: It's not a good idea to use a character such as * in
the name of a query. If not properly handled, you can cause problems for
your query because * is the wild card search character.)


OK, now create a new, regular module in your database (see Modules on left
side of database window). You can name the module anything you want, so long
as it's not the same name as the function that we're going to create next.
Open the Module that you've created and paste the following function into
the module (I've written this function with the assumption that the Branch
Number field is a numeric field, not a text field):

' *** Start of Function
Public Function ExportMyReports() AS Variant
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim lngOrderBy As Long
Dim strSQL As String, strSQL_O As String, strSQL_S As String
Dim strSQL_S1 As String, strSQL_S2 As String

Const strQuery As String = "Top 200 Customer Accounts Based on LY Sales by
Branch*"
Const strRecordset As String = "Branch List Query"
Const strCriterion As String = " WHERE [Branch Number]="
Const strFile As String = "//Sps1/clc/FOCUS/1001/Shared%20Documents/Top 200
Customer Accounts based on LY Sales by Branch _ "
Const strReport As String = "Top 200 Customer Accounts based on LY Sales by
Branch"

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs(strQuery)
' Save original SQL string
strSQL_O = qdf.SQL
strSQL_S = strSQL_O

lngOrderBy = InStrRev(strSQL_S, "ORDER BY")
' Get part of SQL string before ORDER BY clause
strSQL_S1=Left(strSQL_S, lngOrderBy -1) & " "
' Get part of SQL string starting with ORDER BY clause
strSQL_S2 = " " & Mid(strSQL_S, lngOrderBy)

Set rst = dbs.OpenRecordset(strRecordset, dbOpenDynaset, dbReadOnly)

Do While rst.EOF = False
' create branch-specific SQL string in query
strSQL = strCriterion & rst![Branch Number].Value
qdf.SQL = strSQL_S1 & strSQL & strSQL_S2
' output report for the branch (branch number is in file name)
DoCmd.OutputTo acOutputReport, strReport, acFormatRTF, _
strFile & rst![Branch Number].Value & ".rtf",False
' get next branch number
rst.MoveNext
Loop

' Set query's SQL string back to original string
qdf.SQL = strSQL_O
qdf.Close
Set qdf = Nothing
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Exit Function
End Function
' *** End of Function



Now, in the macro that currently does the OutputTo action, replace the steps
with just one step (be sure to include the () at end of function name):
Action: RunCode
Function Name: ExportMyReports()


This macro will run the function, which will create a report for each branch
number in the branch number list query.

--

Ken Snell
<MS ACCESS MVP>

Ok Ken,
I think I have what you need:
I have a query named: [Branch List Query] based off of a table called
[Branch List] with a field called [Branch Number], this is due to the fact
that we have some branch #s that I do not want to run.

Report Recordsource:
Saved query titled [Top 200 Customer Accounts Based on LY Sales by
Branch*]

Sharepoint:
Macro used is OutputTo with the following settings:
Type: Report
Report Name: Top 200 Customer Accounts based on LY Sales by Branch
Output Format: Rich Text Format
Output File: //Sps1/clc/FOCUS/1001/Shared%20Documents/Top 200 Customer
Accounts based on LY Sales by Branch.rtf

The sharepoint outputting works just like kicking a file out to a
folder--so
it's pretty basic.

Thanks for your help and let me know if I've missed anything.

Jim