problem with excel-like subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

forcefield - 26 Mar 2008 14:54 GMT

I have the following:
Form1:
ItemID (autonumber)
Item (combo)

subForm1:
QtyID (autonumber)
LinkedID (number)
EntryDate (Date)
InQty (number)
OutQty (number)
Balance (unbound)

Child Field is LinkedID and Master Field is ItemID

What I would like to do is when I enter data in the InQty and OutQty in the
subform (similar to Excel) the balance field(unbound) in the subform
automatically calculates the balance (with or without VBA)? Is that possible
with form instead of report? I tried using control source of the Balance
field but the result is left much to be desired. Any idea, please?

Pdate InQty OutQty Balance
02/01/2008 30 4 26
04/01/2008 10 0 36
10/01/2008 20 6 50
12/01/2008 0 2 48

Thanks

Reply to this Message

Alex Dybenko - 26 Mar 2008 16:20 GMT

Hi,

add Balance field to subform underlying query as Balance: [InQty]-[OutQty]

and bind balance field to it

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

>I have the following:
> Form1:
[quoted text clipped - 26 lines]
>
> Thanks

Reply to this Message

Paolo - 26 Mar 2008 16:26 GMT

Hi forcefield,

In the afterupdate event of InQty and OutQty put
Balance= nz(InQty,0)-nz(OutQty,0)
Be careful, if Balance is left unbound next time you go to an ID the
balance'll be vanished so is better if you add a field to your table to save
the calculated value.

HTH Paolo

> I have the following:
> Form1:
[quoted text clipped - 24 lines]
>
> Thanks

Reply to this Message