?Dsum only calculate 1st record not subsequents

[Guest]

I have a master form with 3 main fields: (1)LeaveEntitle (2)Leavetook (3)
Leaveleft

I would like to sum total Leave days recorded in a related subform, and
return this sum as Leavetook in master form.
I have 2 problems:-
(1) The Dsum only sum 1st record but not subsequent records
(2) In some records, LeaveEnd=LeaveStart, then I will have 0 LeaveTaken for
these records. Pls advice how to fix the problem

The following is my inexperienced coding:-

Private Sub TxSumDaysTaken_GotFocus()

Dim Lvtk As String

Lvtk = DSum("[LeaveEnd]", "TbTempLeave",
"[EmployeeID]=[forms]![FmMasterLeave]!EmployeeID") - DSum("[Leavestart]",
"TbTempLeave", "[EmployeeID]=[forms]![FmMasterLeave]!EmployeeID")
Me!TxSumDaysTaken = Lvtk
-
Thanks a million for your time and expert advice
Jaylin
*****Jaylin Message ended*******

 

[tina]

suggest you add an unbound textbox control to the subform's Footer section.
name it "txtSumLeave". you can set the textbox's Visible property to No if
you don't want the user to see it. set the textbox's ControlSource to

=Sum(IIf([Leavestart]=[LeaveEnd], 1, DateDiff("d",[Leavestart],
[LeaveEnd])))

change the code in the main form to

Private Sub TxSumDaysTaken_GotFocus()

Me!TxSumDaysTaken = Me!SubformControlName!txtSumLeave

note: make sure you use the name of the subform *control* in the main
form - not the name of the subform as it shows in the list of forms in the
database window. sometimes the two names are the same, but sometimes they're
different.

and btw, just as an fyi, when you refer to a form control in a domain
aggregate function (DSum, in this case), the correct syntax is

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& Forms!FmMasterLeave!EmployeeID)

if the field is a Text data type, rather than a Number (or Autonumber) data
type, the correct syntax is

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = '" _
& Forms!FmMasterLeave!EmployeeID & "'")

and finally, if the control you're refering to is on the same form as the
expression, you can simply use the control name without the full reference,
as

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& Me!EmployeeID)

the above syntax is correct for VBA. if you're using the expression in a
calculated control on the form, the syntax would be

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& [EmployeeID])

hth

 

[Guest]

Dear Ms Tina,
Thank you very much for your expert advice.

Hope you would not mind to guide me further. When I create the textbx on
footer of the subform, it only calcute date diff of first entry. When I input
the next record, the "TxSumDaysTaken" does not change.

Thanks so much for your the syntax guideline. It is very useful for me. I
am able to fix some of the problem I encounter.

--
Very much appreciate your time and help
Jaylin
*****Jaylin Message ended*******

suggest you add an unbound textbox control to the subform's Footer section.
name it "txtSumLeave". you can set the textbox's Visible property to No if
you don't want the user to see it. set the textbox's ControlSource to

=Sum(IIf([Leavestart]=[LeaveEnd], 1, DateDiff("d",[Leavestart],
[LeaveEnd])))

change the code in the main form to

Private Sub TxSumDaysTaken_GotFocus()

Me!TxSumDaysTaken = Me!SubformControlName!txtSumLeave

note: make sure you use the name of the subform *control* in the main
form - not the name of the subform as it shows in the list of forms in the
database window. sometimes the two names are the same, but sometimes they're
different.

and btw, just as an fyi, when you refer to a form control in a domain
aggregate function (DSum, in this case), the correct syntax is

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& Forms!FmMasterLeave!EmployeeID)

if the field is a Text data type, rather than a Number (or Autonumber) data
type, the correct syntax is

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = '" _
& Forms!FmMasterLeave!EmployeeID & "'")

and finally, if the control you're refering to is on the same form as the
expression, you can simply use the control name without the full reference,
as

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& Me!EmployeeID)

the above syntax is correct for VBA. if you're using the expression in a
calculated control on the form, the syntax would be

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& [EmployeeID])

hth

I have a master form with 3 main fields: (1)LeaveEntitle (2)Leavetook (3)
Leaveleft

I would like to sum total Leave days recorded in a related subform, and
return this sum as Leavetook in master form.
I have 2 problems:-
(1) The Dsum only sum 1st record but not subsequent records
(2) In some records, LeaveEnd=LeaveStart, then I will have 0 LeaveTaken for
these records. Pls advice how to fix the problem

The following is my inexperienced coding:-

Private Sub TxSumDaysTaken_GotFocus()

Dim Lvtk As String

Lvtk = DSum("[LeaveEnd]", "TbTempLeave",
"[EmployeeID]=[forms]![FmMasterLeave]!EmployeeID") - DSum("[Leavestart]",
"TbTempLeave", "[EmployeeID]=[forms]![FmMasterLeave]!EmployeeID")
Me!TxSumDaysTaken = Lvtk
-
Thanks a million for your time and expert advice
Jaylin
*****Jaylin Message ended*******

 

[tina]

the control TxSumDaysTaken is on the main form, correct? and the code that
updates it runs on the control's GotFocus event. so the value won't be
updated until you move the focus to that control - regardless of when or how
many records you enter or edit in the subform. if you want the main form
control to update when you enter or edit records in the subform, then run
the update code from the subform.

suggest you remove the code from the GotFocus event of the TxSumDaysTaken
control, and instead add the following code to the *subform's* AfterUpdate
event, as

Private Sub Form_AfterUpdate()

Me.Parent!TxSumDaysTaken = Me!txtSumLeave

End Sub

hth

Dear Ms Tina,
Thank you very much for your expert advice.

Hope you would not mind to guide me further. When I create the textbx on
footer of the subform, it only calcute date diff of first entry. When I input
the next record, the "TxSumDaysTaken" does not change.

Thanks so much for your the syntax guideline. It is very useful for me. I
am able to fix some of the problem I encounter.

--
Very much appreciate your time and help
Jaylin
*****Jaylin Message ended*******

suggest you add an unbound textbox control to the subform's Footer section.
name it "txtSumLeave". you can set the textbox's Visible property to No if
you don't want the user to see it. set the textbox's ControlSource to

=Sum(IIf([Leavestart]=[LeaveEnd], 1, DateDiff("d",[Leavestart],
[LeaveEnd])))

change the code in the main form to

Private Sub TxSumDaysTaken_GotFocus()

Me!TxSumDaysTaken = Me!SubformControlName!txtSumLeave

note: make sure you use the name of the subform *control* in the main
form - not the name of the subform as it shows in the list of forms in the
database window. sometimes the two names are the same, but sometimes they're
different.

and btw, just as an fyi, when you refer to a form control in a domain
aggregate function (DSum, in this case), the correct syntax is

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& Forms!FmMasterLeave!EmployeeID)

if the field is a Text data type, rather than a Number (or Autonumber) data
type, the correct syntax is

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = '" _
& Forms!FmMasterLeave!EmployeeID & "'")

and finally, if the control you're refering to is on the same form as the
expression, you can simply use the control name without the full reference,
as

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& Me!EmployeeID)

the above syntax is correct for VBA. if you're using the expression in a
calculated control on the form, the syntax would be

DSum("[LeaveEnd]", "TbTempLeave", "[EmployeeID] = " _
& [EmployeeID])

hth

I have a master form with 3 main fields: (1)LeaveEntitle (2)Leavetook (3)
Leaveleft

I would like to sum total Leave days recorded in a related subform, and
return this sum as Leavetook in master form.
I have 2 problems:-
(1) The Dsum only sum 1st record but not subsequent records
(2) In some records, LeaveEnd=LeaveStart, then I will have 0

LeaveTaken
for

these records. Pls advice how to fix the problem

The following is my inexperienced coding:-

Private Sub TxSumDaysTaken_GotFocus()

Dim Lvtk As String

Lvtk = DSum("[LeaveEnd]", "TbTempLeave",
"[EmployeeID]=[forms]![FmMasterLeave]!EmployeeID") - DSum("[Leavestart]",
"TbTempLeave", "[EmployeeID]=[forms]![FmMasterLeave]!EmployeeID")
Me!TxSumDaysTaken = Lvtk
-
Thanks a million for your time and expert advice
Jaylin
*****Jaylin Message ended*******