Update Form Values (1 Viewer)

[scouser]

Hi to all. In the attached database I have calculated fields on frmSalesOrders 'Order Balance' and subform frmPayments 'Total Payment' 'Order Balance' and hidden field 'SumPayments'.

At present users must enter a blank payment for the calculated fields to populate (see attached Payment Form Blank Entry).

I would like the values to populate without the need to enter a blank payment and for the Order Balance on frmSalesOrders to update after each Sales Order Item is entered.

Any help appreciated.
Many Thanks,
Phil.

 

[RuralGuy]

Before I open the mdb, what version of Access are you using?

 

[scouser]

Hi. I have access 2016. I can save in other versions is required. I think it may be saved in 2007-2013 format.
Thanks
Phil

 

[RuralGuy]

An .MDB indicates ac2003 or <. Do you need it as an mdb? 2007-2013 is accdb. I use mostly ac2013 but have lower versions.

 

[scouser]

I have uploaded a version saved using Access 2013 (2007 compatible)
Thanks,
Phil.

 

[scouser]

On the 'SumPayments' field I have tried the following:

=Sum(IIf([Total]=NULL,0))
=Sum(IIf([Total]="",0))
=Sum(IIf([Total]=' ',0))

The field remains blank until a payment line is added or 'Deposit' tick box is checked.. The calculated fields then populate.
Thanks
Phil.

 

[scouser]

I got there in the end..

=Nz(Sum([Total]),0)

Thanks,
Phil.

 

[RuralGuy]

It looks like you are good to go. Have a great day.

 

[scouser]

Thanks for taking time to review my post.
Phil.

 

[RuralGuy]

Sometimes all you need is a sounding post!