Populate unbound field after On Click event

N

Nick X

Hi all,
I need to populate my fields after I enter a date range and click my "Apply
Filter" button. Here's what I have

DoCmd.ApplyFilter "fltBilling"
Me.SumFee = Sum([Fee])
Me.SumFine = Sum([Fine])
Me.GrandTotal = Sum([Fee] + [Fine])

It tells me "sub or function not defined" and points at the first "Sum"
{Sum([Fee])}
Many thanks in advance,
NickX
 
K

Klatuu

There is no Sum function in Access. Yes, there is a Sum in Access SQL and a
Sum operator in reports, but not in VBA.
 
N

Nick X

Thanks for your reply,
I changed it to a DSum and got a new error:

Me!SumFee = DSum("[Fee]", "[tblCuts]", "[DateCalled] between #" & BeginDate
& "# and #" & EndDate & "#")
"Run-Time Error '2448'"
"You can't assign a value to this object"

This is what I started with:
= IIf([EndDate] = "", "", Sum([Fine]))
This was in the control source of the text box, but instead of occuring
after the update of "EndDate" I would like it to happen on the "OnClick"
event of the cmd button.
Thanks,
NickX

Klatuu said:
There is no Sum function in Access. Yes, there is a Sum in Access SQL and a
Sum operator in reports, but not in VBA.

Nick X said:
Hi all,
I need to populate my fields after I enter a date range and click my "Apply
Filter" button. Here's what I have

DoCmd.ApplyFilter "fltBilling"
Me.SumFee = Sum([Fee])
Me.SumFine = Sum([Fine])
Me.GrandTotal = Sum([Fee] + [Fine])

It tells me "sub or function not defined" and points at the first "Sum"
{Sum([Fee])}
Many thanks in advance,
NickX
Click to expand...
Click to expand...
 
K

Klatuu

Sorry, I don't see the problem. There are two things I would check for
starters.
First, try assigning a literal value at the same point in the code to see if
it is a problem with assigning a value to the control:
Me!SumFee = 99.4

Second, I would check to be sure both dates on the form have a value before
trying to use them.

Nick X said:
Thanks for your reply,
I changed it to a DSum and got a new error:

Me!SumFee = DSum("[Fee]", "[tblCuts]", "[DateCalled] between #" & BeginDate
& "# and #" & EndDate & "#")
"Run-Time Error '2448'"
"You can't assign a value to this object"

This is what I started with:
= IIf([EndDate] = "", "", Sum([Fine]))
This was in the control source of the text box, but instead of occuring
after the update of "EndDate" I would like it to happen on the "OnClick"
event of the cmd button.
Thanks,
NickX

Klatuu said:
There is no Sum function in Access. Yes, there is a Sum in Access SQL and a
Sum operator in reports, but not in VBA.

Nick X said:
Hi all,
I need to populate my fields after I enter a date range and click my "Apply
Filter" button. Here's what I have

DoCmd.ApplyFilter "fltBilling"
Me.SumFee = Sum([Fee])
Me.SumFine = Sum([Fine])
Me.GrandTotal = Sum([Fee] + [Fine])

It tells me "sub or function not defined" and points at the first "Sum"
{Sum([Fee])}
Many thanks in advance,
NickX
Click to expand...
Click to expand...
Click to expand...
 
N

Nick X

Sorry, I forgot to change my text boxes back to unbound. That took care of
the error: "You can't assign a value to this object". Now it doesn't display
the sums properly. Here is the complete code:
Private Sub cmdfltBilling_Click()

If IsNull([BeginDate]) Or IsNull([EndDate]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "BeginDate"
Else
If [BeginDate] > [EndDate] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "BeginDate"
Else
DoCmd.ApplyFilter "fltBilling"
Me!SumFee = DSum("[Fee]", "[tblCuts]", "[DateCalled] between #"
& BeginDate & "# and #" & EndDate & "#")
Me!Fine = DSum("[Fine]", "[tblCuts]", "[DateCalled] between #" &
BeginDate & "# and #" & EndDate & "#")
Me!GrandTotal = DSum("[Fee] + [Fine]", "[tblCuts]",
"[DateCalled] between #" & BeginDate & "# and #" & EndDate & "#")
End If
End If

End Sub

In "Fee" the value is $117,350.40, it should be $21,657.60
"Fine" = $70.00, should be $70.00
In "GrandTotal" the value is $117,420.40, it should be $21,727.60
I'm getting closer, do you have any ideas?
Thanks
NickX
 
N

Nick X

Figured it out, I was using the wrong domain.

"Private Sub cmdfltBilling_Click()

If IsNull([BeginDate]) Or IsNull([EndDate]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "BeginDate"
Else
If [BeginDate] > [EndDate] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "BeginDate"
Else
DoCmd.ApplyFilter "fltBilling"
Me!SumFee = DSum("[Fee]", "[fltBilling]",
"[tblCuts].[UtilityID]=" & Me![JobID])
Me!SumFine = DSum("[Fine]", "[fltBilling]",
"[tblCuts].[UtilityID]=" & Me![JobID])
Me!GrandTotal = DSum("[Fee] + [Fine]", "[fltBilling]",
"[tblCuts].[UtilityID]=" & Me![JobID])
End If
End If

End Sub
 
K

Klatuu

Glad you got it working. I do have one suggestion. I notice you are using a
third DSum to get the grand total. You could make this execute a lot faster
by removing the third DSum and just adding the values in the other two
controls.

Instead of:
Me!GrandTotal = DSum("[Fee] + [Fine]", "[fltBilling]",
"[tblCuts].[UtilityID]=" & Me![JobID])

Use:
Me.GrandTotal = Me.SumFee + Me.SumFine
 
K

Klatuu

It occured to me there is even a better, faster way. Rather than two
DLookups, one pass with a recordset will do the job.

First, dim a recordset object
Dim rst As Recordset

Then
Set rst = CurrentDb.OpenRecordset("SELECT Sum([Fee]) As TotFees,
Sum([Fine]) As TotFines FROM fltBillint WHERE <put your filtering criteria
here>;")
Me.SumFee = rst![TotFees]
Me.SumFine = rst![TotFines]
Me.GrandTotal = rst![TotFees] + rst![TotFines]
rst.Close
Set rst = Nothing
 
Top