Calculating the previous balance to the new balance

[Nyla K]

I have two tables, which I use to create two forms (Order Entry and Account
Receivable). New orders are stored in the "Order Entry" table while the
"Account Receivable" table shows any pending or payments made.

My question is how can I automatically include any credits or debits in the
"Amount Due" field in the "Order Entry" form when placing new orders? For
example:
Customer A made a payment of $15.00 on a $12.00 order, having a credit of
$3.00 in the "Accounts Receivable" table. When Customer A places a new order
totalling $20.00, the "Amount Due" field in the Order Entry form will show
$17.00.

Thanks,
Nyla

 

[Klatuu]

You could use the DLookUp function to find out if there is a credit balance.
You would put it where you calculate the Amount Due on your form. The
question is in your A/R table, are credits carried as a negative number or
are they in a separate field?
You will also need to check for Null being returned so it will not mess up
your calculation. This could happen for a new customer who as yet is not in
the AR system. The function below adds the customer's current balance to the
Order. (That is what I think I read in your post). If you only want to apply
credit balances you will have to check for < 0 before you add it.

Dim sngCrDue as Single

sngCrDue = DLookup("[AcctBalanceField]","ARTableName","[CustID] = '" _
& Me.Customer & "'")
If Not IsNull(sngCrDue) Then
Me.AmtDue = Me.AmtDue + sngCrDue

 

[Nyla K]

Thank you, Klatuu.

The credit shows as a negative number. In addition, I would also like to
include any debit to the amount due field. So if a customer pays $3.00 less
than the amount due, the difference will be added to the new order amount.

Thanks again,
Nyla

You could use the DLookUp function to find out if there is a credit balance.
You would put it where you calculate the Amount Due on your form. The
question is in your A/R table, are credits carried as a negative number or
are they in a separate field?
You will also need to check for Null being returned so it will not mess up
your calculation. This could happen for a new customer who as yet is not in
the AR system. The function below adds the customer's current balance to the
Order. (That is what I think I read in your post). If you only want to apply
credit balances you will have to check for < 0 before you add it.

Dim sngCrDue as Single

sngCrDue = DLookup("[AcctBalanceField]","ARTableName","[CustID] = '" _
& Me.Customer & "'")
If Not IsNull(sngCrDue) Then
Me.AmtDue = Me.AmtDue + sngCrDue

I have two tables, which I use to create two forms (Order Entry and Account
Receivable). New orders are stored in the "Order Entry" table while the
"Account Receivable" table shows any pending or payments made.

My question is how can I automatically include any credits or debits in the
"Amount Due" field in the "Order Entry" form when placing new orders? For
example:
Customer A made a payment of $15.00 on a $12.00 order, having a credit of
$3.00 in the "Accounts Receivable" table. When Customer A places a new order
totalling $20.00, the "Amount Due" field in the Order Entry form will show
$17.00.

Thanks,
Nyla