pass a calculated form field to a subform field?

B

Brook

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



Sprinks said:
Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

Brook said:
hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
S

Sprinks

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

Brook said:
hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



Sprinks said:
Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

Brook said:
hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

Sprinks said:
Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

Brook said:
hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



Sprinks said:
Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
S

Sprinks

Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

Brook said:
Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

Sprinks said:
Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

Brook said:
hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
S

Sprinks

The code has been tested, so its internal logic is sound. The error must lie
in one of the references to your controls and/or tables. So, in the
function definition:

- Change TransactionDate to the name of your field in the table underlying
the subform as you suggested.

- Check that the fieldname in the curInvoice assignment statement is the
exact name of your field

- Check that the parameters passed to the Dsum function are correct. To
see its syntax and another example, choose View, Code from form design view,
then use VBA help on Dsum.

If you’re still getting an error, post the names of all of the relevant
controls and your modified code, and I will see if I can find it.

Hope that helps.
Sprinks


Brook said:
Ok,

So I changed my Balance Due value to =MyNewBalance(), and added the code
to the onload for the form.

When I run my form, I am getting #Error in my balance Due column?

Is there code that I should run to update the invoicetotal from the main
form to the subform on enter? I am unsure why I would be getting the error. I
do have one question though: for the transactiondate, should I replace this
with my "paymentdate"?

Thanks,

Brook

Sprinks said:
Brook,

Since the function is specific to this form, place it in its code module.
From Form Design View, select View, Code, and paste the code there, then edit
it if the field and control references aren't accurate.

Then set the calculated Balance Due control's ControlSource to

=MyNewBalance()

Hope that helps.
Sprinks

Brook said:
Thank you...

The only question I have now, is where do I place the code? Do I need to
make any changes to my form layout or tblinvoicepayments?

Thanks so much for your time and effort to help me out..

Brook

:

Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

:

Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

:

Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

:

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

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



Brook said:
I added the code like you have stated, and when I enter my form, enter a new
invoice then go to the payment subform, I am getting an #Error with the
Balance Due Column, is there something I should look at to find out the
reason behind the error?

Brook

Sprinks said:
Brook,

Since the function is specific to this form, place it in its code module.
From Form Design View, select View, Code, and paste the code there, then edit
it if the field and control references aren't accurate.

Then set the calculated Balance Due control's ControlSource to

=MyNewBalance()

Hope that helps.
Sprinks

Brook said:
Thank you...

The only question I have now, is where do I place the code? Do I need to
make any changes to my form layout or tblinvoicepayments?

Thanks so much for your time and effort to help me out..

Brook

:

Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

:

Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

:

Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

:

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
S

Sprinks

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
 
S

Sprinks

Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

Brook said:
Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

Sprinks said:
Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

Brook said:
Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

I added the code like you have stated, and when I enter my form, enter a new
invoice then go to the payment subform, I am getting an #Error with the
Balance Due Column, is there something I should look at to find out the
reason behind the error?

Brook

Sprinks said:
Brook,

Since the function is specific to this form, place it in its code module.
From Form Design View, select View, Code, and paste the code there, then edit
it if the field and control references aren't accurate.

Then set the calculated Balance Due control's ControlSource to

=MyNewBalance()

Hope that helps.
Sprinks

Brook said:
Thank you...

The only question I have now, is where do I place the code? Do I need to
make any changes to my form layout or tblinvoicepayments?

Thanks so much for your time and effort to help me out..

Brook

Sprinks said:
Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

:

Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

:

Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

:

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

Ok,

So I changed my Balance Due value to =MyNewBalance(), and added the code
to the onload for the form.

When I run my form, I am getting #Error in my balance Due column?

Is there code that I should run to update the invoicetotal from the main
form to the subform on enter? I am unsure why I would be getting the error. I
do have one question though: for the transactiondate, should I replace this
with my "paymentdate"?

Thanks,

Brook

Sprinks said:
Brook,

Since the function is specific to this form, place it in its code module.
From Form Design View, select View, Code, and paste the code there, then edit
it if the field and control references aren't accurate.

Then set the calculated Balance Due control's ControlSource to

=MyNewBalance()

Hope that helps.
Sprinks

Brook said:
Thank you...

The only question I have now, is where do I place the code? Do I need to
make any changes to my form layout or tblinvoicepayments?

Thanks so much for your time and effort to help me out..

Brook

Sprinks said:
Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

:

Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

:

Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

:

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

Hello Sprinks,

I wanted to let you know that I am going to read through your comments and
suggestions and will post a reply a little later, so be sure to check back.

Brook

Sprinks said:
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
 
B

Brook

Thank you...

The only question I have now, is where do I place the code? Do I need to
make any changes to my form layout or tblinvoicepayments?

Thanks so much for your time and effort to help me out..

Brook

Sprinks said:
Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

Brook said:
Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

Sprinks said:
Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

:

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

Sprinks said:
Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

Brook said:
Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

Sprinks said:
Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
S

Sprinks

Brook,

Since the function is specific to this form, place it in its code module.
From Form Design View, select View, Code, and paste the code there, then edit
it if the field and control references aren't accurate.

Then set the calculated Balance Due control's ControlSource to

=MyNewBalance()

Hope that helps.
Sprinks

Brook said:
Thank you...

The only question I have now, is where do I place the code? Do I need to
make any changes to my form layout or tblinvoicepayments?

Thanks so much for your time and effort to help me out..

Brook

Sprinks said:
Hi, Brook.

This one took a little doing, as I've never done transactional applications
before, but I think I've got it. While this could be done in an IIf function
call directly in the ControlSource, I think the logic is much more readable
in a custom function:

Private Function MyNewBalance() As Currency
Dim curInvoice As Currency
curInvoice = Me.Parent.Form!YourInvoiceTotal

If Nz([TransactionDate]) = 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 = curInvoice - DSum("[Payment]", "Transactions", _
"[TransactionDate] <= #" & Nz([TransactionDate]) & "#")
End If
End Function

In the AfterUpdate event of the TransactionDate and Payment fields, do a
recalc:

Me.Recalc

to update the BalanceDue control.

Regarding storing these values in a table, yes; you can do it, but it is
inadvisable to do so in virtually all cases. The main reason is that you
because you need VBA code embedded in the form, you risk the field being
wrong if data is added or changed outside the context of your form.
Moreover, it is faster to recalculate it on the fly than read it from disk.
So if you need this value in a report, for example, just add a calculated
field to a query containing all the fields you need, and base the report on
the query.

Hope that helps.
Sprinks

Brook said:
Thanks Sprinks<

That is very close to what I want, but I want to have separate "Balance
Dues" after each payment.

So:
InvoiceTotal Less Payment 1 = Balance Due
Balance Due Less Payment 2 = a new Balance Due
and so on...

Is this possible?

Also, is it possible to pass these calculated fields to a table?

Thanks,

Brook

:

Hi, Brook.

Glad it worked out.

If I understand you correctly, Balance Due = SumofCharges - SumofPayments.
Place a summary field in your payments subform footer (it can be invisible if
you like).

=Sum([PaymentAmount])

If you wish to bring this total to your summary page, just refer to it:

=MyPaymentsSubform.Form!SumofPaymentAmount

Then do your math for the Balance Due.

Hope that helps.
Sprinks

:

Thank you very much...

That worked... I was unaware of the "Parent" as a reserved word.

I do have another question... I'm not sure if you can answer or not..

The setup that was just created is for my invoicepayments subform, which
takes the invoice total and then allows the user to enter payments that are
deducted from the total.

What I am trying to do is this:

Payment1 is deducted from the invoice total giving a Balance Due, then each
additional payment is then deducted from the Balance Due to calculate a new
account balance..

Do you understand... do you know how I can accomplish this?

Thanks,

Brook

:

Hi, Brook.

No, I meant it literally--"Parent" is a reserved Access word. Cut and paste
the following into the Control Source property of the subform control:

=[Parent].[Form]![invoicetotal]

Hope that helps.
Sprinks

:

hello Sprinks,

Thanks for the tip,

I am assuming that you mean make the value of my invoicetotal on my
subform to be: =frminvoices.Form!invoicetotal


When I do this, I get: #Name?

my "main form/parent form" is called frminvoices, and my field is called
invoicetotal

the invoicetotal is based on another subform within the same parent form
=[invoicesubtotal]+[ShippingCost]

so I have two subforms and a main form.

and I don't know if this matters, but my subforms are located on two tabs.
one for product enty and one for payment enter.

Thanks for the response..

Brook


Any Ideas?



:

Hi, Brook. Try:

=[Parent].[Form]![invoicetotal]


Hope that helps.
Sprinks

:

hello all..

I am trying to pass a calculated form (frminvoices) field (invoicetotal
(This is a calculated field)) to subform (frminvoicepayments subform) field
(invoicetotal).

I know this should be easy, but I have racked my brain on this one...

Can anyone help?

Thanks,

Brook
 
B

Brook

Responses to your comments:

Please see below your comments / questions:

Sprinks said:
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.

--- so for the Balance Due I should create an unbound field (=myNewBalance)?
- 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.

-- Why wouldn't my invoicetotoal, invoicenumber, and balancedue have
anything to do with a payment? don't I need the invoicenumber to reference
the invoice, the invoicetotal to have something to deduct my payment from and
balance due as my newinvoice balance?
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 have found several articles regarding Normaliation that I am reading
and looking into.

- I don't understand why you have invoiceid, invoicenum, and invoicenumber
fields in tblinvoices. They presumably do the same thing.

-- Ok... there is a reason behind this::: I have two types of invoices -
Sold and Consignment, each has a different type of identifier for Sold its
"SLD-" and an invoice number and for Consignment its "CON-" and an invoice
number.

-- The invoiceid is an autonumber field to give me an basis for my primary
key for no duplicates, then the invoicenum is a number that is based on the
invoice type and performs a DMAX on the invoice type, see below on my code:

Begin Code **************

Private Sub cboinvoicetype_AfterUpdate()

Me.invoicenum = Nz(DMax("invoicenum", "tblinvoices", "invoicetype = '" &
Me.invoicetype & "'"), 0) + 1

If Me.cboinvoicetype = "Sold" Then
Me.invoicenumber = "SLD-" & Format(invoicenum, "0000")
Else
If Me.cboinvoicetype = "Consignment" Then
Me.invoicenumber = "CON-" & Format(invoicenum, "0000")
End If
End If


End Sub


End Code************



- 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.

-- I understand this, and the reason that I set it up this way is that my
billing and shipping address are not always the same for each client, I have
instances in which at the last minute the Ship To Address changes to the
customers address instead of my client, that is why I have it set up and
saved in the manner.

-- Can you suggest a different way to do this? I am open to ideas?
- 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 have a lot of people tell me that, but the thing is, is that I sell
products in which each product id is unique, once I select my item from my
inventory, it contains a unique orderid (productID), size, shape, and will
always only be one of that product, and my unit price is actually my
pricepersqfoot per my tblinvoicedetails.

-- I sell custom made carpets, so each carpet gets an orderid/serialid,
size,sqft, quality, pricepersqfoot, totalprice, and shippingprice.



--- Please letme know if this helps you understand my madness on my setup..

Brook

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
 
B

Brook

Sprinks....

I wanted to let you know that I added the updated code, and I am sorry for
not catching the miss spellings on my coding...

I corrected the code as you suggested and it is working great, I just have
one more question, is it possible to keep my previous balance before I add a
new payment?

So if the BalanceDue was $500.00 and I add a payment of $50.00, the new
balance is $450.00, but then if I add a new payment of $50.00, the
newbalancedue is $400.00. So therefore it would act as a running balance
after the payments are made...


thank you so much...... for all your help...

check out my website and you will see the products that I design, import
and sell...

www dot karmaimports dot net

Brook

Sprinks said:
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
 
S

Sprinks

Brook,

Balance Due is not a "field" on your form. It is an unbound "control" that
displays a calculation. In general, if a quantity or string is calculated
from other table fields, it SHOULD NOT be stored as a field itself. To do so
would violate 3-Normal Form rules, which you will discover as you study
normalization.

So, you should delete all calculated fields from your table structures.
These include:

InvoiceNumber Calculated from InvoiceNum and InvoiceType
InvoiceTotal Calculated as a summary of Invoice Details
BalanceDue Calculated as Sum(InvoiceDetails) - Sum(InvoicePayments)

The common way to handle customer's whose ShipTo address will change is to
provide the ShipTo fields in the Order table (your Invoice table), and
include them also in the Customer table for use as a default to speed order
entry. So the user would choose the customer from a combo box. The combo
box would include all columns you'd like to display on the form, including
their billing address info, and the shipto info. For the billing address,
merely display these using the Column property of the combo box, which is
zero-based. For example, to display the third column of the box in an
unbound textbox, set the ControlSource to:

= Me!MyComboBox.Column(2)

These informative controls merely *display* customer information on the
screen; the only thing that is stored is the CustomerNumber. The ShipTo
fields would be handled differently since they can change with each order.
Assuming, however, that there is a default ShipTo, load this information into
the Customer table record, and then copy it to your Invoice fields in the
AfterUpdate event of the combo box:

Me![ShipToAddress] = Me!MyComboBox.Column(x)
Me![ShipToAddress2] = Me!MyComboBox.Column(y)
Me![ShipToCity] = Me!MyComboBox.Column(z)
.....etc.

where x,y, and z are the index to the corresponding column of the combo box.

Hope that answers your questions.
Sprinks

Brook said:
Responses to your comments:

Please see below your comments / questions:

Sprinks said:
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.

--- so for the Balance Due I should create an unbound field (=myNewBalance)?
- 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.

-- Why wouldn't my invoicetotoal, invoicenumber, and balancedue have
anything to do with a payment? don't I need the invoicenumber to reference
the invoice, the invoicetotal to have something to deduct my payment from and
balance due as my newinvoice balance?
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 have found several articles regarding Normaliation that I am reading
and looking into.

- I don't understand why you have invoiceid, invoicenum, and invoicenumber
fields in tblinvoices. They presumably do the same thing.

-- Ok... there is a reason behind this::: I have two types of invoices -
Sold and Consignment, each has a different type of identifier for Sold its
"SLD-" and an invoice number and for Consignment its "CON-" and an invoice
number.

-- The invoiceid is an autonumber field to give me an basis for my primary
key for no duplicates, then the invoicenum is a number that is based on the
invoice type and performs a DMAX on the invoice type, see below on my code:

Begin Code **************

Private Sub cboinvoicetype_AfterUpdate()

Me.invoicenum = Nz(DMax("invoicenum", "tblinvoices", "invoicetype = '" &
Me.invoicetype & "'"), 0) + 1

If Me.cboinvoicetype = "Sold" Then
Me.invoicenumber = "SLD-" & Format(invoicenum, "0000")
Else
If Me.cboinvoicetype = "Consignment" Then
Me.invoicenumber = "CON-" & Format(invoicenum, "0000")
End If
End If


End Sub


End Code************



- 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.

-- I understand this, and the reason that I set it up this way is that my
billing and shipping address are not always the same for each client, I have
instances in which at the last minute the Ship To Address changes to the
customers address instead of my client, that is why I have it set up and
saved in the manner.

-- Can you suggest a different way to do this? I am open to ideas?
- 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 have a lot of people tell me that, but the thing is, is that I sell
products in which each product id is unique, once I select my item from my
inventory, it contains a unique orderid (productID), size, shape, and will
always only be one of that product, and my unit price is actually my
pricepersqfoot per my tblinvoicedetails.

-- I sell custom made carpets, so each carpet gets an orderid/serialid,
size,sqft, quality, pricepersqfoot, totalprice, and shippingprice.



--- Please letme know if this helps you understand my madness on my setup..

Brook

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
 
S

Sprinks

Brook,

I don't understand your question. The code is designed to do precisely what
you describe; display the balance due as of a given payment:

Example of InvoicePaymentDetails Subform (assuming an InvoiceTotal of $1000):

Payment# PayDate Amount Balance Due
----------- ----------- ---------- ------------------
1 3/1/05 $500.00 $500.00
2 4/1/05 $200.00 $300.00
3 5/1/05 $300.00 $ 0.00

Sprinks

Brook said:
Sprinks....

I wanted to let you know that I added the updated code, and I am sorry for
not catching the miss spellings on my coding...

I corrected the code as you suggested and it is working great, I just have
one more question, is it possible to keep my previous balance before I add a
new payment?

So if the BalanceDue was $500.00 and I add a payment of $50.00, the new
balance is $450.00, but then if I add a new payment of $50.00, the
newbalancedue is $400.00. So therefore it would act as a running balance
after the payments are made...


thank you so much...... for all your help...

check out my website and you will see the products that I design, import
and sell...

www dot karmaimports dot net

Brook

Sprinks said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top