pass a calculated form field to a subform field?

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

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

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" wrote:

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Hi, Brook. Try:

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


Hope that helps.
Sprinks

"Brook" wrote:

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