Running Report via VBA

O

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
 
O

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
 
M

Marshall Barton

OD said:
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.
Click to expand...


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
 
Top