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.Name@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" <kevin.vaughan@nhw.hume.org.auwrote in message
news:1175750421.077412.103760@w1g2000hsg.googlegro ups.com...
Quote:
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
>
