Very Difficult Text Box Question

[jbl1167]

I do have a database that contains invoicing information. I created a form for billing entry and every bill may include a payment. Many times the payment is less than the value of the invoice and we need to carry out the balance of the invoice for the next invoice. The problem is that I do not know how to save that value. I have a text box called next_due_invoice that contains the formula for the balance but how can I save this value to the field that is supposed to have it? The users never have access to the next_due_invoice and the field is calculated automatically by the machine. I put the formula at the control source property therefore I can not put the data field there. Thank you

 

[Remou]

I am fairly certain that accounting does not work like that in any country, however, you may wish to check with your accountants. What you need are statements and invoices.

 

[jbl1167]

Thank you,

The technical question here is how could I save the value from a calculated text box into a field in a table under the circumstances that I mention when I posted the question?

Thank you

 

[VRoscioli]

In the Change event of the textbox, you could put:

[i]FieldName[/i]=[i]TextboxName[/i].Value

-V

 

[PHV]

Add a TextBox bound to the field in the underlaying table/query and then, in the BeforeUpdate event procedure of the form:
Me![new textbox] = Me![next_due_invoice]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

jbl1167 . . .

Would it not be easier on accounting to [blue]leave an invoice open until its fully paid?[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[MSWhat]

JBL,

I've got a similar situation. What I've done is set a BeforeUpdate event to run a SetValue Macro. The expression will be the formula (e.g.):

CCur([TableName].[Invoice]-[TableName].[Payment Amount])

And the item will be the column in your table that you want this calculated value to be saved to. That way data is entered into your form, calculated there, then saved to your table, so you can make reports, etc, with this calculated value.

Hope this helps. Let me know if you've got questions.

 

[randy700]

Storing a calculated value in your table is RARELY a good idea. Better to perform the calculations in a query, and base any reports on the query.


Randy

 

[lameid]

I believe what Remou is suggesting is that you need to have different detail and hence different tables for invoices and statements.

In broad terms I would think a Receivables system would have a table for all documents (invoices, payments, Credit memos etc.) and another table to apply one document to another. Pick a direction for the application... always apply credits to debits.

Then have a procedure that takes basic entry of things and finalize or post it to appropriate tables. While possible to do this in a form as mentioned, I think you will create a ton of reconciliation work.

 

[ZmrAbdulla]

What if the the customer again pay less amount than accumulated total?

You have "invoice amount" and "paid amount" fields in your table.

You can have DSum() with criteria to check total due amount difference of invoiced and paid.

a sample from the help

Dim curX As Currency
curX = DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")

________________________________________________________
Zameer Abdulla
Help to find Missing people