Whats the VBA syntax to change a reports recordset?

[nonturbo]

I've been working for quite awhile on this, and I know someone here can help me with probably one line of code, or just point me in the right direction.

I've got a report that prints mailing labels. I have a form that pops up from the reports OnOpen() event to ask some options (what label to start printing at, all accounts, or to select a single account). This all works fine.

THE PROBLEM IS: My boss now wants the ability to print labels that are from a separate query. I could easily just create a copy of the report and change the recordset to the new query, but it would look much better if I can have an option button that will change the report's recordset. I'm sure this is possible.

The Report is called Mailing Labels and I've got an SQL statement already written that if I can just write some VBA for the following pseudocode, I'd be set.

SQL = MySQL Statement
Report.[Mailing Labels].Recordset = SQL

Please help if you can! Thanks!!!

 

[nonturbo]

C'mon, no takers? I'm really struggling on this one, and the worst part is I know the solution is so easy.

 

[cmmrfrds]

Use the RecordSource object not the Recordset. The basic syntax is.

Me.RecordSource = "select * from table"

 

[nonturbo]

Great! Now we're getting somewhere. The question now is where the best place to put this would be. I've got a feeling that it must be on the report's onOpen or OnFormat events, not on the form that's called by the report to set the above conditions. Let me mess around with this for a bit and if I keep fumbling, I'll let everyone know. Thanks cmmfrds for the help.
NT

 

[cmmrfrds]

I always put the code in the OnOpen event. In this example, I created a public function to return the invoiceheader id, since you can use functions in a query.


Private Sub Report_Open(Cancel As Integer)
Dim sql1 As String, sql2 As String, SQLString As String
Dim rst As DAO.Recordset

sql1 = sql1 + "SELECT ACase.aCaseID, ACase.referralDate, "
sql1 = sql1 + "Client.ssNumber , InvoiceHeader.invoiceHeaderID "
sql1 = sql1 + "FROM ACase INNER JOIN "
sql1 = sql1 + "InvoiceHeader ON "
sql1 = sql1 + "ACase.aCaseID = InvoiceHeader.aCaseID INNER JOIN "
sql1 = sql1 + "Company ON "
sql1 = sql1 + "InvoiceHeader.companyID = Company.companyID INNER JOIN "
sql1 = sql1 + "Client ON InvoiceHeader.clientID = Client.clientID "
sql1 = sql1 + "WHERE (InvoiceHeader.invoiceHeaderID = "
sql2 = &quot"

SQLString = sql1 & ReturnInvoiceHeaderID() & sql2
Me.RecordSource = SQLString

' Debug.Print " record source = "; Me.RecordSource
End Sub

 

[nonturbo]

Interesting approach, and I like how you broke up the sql string. I guess I'm relatively new to VBA programming. I think I might use a similar method to clean up my SQL queries in VBA. After messing around with this, I finally got it working, although with a very odd (and annoying) "Enter Parameter Value" inputbox that pops up asking for the value of the parameter I pass from the form to the report. If I type anything into the input box, the report opens as desired, but if I click OK with the input box blank, I get a runtime error 2427: You entered an expression that has no value. If I debug, it places me into the VBA IDE for the reports detail section, in a completely irrelivant section of code that has nothing to do with the "Enter Parameter Value" variable it was looking for. Have any ideas!? ...At least I can get the report to open now though. Thanks!

 

[cmmrfrds]

Typically if a query requires a paramater and none is supplied you get the popup window. To avoid make sure the query has a supplied paramter.

Notice in this code a function is returning an id, which in effect is a parameter.
SQLString = sql1 & ReturnInvoiceHeaderID() & sql2

Create a function in the Standard Module along with a public variable. The variable is loaded in the form before running the query.

Public pubInvoiceHdrID As Double
Function ReturnInvoiceHeaderID() As Double
ReturnInvoiceHeaderID = pubInvoiceHdrID
End Function