Assign Value to a Report Control (97)

Topic

New Reply

Stepping through the following code in debug, the two variables intHourWorked and intHeadCount do correctly accumulate the values I need. However, when I get to the lines where I try to plug the values into the respective controls on the report, I get a run-time error ‘2448’ (You can’t assign a value to this object) grrrrrr,

What is the correct method of assigning a value?

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim rs As Recordset
Dim dbs As Database
Dim strSQL As String
Dim intHeadCount, intHourWorked As Long
Set dbs = CurrentDb()
strSQL = “SELECT DISTINCT tblYearEnd.fkID, tblYearEnd.intYrTtlHours, ” & _
“tblYearEnd.intYrHdCount FROM tblYearEnd;”
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
intHeadCount = 0
intHourWorked = 0
Do While Not rs.EOF
‘ intYrTtlHours is Total Hours worked for a specific ID
intHourWorked = intHourWorked + rs!intYrTtlHours
‘ intYrHdCount is Head count for a specific ID
intHeadCount = intHeadCount + rs!intYrHdCount
rs.MoveNext
Loop
‘ txtGTtlHoursWorked and txtGTtlNoEmployees are fields in the report footer
txtGTtlHoursWorked = intHourWorked
txtGTtlNoEmployees = intHeadCount

End Sub

Any help is greatly appreciated.

TIA,

Ken

Reply | Quote

Replies

I don’t think you need any code here.

If tblYearEnd is the record source of the report, you can set the control source of txtGTtlHoursWorked to =Sum([intYrTtlHours]) and that of txtGTtlNoEmployees to =Sum([intYrHdCount]).

Otherwise, set the control source of txtGTtlHoursWorked to =DSum(“intYrTtlHours”,”tblYearEnd”) and that of txtGTtlNoEmployees to =DSum(“intYrHdCount”,”tblYearEnd”).

Reply | Quote

Thanks Hans. That is a lot simpler than the Loop code I had.

Although the tblYear is not the record source of the report, was able to create and save a query (using the sql statement in the code), and am now using that saved query as the record source. Hence:
txtGTtlHoursWorked =DSum(“intYrTtlHours”,”sqIDHrHdCnt”)
works fine where “sqIDHrHdCnt” is the saved query.

However, is there a way of replacing the saved query with code and an SQL statement? I tried various combinations of:
txtGTtlHoursWorked =DSum(“intYrTtlHours”,”strSQL”) and
txtGTtlHoursWorked =DSum(“intYrTtlHours”,strSQL)
where strSQL = “SELECT DISTINCT tblYearEnd.fkID, tblYearEnd.intYrTtlHours, ” & _
“tblYearEnd.intYrHdCount FROM tblYearEnd;”

and also tried
txtGTtlHoursWorked =DSum(“intYrTtlHours”,”SELECT DISTINCT tblYearEnd.fkID, tblYearEnd.intYrTtlHours, ” & _
“tblYearEnd.intYrHdCount FROM tblYearEnd;”)

None of these three approaches were successful. It is probably very simple, and punctuation out of place, but I can’t see it.

If not possible, I can live with the existing fix.

Thanks again Hans for sharing your ideas.

Ken

Reply | Quote

Domain functions such as DSum only work with tables and stored queries, not with SQL strings. Sorry.

Reply | Quote

Domain functions such as DSum only work with tables and stored queries, not with SQL strings. Sorry.

Reply | Quote

Thanks Hans. That is a lot simpler than the Loop code I had.

Although the tblYear is not the record source of the report, was able to create and save a query (using the sql statement in the code), and am now using that saved query as the record source. Hence:
txtGTtlHoursWorked =DSum(“intYrTtlHours”,”sqIDHrHdCnt”)
works fine where “sqIDHrHdCnt” is the saved query.

However, is there a way of replacing the saved query with code and an SQL statement? I tried various combinations of:
txtGTtlHoursWorked =DSum(“intYrTtlHours”,”strSQL”) and
txtGTtlHoursWorked =DSum(“intYrTtlHours”,strSQL)
where strSQL = “SELECT DISTINCT tblYearEnd.fkID, tblYearEnd.intYrTtlHours, ” & _
“tblYearEnd.intYrHdCount FROM tblYearEnd;”

and also tried
txtGTtlHoursWorked =DSum(“intYrTtlHours”,”SELECT DISTINCT tblYearEnd.fkID, tblYearEnd.intYrTtlHours, ” & _
“tblYearEnd.intYrHdCount FROM tblYearEnd;”)

None of these three approaches were successful. It is probably very simple, and punctuation out of place, but I can’t see it.

If not possible, I can live with the existing fix.

Thanks again Hans for sharing your ideas.

Ken

Reply | Quote

I don’t think you need any code here.

If tblYearEnd is the record source of the report, you can set the control source of txtGTtlHoursWorked to =Sum([intYrTtlHours]) and that of txtGTtlNoEmployees to =Sum([intYrHdCount]).

Otherwise, set the control source of txtGTtlHoursWorked to =DSum(“intYrTtlHours”,”tblYearEnd”) and that of txtGTtlNoEmployees to =DSum(“intYrHdCount”,”tblYearEnd”).

Reply | Quote