DSUM AND FORM UPDATE

[Guest]

I have a form and subform, where the subform displays course booking records
for the form for each customer. I display the total spend for all the
subforms for each customer in a calculated field in the main form. I use the
syntax

=DSum"[COUR_ACT_FEE]","study_leave_recs
","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs
","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")= .....
ETC.

which works ok. MY PROBLEM is that I cannot get the total field to update
in real time, even if I save the record and skip to the next customer and
then go back. I have to come out of the form and then go back in to get the
correct total. How can I refresh the form without doing this ? Have I done
something wrong?

 

[Andi Mayer]

I have a form and subform, where the subform displays course booking records
for the form for each customer. I display the total spend for all the
subforms for each customer in a calculated field in the main form. I use the
syntax

=DSum"[COUR_ACT_FEE]","study_leave_recs
","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs
","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")= .....
ETC.

which works ok. MY PROBLEM is that I cannot get the total field to update
in real time, even if I save the record and skip to the next customer and
then go back. I have to come out of the form and then go back in to get the
correct total. How can I refresh the form without doing this ? Have I done
something wrong?

what you think about a
me.refresh
and/or
[study_leave_recs].[Form].refresh

 

[Guest]

Thanks Andi. It looks good, but how do I use it? I guess it goes in a
macro, but how ?! Sorry I'm still relatively new to Access.

I have a form and subform, where the subform displays course booking records
for the form for each customer. I display the total spend for all the
subforms for each customer in a calculated field in the main form. I use the
syntax

=DSum"[COUR_ACT_FEE]","study_leave_recs
","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs
","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")= .....
ETC.

which works ok. MY PROBLEM is that I cannot get the total field to update
in real time, even if I save the record and skip to the next customer and
then go back. I have to come out of the form and then go back in to get the
correct total. How can I refresh the form without doing this ? Have I done
something wrong?

what you think about a
me.refresh
and/or
[study_leave_recs].[Form].refresh

 

[Andi Mayer]

Thanks Andi. It looks good, but how do I use it? I guess it goes in a
macro, but how ?! Sorry I'm still relatively new to Access.

i think with your massive DSum's you will ever have troubles, but try:

in the onCurrent event of the main form

me.study_leave_recs.Form.recalc
me.recalc

where study_leave_recs is the control which holds your subform

 

[Guest]

Hi Andi, thanks for this. I'm still having problems. I guess I'm greener
than you thought!! I went into the form properties and then current event. I
then clicked to open the expression builder. I pasted the syntax into the box
closed the expression builder. Nothing happened on the form when I entered
new data. Please advise. Sorry to be a pain. Thanks again.

Regards Peter

ps I only sum the 4 field columns which capture the spend data per record
using dsum. Can you suggest a better way? Thanks again.

 

[Andi Mayer]

Hi Andi, thanks for this. I'm still having problems. I guess I'm greener
than you thought!! I went into the form properties and then current event. I
then clicked to open the expression builder. I pasted the syntax into the box
closed the expression builder. Nothing happened on the form when I entered
new data. Please advise. Sorry to be a pain. Thanks again.

this was a VBA expression, therefore you have to choose the "code
builder"

ps I only sum the 4 field columns which capture the spend data per record
using dsum. Can you suggest a better way? Thanks again.

I would use SQL

check in the VBA--tools--references if you have a reference set to
Microsoft DAO

Private Sub Form_Current()
Dim SQL As String
Dim rs As DAO.Recordset
Me.study_leave_recs.Form.refresh
SQL = "SELECT Sum(study_leave_recs.COUR_ACT_FEE) AS " _
&" SumOfCOUR_ACT_FEE," _
& " Sum(study_leave_recs.COUR_ACT_TRAVEL) AS SumOfCOUR_ACT_TRAVEL" _
& " FROM study_leave_recs" _
& " GROUP BY study_leave_recs.BUDGET_HOLDER_ID" _
& " HAVING (((study_leave_recs.BUDGET_HOLDER_ID)=" _
& me.study_leave_recs.Form!budget_holder_id &"));"
Set rs = CurrentDb.OpenRecordset(SQL)
If rs.RecordCount > 0 Then
Me.Text0 = rs!SumOfCOUR_ACT_FEE + rs!SumOfCOUR_ACT_TRAVEL
Else
Me.Text0 = 0
End If
rs.Close
Set rs = Nothing
End Sub

this look complicated, but it's an easy task (I have done the same and
didn't changed anything, therefore you can follow easily)

go to the query-designer, create a new query, choose your table
trag the sum fields into the grid (like COUR_ACT_FEE) and the filter
field (BUDGET_HOLDER_ID)
right click in the grid choose functions

in the totals: choose sum (for the summing) and
Where for your filter field

to test it put a filter-Value in the condition cell of the filterField

and choose data Sheet view

now you have all the summs in a row

if the result fits you need, choose SQL-View

copy the string and past it into the sql-string in the on current
event and change the filter to the field you want to use

the refresh at the beginning takes care that the value is written back
to the table.

the recordset has now the fields you want to have and the I write the
summ of this fields into the Me.text0 control

 

[Guest]

Hi again,

I've created the query as suggested placing the fields I wanted summed.
This works ok, but how can I get to see the sum for all the totals together,
ie the

sum
(
sum(act_course_fee)+
sum(act_travel) +
Sum(act_subsistance) +
sum(act_other_expen)
)
where budget_id=????


Thanks again.

Peter

 

[Andi Mayer]

Hi again,

I've created the query as suggested placing the fields I wanted summed.
This works ok, but how can I get to see the sum for all the totals together,
ie the

sum
(
sum(act_course_fee)+
sum(act_travel) +
Sum(act_subsistance) +
sum(act_other_expen)
)
where budget_id=????

if you want to have a "single query" to sum all you have to make a
second query which uses the first query and make a single field with
your formular

in my case I have oppend a recordset which uses query1 and add the
fields in the recordset to fill the control