Generate a Report according to parameters on a Form

[Perfectdark]

I have a form where the user can enter 2 Parameters. Now i want to automatically generate a Report according to these parameters.

I'm making an SQL Statement and this needs to become the recordsource of the report.

Here is what i have done so far:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click
 
Dim ExpID As Long
 
ExpID = Nz(DLookup("[ExperimentID]", "qrySearchExperiment", "[SampleNumber] Like '* " & Me!txtSampleNrLow & "' AND [Organism] Like '*" & Me!txtOrganism & "*'"), 0)
 
Me!strSQLReport = "SELECT tblSamples.SampleNumber, tblSamples.SampleType, tblSamples.SampleDescription, tblExperiments.Organism, fktAvg(tblAnalysisData.absoluteNumberA,tblAnalysisData.absoluteNumberB,tblAnalysisData.absoluteNumberC) AS MeanCfu, fktMeanCtrl(tblAnalysisData.ExperimentID) AS MeanCtrl, fktActivity([MeanCtrl],[MeanCfu],tblSamples.Control) AS [Antimicrobial Activity]FROM tblExperiments INNER JOIN (tblSamples INNER JOIN tblAnalysisData ON tblSamples.SampleID = tblAnalysisData.SampleID) ON tblExperiments.ExperimentID = tblAnalysisData.ExperimentID"
Me!strSQLReport = strSQLReport & " WHERE (((tblExperiments.ExperimentID) = " & ExpID & ")) ORDER BY tblSamples.SampleNumber"
 
 
DoCmd.OpenReport "rptGenerated", acPreview
 
Exit_cmdPreviewReport_Click:
    Exit Sub
 
Err_cmdPreviewReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewReport_Click
 
End Sub
 
 

and:

Private Sub Report_Open(Cancel As Integer)
 
Me!RecordSource = Forms!("frmGenerateReport")!strSQLReport
 
End Sub
 

I Get following error in the open event:

"Compile Error:
Type declaration character does not match declared data type"

Forms! is marked.

strSQLReport is a hidden field of the form. i can see, that i get the right SQL Statment.

I'm new to access, so don't now many basics. so can't i use Forms! in the open event of a report?

If not, how do i do this?

[puppydogbuddy]

Try the syntax this way and see what happens:

Me.RecordSource = Forms!frmGenerateReport!strSQLReport

or like this

Me.RecordSource = Forms("frmGenerateReport").Controls(strSQLReport)