Launching Report with RowSource set

[darkmyztz]

Hi. Basically, I've been having trouble with this for a while now. I thought I got it working yesterday, but it turns out not. Here is the situation.

I have built a form that contains several combo boxes and a few text boxes for input. The user enters some or all of this information and clicks "Generate Report". Upon clicking this button, the form needs to launch a generic report that is populated with data using the search terms specified in the form. However, to add complexity, only certain parts of the SELECT and FROM clauses can exist depending on which options are completed on the search form.

I tried: DoCmd.OpenReport stDocName, acPreview, sqlQuery, whereCond

Where sqlQuery changed based on which settings were specified. However, this did not help because the actual query that belongs to the Report never seemed to change: sometimes it was missing columns.

I tried also to set rptNominationsList.RowSource = sqlQuery, but that also doesn't seem to work. On this one I get "Object required".

Thanks in advance!

Alex

 

[KenHigg]

I'm guessing you'll need to build a sql string on the fly for this. See this:

Link

 

[darkmyztz]

I discovered the solution to this problem, for future reference if anyone encounters this.

When using a Form to open a Report, you can pass a SQL string through the OpenArgs function of the DoCmd.OpenReport:

Form
DoCmd.OpenReport stDocName, acPreview, , stWhereCond, , OpenArgs:=stSql

Where stSql is your SQL query.

Report
In the report, you want to have something like this in the onOpen event of the Report.

Reports!reports_name.RecordSource = Me.OpenArgs

And viola!

 

[KenHigg]

Or you could simply set the reports recordsource from the forms code:

Reports!reports_name.RecordSource = stSql

 

[darkmyztz]

I tried that earlier and it didn't work, which is why I moved on to this. Hmm.

 

[modest]

Sorry - I'm a little late on this, but thats because you were trying to change it while previewing the finished product. You would have to make the changes in design mode:

            DoCmd.OpenReport sReport, acViewDesign
            Reports(sReport).RecordSource = sQuery
            DoCmd.Close acReport, sReport, acSaveYes
            DoCmd.OpenReport sReport, acViewPreview

hope this helps whomever else might find it useful.