Running Report via VBA

[OD]

Hi

Is there a functional limit to the size of a string that can be passed as a
recordsource to a report? I'm calling this code

strSQLResults = BuildProdsByVendors(intVendorID)

DoCmd.Echo False
DoCmd.OpenReport "rptPurchByPublisherByDate", acViewDesign
Reports("rptPurchByPublisherByDate").RecordSource = strSQLResults

DoCmd.Close , , acSaveYes

DoCmd.Echo True
DoCmd.OpenReport "rptPurchByPublisherByDate", acViewPreview

and passing this string as strSQLResults

SELECT tblVendors.VendID, tblVendors.Manufacturer, TransDetail.PurchDate,
Product.m_Number, Product.Product, TransDetail.UnitCost,
TransDetail.RequestedBy, TransDetail.RCNum, tblRCNumbers.Department FROM
Product INNER JOIN ((tblVendors INNER JOIN TransDetail ON tblVendors.VendID =
TransDetail.Publisher) INNER JOIN tblRCNumbers ON TransDetail.RCNum =
tblRCNumbers.RC) ON (tblVendors.VendID = Product.VendID) AND
(Product.m_Number = TransDetail.m_Number) WHERE (((tblVendors.VendID) = 5))
ORDER BY TransDetail.PurchDate;

The reason I'm asking is the query runs fine except that the report is not
in PurchDate order. I've tried setting the order to purchdate but it doesn't
recognize the variable and I've tried hard coding it in the report but it
gets overwritten each time I run the query.
Any help is greatly appreciated
Thank you

 

[Dale_Fye via AccessMonster.com]

1. The order of a report is defined by the Reports Sorting and Grouping
properties, not by the OrderBy clause in the underlying query.

2. If you are running a report, the recordSource should normally remain
constant. If you want to limit the report to a subset of the records, pass
the report a criteria in the WHERE parameter of the OpenReport method;
something like:

strCriteria = "VendID = 5"
docmd.OpenReport "reportName",acViewPreview, , strCriteria

HTH
Dale

 

[OD]

Thank you
It helps in a way.
Point 1. The order of the report is defined by the Reports SOrting and
Grouping properties.....

If I try to hard code it in the report it disappears (gets overwrittten)
when I assign the query to the report.
I have also tried using Reports("reportname").OrderBy PurchReq
and it gives me a variable not defined error. There has to be a way I can
tell the report the sort order I would like to use especially if it doesn't
take the order from the query.

Thanks

 

[Marshall Barton]

Is there a functional limit to the size of a string that can be passed as a
recordsource to a report? I'm calling this code

strSQLResults = BuildProdsByVendors(intVendorID)

DoCmd.Echo False
DoCmd.OpenReport "rptPurchByPublisherByDate", acViewDesign
Reports("rptPurchByPublisherByDate").RecordSource = strSQLResults

DoCmd.Close , , acSaveYes

DoCmd.Echo True
DoCmd.OpenReport "rptPurchByPublisherByDate", acViewPreview

and passing this string as strSQLResults

SELECT tblVendors.VendID, tblVendors.Manufacturer, TransDetail.PurchDate,
Product.m_Number, Product.Product, TransDetail.UnitCost,
TransDetail.RequestedBy, TransDetail.RCNum, tblRCNumbers.Department FROM
Product INNER JOIN ((tblVendors INNER JOIN TransDetail ON tblVendors.VendID =
TransDetail.Publisher) INNER JOIN tblRCNumbers ON TransDetail.RCNum =
tblRCNumbers.RC) ON (tblVendors.VendID = Product.VendID) AND
(Product.m_Number = TransDetail.m_Number) WHERE (((tblVendors.VendID) = 5))
ORDER BY TransDetail.PurchDate;

The reason I'm asking is the query runs fine except that the report is not
in PurchDate order. I've tried setting the order to purchdate but it doesn't
recognize the variable and I've tried hard coding it in the report but it
gets overwritten each time I run the query.

Report sorting needs to be specified in the Sorting and
Grouping window (View menu). Sorting in the query and the
report's OrderBy property only used after all the sorting
and grouping has been done.

Your method of setting the report's record source is a
seriosly bad way to do it. Instead, you should have the
code in the report's Open event set the report's record
source.

strSQLResults = BuildProdsByVendors(intVendorID)
DoCmd.OpenReport "rptPurchByPublisherByDate", _
acViewPreview, OpenArgs:= strSQLResults

Then the report's Open event would look like:
Me.RecordSource = Me.OpenArgs

 

[OD]

Thank you so much

I'll look into that
Greatly appreciated!

 

[Dale_Fye via AccessMonster.com]

1. Don't "assign the query to the report". Save the query and use the saved
query as the source of the report. If you really want to have the query do
the WHERE clause, and not leave it to the report, then prior to opening up
the report, change the SQL of the query that is the source for the report.

a. Define the query: qry_Report1
b. Save the query:
c. Assign that query as the reports RecordSource
d. Then, if you insist, change the SQL property of the query via code:

strSQL = .....
currentdb.querydefs("qry_Report1").sql = strSQL
Docmd.OpenReport "yourReport", acViewPreview

2. Regarding the sort order. The report is probably dropping the sorting
because you are not saving it with a recordsource. If it doesn't have a
recordsource, the sort fields don't make any sense. This should be resolved
if you follow the instructions in step #1.

Dale

Thank you
It helps in a way.
Point 1. The order of the report is defined by the Reports SOrting and
Grouping properties.....

If I try to hard code it in the report it disappears (gets overwrittten)
when I assign the query to the report.
I have also tried using Reports("reportname").OrderBy PurchReq
and it gives me a variable not defined error. There has to be a way I can
tell the report the sort order I would like to use especially if it doesn't
take the order from the query.

Thanks

1. The order of a report is defined by the Reports Sorting and Grouping
properties, not by the OrderBy clause in the underlying query.

[quoted text clipped - 43 lines]