Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Assign Value to a Report Control (97)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign Value to a Report Control (97)

    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").

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Assign Value to a Report Control (97)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign Value to a Report Control (97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •