pass variable to report

[teel73]

I'm trying to simply pass a variable to my report.

I have a report named "rptMonthlyStatistics". And a unbound control named "totCompleted". On Open event of the report I am calling a function named "getMonthlyStats". The code for that funtion is here:

Dim dbs As Database
Dim myqry As QueryDef
Dim rstCompleted As Recordset
Dim sql As String
Dim totCompleted As Integer
Dim rangeFrm As Form
Dim myStart, myEnd As String
Set dbs = CurrentDb
Set rangeFrm = Form_frmDateRange
myStart = rangeFrm!txtStart
myEnd = rangeFrm!txtEnd

sql = "SELECT Count(tblContractorDataOnly.Id) AS CountOfId " _
    & "FROM tblContractorDataOnly INNER JOIN tblBackgoundReview ON tblContractorDataOnly.Id = tblBackgoundReview.Data_ID " _
    & "WHERE(((tblBackgoundReview.BICompleted) Between #" & myStart & "# And #" & myEnd & "#) AND ((tblBackgoundReview.BIStatus)<>'EXEMPT'));"
Set myqry = dbs.CreateQueryDef("", sql)
Set rstCompleted = myqry.OpenRecordset(dbOpenSnapshot)
Reports!rptMonthlyStatistics!totCompleted = rstCompleted!CountOfID

I am simply trying to get the control on my report to display the number of records in my temp Query. "rstCompleted.countofID"

When I run my report it produces an error: "You can not assign a value to this object"

Below is the line that the code breaks on:
Reports!rptMonthlyStatistics!totCompleted = rstCompleted!CountOfID

How can I pass the value of rstCompleted.countOfID to my report?

 

[RuralGuy]

Save the value in a local variable and put it in the control in the Format event of the section where the control is located. The Open Event of a report is too early to reference a control on the report.

 

[teel73]

thank you buddy! that worked.

 

[RuralGuy]

Glad I could help.

 

[Lightwave]

Magic - this sorted the same problem out for me

 

[RuralGuy]

The gift that keeps giving. Excellent!