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.RecorsetClo ne
Set rs = Me!RecordsetClo ne
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.OpenRecords et("SELECT ShiftID, ShiftDescriptio n,
ShiftPaidTime " & "FROM RosterShifts", dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescripti on", rst!ShiftDescri ption.Value
.Add "ShiftPaidTime" , rst!ShiftPaidTi me.Value
End With
ShiftData.Add rst!ShiftID.Val ue, 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.Contr ols(strCtlName) .Value) Then
strShiftID = Me.Controls(str CtlName).Value
lngTotal = lngTotal + ShiftData(strSh iftID)("ShiftPa idTime")
End If
Next
Me.TotalTime.Va lue = lngTotal
lngTotal = 0



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

Dim frm As Form
Set frm = Me.Form

With frm.RecorsetClo ne
'Set rs = Me!RecordsetClo ne
'With rs
Do Until .EOF
DoCmd.GoToRecor d acActiveDataObj ect, , acFirst
lngTotal = lngTotal + ShiftData(strCo ntrolName)("Shi ftPaidTime")
DoCmd.GoToRecor d acActiveDataObj ect, , acNext
lngTotal = lngTotal + ShiftData(strSh iftID)("ShiftPa idTime")
Loop
SumShift1 = lngTotal

End With
End Function

[Don Leverton]

Re: Complicated continous form column Sum

Hi Kev,

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

I'm an Access 97 user, so I don't know anything about "Dictionari es" ...
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.Sh iftID FROM RosterShifts
ORDER BY RosterShifts.Sh iftID;

2.) An unbound textbox (txtSumShiftTim e) with this as it's Control Source:
=DSum("ShiftPai dTime","RosterS hifts","([ShiftID] = '" & Me![cboShiftID] &
"')")

If "txtShiftTi me" 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.Name@Telus.n et

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" <kevin.vaughan@ nhw.hume.org.au wrote in message
news:1175750421 .077412.103760@ w1g2000hsg.goog legroups.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.RecorsetClo ne
Set rs = Me!RecordsetClo ne
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.OpenRecords et("SELECT ShiftID, ShiftDescriptio n,
ShiftPaidTime " & "FROM RosterShifts", dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescripti on", rst!ShiftDescri ption.Value
.Add "ShiftPaidTime" , rst!ShiftPaidTi me.Value
End With
ShiftData.Add rst!ShiftID.Val ue, 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.Contr ols(strCtlName) .Value) Then
strShiftID = Me.Controls(str CtlName).Value
lngTotal = lngTotal + ShiftData(strSh iftID)("ShiftPa idTime")
End If
Next
Me.TotalTime.Va lue = lngTotal
lngTotal = 0
>
>
>
Dim strControlName As String, strContValue As String
Dim CrntCtl As Control
Set CrntCtl = Screen.ActiveCo ntrol
strControlName = CrntCtl.Name
>
Dim frm As Form
Set frm = Me.Form
>
With frm.RecorsetClo ne
'Set rs = Me!RecordsetClo ne
'With rs
Do Until .EOF
DoCmd.GoToRecor d acActiveDataObj ect, , acFirst
lngTotal = lngTotal + ShiftData(strCo ntrolName)("Shi ftPaidTime")
DoCmd.GoToRecor d acActiveDataObj ect, , acNext
lngTotal = lngTotal + ShiftData(strSh iftID)("ShiftPa idTime")
Loop
SumShift1 = lngTotal
>
End With
End Function
>