Change Report Record Source from VB

[PCX]

I have a report "Customer Invoices" that has a query record source "OpenOrderQry". I have tried to change the record source from VB with the following command:

DoCmd.OpenReport "Customer Invoices", acViewPreview, "OrdersQrybyDate".

The Report opens, but with the "OpenOrderQry" records displayed and not the desired "OrdersQrybyDate".

In checking the queries independently of the reports, the correct records are displayed for each query.

What am I doing wrong? (I assume I can change the record source from VB?)

Thanks
Lee

 

[PCX]

This forum is great...

I found the answer to my question in a previous answer by "jimmythegreek" in the thread "Recordsource for reports".

Here it is incase someone has a similar problem:


"Create your report as you want it, bound to a query, table, or SQL Statement.
When you are sure you have want you want, Remove the Record Source, and save the report.

In a module, create a public string, let's say gstrReportSQL.

After you have created your SQL String (I assume from a form), set gstrReportSQL equal to your created SQL String.

On the OnOpen event of the report, put the following:
Me.RecordSource = gstrReportSQL"


I set my gstrReportSQL = to the "OrdersQrybyDate" query in the form that calls the Report and like magic, the query is set correctly

Thanks
Lee

 

[MichaelRed]

PCX,

The alternative is to have a single query, which is left in the ReciordSource, but to change the query dynamically before opening the report.

For example, let the report recordsource be "MyReportQuery", but set the SQL property of MyReportQuery to the SQL property of "OrdersQrybyDate" | "OpenOrderQry" .


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[kyleo]

Thanks for guiding me. I've got the report working. Now I want to "pass" a variable string "CustName" that shows up in a text box in the report which is run in MSaccess.

It appears that when I do the OPEN Report stmt from my VB program the Access VBA is not able to get the "CustName" from the VB even though it is declared as a public in the general declarations section of the VB program. Don't they "talk" to each other?

I'm pretty naive on this stuff so thanks for bearing with me.