Problem with sum not refreshing

[Guest]

Hi

In a subform I have cbo_BodyType containing BodyType, Prm1, Prm2 and this
populates the unbound control MV_PRM with either Prm1 or Prm2 depending on
the body type selected and which state the debtor is based in. The control
source for MV_PRM is
=IIf([Forms]![frm_MV].[STATE]="NSW",[cbo_BodyType].[Column](3),[cbo_BodyType].[Column](2)).

Okay, I know I can't sum an unbound control, therefore I have Prm1 and Prm2
as controls on the subform, but hidden and I sum these and show the relevant
sum via the following:
MVPRMTOTAL = IIf([Forms]![frm_MV].[STATE]="NSW",Sum([Prm1]),Sum([Prm2]))

My problem is thus: if the body type is changed in the form, MV_PRM updates
fine, but MVPRMTOTAL doesn't update with the new total, until I click to a
new record and return back to it. See the following as an example, (and for
ease of explanation I’ll leave out the state criteria):

Vehicle BodyType Prm
1 Sedan $100
2 Truck $200

The sum should therefore be $300. However, if I change the body type of
vehicle 1 to Truck, the sum should be $400, yet in my case it stays at $300.

I tried to put the condition in the underlying record source using the
following:

SELECT tbl_MVDETAILS.MVPOLICY_IDX, tbl_MVDETAILS.MV_LINE,
tbl_MVDETAILS.MV_REGO, tbl_MVPRMS.MV_BODY, CASE tbl_DEBTORS.STATE WHEN 'NSW'
THEN tbl_MVPRMS.MV_PRM1 ELSE tbl_MVPRMS.MV_PRM2 END AS MV_PRM, FROM
tbl_MVDETAILS INNER JOIN tbl_MVPRMS ON tbl_MVDETAILS.MV_PRM_IDX =
tbl_MVPRMS.MV_PRM_IDX INNER JOIN tbl_MVMASTER ON tbl_MVMASTER.MVPOLICY_IDX =
tbl_MVDETAILS.MVPOLICY_IDX
INNER JOIN tbl_DEBTORS ON tbl_DEBTORS.DEBTOR_IDX = tbl_MVMASTER.DEBTOR_IDX

and then had MV_PRM as just a text box, not a control populated via the
combo box. Then my problem got worse when neither MV_PRM OR MVPRMTOTAL would
update when I changed the body type!!

For clarification, I can’t add a sum column to the query as one of my
columns is a Memo field and I get the error that I can’t sum a Memo field.

Any help is appreciated!!!

Winsa

 

[John Vinson]

and then had MV_PRM as just a text box, not a control populated via the
combo box. Then my problem got worse when neither MV_PRM OR MVPRMTOTAL would
update when I changed the body type!!

Requery the controls in the AfterUpdate event of the body type combo.

For clarification, I can’t add a sum column to the query as one of my
columns is a Memo field and I get the error that I can’t sum a Memo field.

Use the First() aggregate function for the Memo field (if it makes no
difference which record's memo you see - if you have multiple memos,
and want to sum another field, you're stuck doing as above).

John W. Vinson[MVP]

 

[Guest]

Hi John

Thanks for your suggestions.

I tried to requery MVTTLPRM in the AfterUpdate Event of cbo_BodyType, but I
get #Error even before I do anything to cbo_BodyType.

As for First(), unfortunately it does matter which records' memo I see, so
I'll have to stick with the first option.

Any other ideas?

Thanks
Winsa