Complicated continous form column Sum

[Kev]

Hi,

I am trying to total a column (Shift1) in a subform (continous forms)
from the after update event of the (Shift1) column control within the
subform. This column stores shift codes, I want to sum the hours
associated with these shift codes from the RosterShifts Table.
ie. Shift1 may = D
The D shift is 8 hours long

I am using Dictionary objects within the form as suggested by Gord,
this works well for totalling the hours for a record. I was wondering
if I could use a similar approach for summing the hours for all
records for Shift1.
I have a Public Function called CalcTotalTime2() in the afterupdate
event for shift1 please see below - this will be used for all shifts
if it works.

I am getting the error Database can't find the field '|" referred in
your expression.

It seems to be related to my recordset reference as:

Dim frm As Form
Set frm = Me.Form

With frm.RecorsetClone
Set rs = Me!RecordsetClone
With rs

I have tried a few options does someone know where I am going wrong?
Cheers
Kevin
Private Sub Form_Load() ' Kindly supplied by GORD
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim ShiftRecord As Dictionary

Set ShiftData = New Dictionary

Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("SELECT ShiftID, ShiftDescription,
ShiftPaidTime " & "FROM RosterShifts", dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescription", rst!ShiftDescription.Value
.Add "ShiftPaidTime", rst!ShiftPaidTime.Value
End With
ShiftData.Add rst!ShiftID.Value, ShiftRecord
Set ShiftRecord = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cdb = Nothing

End Sub

Public Function CalcTotalTime2()
Dim rs As DAO.Recordset
Dim lngTotal As Long, i As Integer, strCtlName As String, strShiftID
As String
Dim dayTotal As Long
lngTotal = 0

For i = 1 To 28
strCtlName = "Shift" & Format(i, "00")
If Not IsNull(Me.Controls(strCtlName).Value) Then
strShiftID = Me.Controls(strCtlName).Value
lngTotal = lngTotal + ShiftData(strShiftID)("ShiftPaidTime")
End If
Next
Me.TotalTime.Value = lngTotal
lngTotal = 0

Dim strControlName As String, strContValue As String
Dim CrntCtl As Control
Set CrntCtl = Screen.ActiveControl
strControlName = CrntCtl.Name

Dim frm As Form
Set frm = Me.Form

With frm.RecorsetClone
'Set rs = Me!RecordsetClone
'With rs
Do Until .EOF
DoCmd.GoToRecord acActiveDataObject, , acFirst
lngTotal = lngTotal + ShiftData(strControlName)("ShiftPaidTime")
DoCmd.GoToRecord acActiveDataObject, , acNext
lngTotal = lngTotal + ShiftData(strShiftID)("ShiftPaidTime")
Loop
SumShift1 = lngTotal

End With
End Function

[Don Leverton]

Hi Kev,

To answer your question:
=================
I think your problem is with the way you have set up your RecordsetClone.
Me.RecordsetClone returns a recodset based on the main form, not the
subform.
Me![subform name here].Form.RecordsetClone should do the trick as far as
that goes.

I'm an Access 97 user, so I don't know anything about "Dictionaries" ...
I'm a little confused by the fact that you seem to be mixing form object
references in with your recordset.
It also appears that you have 28 (unbound text-boxes?) named "Shift01" to
"Shift28" ... what's the purpose there?

Anyway:
ALTERNATE SOLUTION :
====================
Couldn't this be done by simply using a DSum() of ShiftPaidTime?

I'd suggest creating two unbound controls on the main form:

1.) An unbound combo-box (cboShiftID) to choose a ShiftID value (i.e A, B,
C, D...) from
Use this as the Row Source property :
SELECT DISTINCT RosterShifts.ShiftID FROM RosterShifts
ORDER BY RosterShifts.ShiftID;

2.) An unbound textbox (txtSumShiftTime) with this as it's Control Source:
=DSum("ShiftPaidTime","RosterShifts","([ShiftID] = '" & Me![cboShiftID] &
"')")

If "txtShiftTime" doesn't automatically recalculate it's value after
entering a value in the subform control, you may have to trigger a "Refresh"
(or "Repaint"?) of the main form in your AfterUpdate() event.

==========================
--
HTH,
Don
=============================
E-Mail (if you must) My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================
"Kev" <ke***********@nhw.hume.org.auwrote in message
news:11**********************@w1g2000hsg.googlegro ups.com...

Hi,

I am trying to total a column (Shift1) in a subform (continous forms)
from the after update event of the (Shift1) column control within the
subform. This column stores shift codes, I want to sum the hours
associated with these shift codes from the RosterShifts Table.
ie. Shift1 may = D
The D shift is 8 hours long

I am using Dictionary objects within the form as suggested by Gord,
this works well for totalling the hours for a record. I was wondering
if I could use a similar approach for summing the hours for all
records for Shift1.
I have a Public Function called CalcTotalTime2() in the afterupdate
event for shift1 please see below - this will be used for all shifts
if it works.

I am getting the error Database can't find the field '|" referred in
your expression.

It seems to be related to my recordset reference as:

Dim frm As Form
Set frm = Me.Form

With frm.RecorsetClone
Set rs = Me!RecordsetClone
With rs

I have tried a few options does someone know where I am going wrong?
Cheers
Kevin
Private Sub Form_Load() ' Kindly supplied by GORD
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim ShiftRecord As Dictionary

Set ShiftData = New Dictionary

Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("SELECT ShiftID, ShiftDescription,
ShiftPaidTime " & "FROM RosterShifts", dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescription", rst!ShiftDescription.Value
.Add "ShiftPaidTime", rst!ShiftPaidTime.Value
End With
ShiftData.Add rst!ShiftID.Value, ShiftRecord
Set ShiftRecord = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cdb = Nothing

End Sub

Public Function CalcTotalTime2()
Dim rs As DAO.Recordset
Dim lngTotal As Long, i As Integer, strCtlName As String, strShiftID
As String
Dim dayTotal As Long
lngTotal = 0

For i = 1 To 28
strCtlName = "Shift" & Format(i, "00")
If Not IsNull(Me.Controls(strCtlName).Value) Then
strShiftID = Me.Controls(strCtlName).Value
lngTotal = lngTotal + ShiftData(strShiftID)("ShiftPaidTime")
End If
Next
Me.TotalTime.Value = lngTotal
lngTotal = 0

Dim strControlName As String, strContValue As String
Dim CrntCtl As Control
Set CrntCtl = Screen.ActiveControl
strControlName = CrntCtl.Name

Dim frm As Form
Set frm = Me.Form

With frm.RecorsetClone
'Set rs = Me!RecordsetClone
'With rs
Do Until .EOF
DoCmd.GoToRecord acActiveDataObject, , acFirst
lngTotal = lngTotal + ShiftData(strControlName)("ShiftPaidTime")
DoCmd.GoToRecord acActiveDataObject, , acNext
lngTotal = lngTotal + ShiftData(strShiftID)("ShiftPaidTime")
Loop
SumShift1 = lngTotal

End With
End Function