How do I populate and calculate values for each row individually?

[DrAtsab]

Hi All

My first posting - probably of many if this week is anything to go by.

I have a child set of records that show outstanding invoices per customer showing as a subform/datasheet. I have two blank columns, one I am populating with the amount I wish to pay and the other which automatically compares the amount due with the amount I wish to pay. Unfortunately when I enter the amount I want to pay in the first row (first invoice in the set) the same figure is automatically entered into the same column for every other row.

How do I populate and calculate values for each row individually?

 

[namliam]

You dont, or not in a tabular form anyway...

That is unless you make the textbox "bound" to your table...

P.S. Welcome to the forum

 

[valheru]

Hi There

Could you post your DB so that I can get a clearer understanding of what you are trying to achieve. I assume that what you want is you want to enter the amount that you are paying, which then automatically gets deducted from the amount due. You then want to new amount due displayed in the other empty column automatically?

 

[DrAtsab]

Hi There

Could you post your DB so that I can get a clearer understanding of what you are trying to achieve. I assume that what you want is you want to enter the amount that you are paying, which then automatically gets deducted from the amount due. You then want to new amount due displayed in the other empty column automatically?

Not Quite. I have a table of transactions some of which are invoices unpaid. On my form i choose a supplier and in the subform list all the invoices that are unpaid for that supplier. The fields i show are: Invoice_Amount, Invoice_Date, Transaction_ID. I also have two other fields that are unbound to the transactions table: Payment & Difference. I wish to enter a figure into the Payment column and run an event to calculate the difference between Invoice_Amount and Payment. I want to be able to do this for each individual row in the recordset. These will be posted as paid/part paid transactions into the transaction table and will be cross referrenced usning the original Transaction_ID.

 

[gemma-the-husky]

a few ways of doing this - this doesnt include detials, just a methodology

a) separate payments table - if you have a situation where you have posted say, a rates bill, and wish to pay this off instalments, then you could use a payments table linked to the transaction record. difficulty here is you need the payments table, even when invoice is fully paid in one go, leading to a load of extra disc reads to retrieve information - possibly slowing the app down a little, depending on the size of the files

b) only store outstanding balance - store the original invoice vlaue, and the outstanding balance - as you make payments reduce the outstanding blaance - you wont have the full payment history, but you might not need it

c) deliberately DENORMALISE - decide on a maximum payments (say 12) for each transaction, and store these all within the same transaction record.


i've seen all these methods in financial systems - so its a matter of taste really

----------
now i dont think you can do what you are trying to do with an unbound field, because in a continuous form it wont show/populate correctly, i dont think - you need to have a bound field called eg thispaymentworking - then you can process this field if you accept the payment, and just ignore it if you cancel

hope this helps

 

[DrAtsab]

Thanks all for your help.