You're not troublesome; it's my pleasure.
Before we get into the code, which had several errors, I have several
comments regarding your tables.
- You do not need or want a invoicetotal *field* or a balance due *field* in
tblinvoicepayments for the reasons I mentioned earlier about storing
calculated fields. These calculated fields are merely displayed in form
*controls*, and may be recalculated for a report in a query.
- Each table's fields should describe an attribute of the "thing" the table
represents. Invoicetotal, Balance Due, InvoiceDate, and InvoiceNumber have
nothing to do with a payment, they are either calculated fields or attributes
of an Invoice. The only field you need in tblInvoicePayments to relate it to
tblInvoices is the latter's primary key (called a foreign key in
tblInvoicePayments). This field must be the same type. Assuming that
invoiceid is the primary key--invoiceid in tblinvoices and tblinvoicepayments
do not match in type; it is a Long Integer in the former and Text in the
latter.
This general subject is called table normalization--I encourage you to read
some about from a good reference. It's very common for new users to
duplicate fields this way, and it makes it much more difficult to use the
database and program form code when the tables are not normalized. Also note
the difference, alluded to earlier, between a FIELD, which has a data type,
and is where data is STORED in a table, and a CONTROL on a form or report,
which has no datatype, and is merely a container to display a field, static
text, calculation, function result, etc.
- I don't understand why you have invoiceid, invoicenum, and invoicenumber
fields in tblinvoices. They presumably do the same thing.
- Company, BillingAddr1, and the remainder of the company fields do not
belong in the tblInvoices table; they are attributes of Companys.
Analogously to the example above, all you need is a foreign key corresponding
to Company's primary key, e.g., CustomerNumber. The common way to do this is
with a combo box that permits selection by company name, but *stores* the
customer number in the underlying field.
- Normally in an invoice details table, there would be a unit price and a
quantity, and the extended price would merely be a calculated control on the
form. I don't see a Qty field.
I strongly urge you to read up on table normalization, and normalize your
tables before attempting to go further.
In the code, the second and third lines refer to "inviocetotal". this
should be "invoicetotal", as it is correctly spelled in the MyNewBalance
assignment statement later in the function.
Also the table where you are looking up Payment Amount is in the
tblinvoicepayments table, not tblInvoices.
Some minor comments: using a prefix to identify the type of all variables
is very valuable in debugging your code. Bracket delimiters around objects
(table and field, for example) are similarly helpful. Also, since the If
statement checks for the null condition, the Nz call can be removed from the
Dsum call in the Else statement. Making these changes, the resulting code is:
Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]
If Nz([paymentdate]) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call
MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
In order to update after you've entered the payment amount, place:
Me.Recalc
in its AfterUpdate event procedure.
Hope that will resolve it; but if not, don't hesitate to ask.
Sprinks
Brook said:
I'm sorry that I am so troublesome with this, but thanks for all your help..
one question that I had is that previously we had passed the invoicetotal to
the subform invoicepayments, should I change that back to invoicetotal or
keep it as (=Parent.Forms!invoicetotal?
the code is working when I enter 1 payment, then exit the form then come
back, the balance has changed.
however, when I add a new payment, the code doesn't deduct the new payment
from the previous balance...
Here is what I changed the code to based on my tbl & frm values:
Private Function MyNewBalance() As Currency
Dim inviocetotal As Currency
inviocetotal = Me.Parent.Form!invoicetotal
If Nz([paymentdate]) = 0 Then
' Set to zero to avoid if no date has yet been entered to avoid triggering
' an error in the Dsum call
MyNewBalance = 0
Else
MyNewBalance = invoicetotal - DSum("[PaymentAmount]", "tblinvoices", _
"[PaymentDate] <= #" & Nz([paymentdate]) & "#")
End If
End Function
Here are my 3 tables and field names:
3 tables:
tblinvoices:
Fields & Types
invoiceid Long Integer
invoicetype Text
invoicenum Long Integer
invoicenumber Text
invoicedate Date/Time
Company Text
Contact Text
BillingAddr1 Text
BillingAddr2 Text
BillingAddr3 Text
BillingCity Text
BillingState Text
BillingZip Text
Phone Text
Fax Text
ShippingAddr1 Text
ShippingAddr2 Text
ShippingAddr3 Text
ShippingCity Text
ShippingState Text
ShippingZip Text
ShippingCompany Text
tblinvoicedetails:
Fields
invoiceid Long Integer
orderid Long Integer
serialnumber Text
DesignNumber Text
DesignName Text
Quality Text
Size Text
SqFt Text
PricePerSqFoot Currency
TotalPrice Currency
shippingcost Currency
tblinvoicepayments:
Fields:
invoiceid Text
invoicedate Date/Time
invoicenumber Text
invoicetotal Currency
paymentnumber Long Integer
paymentdate Date/Time
paymentamount Currency
Balance Due Currency
