Displaying customer's total

[Guest]

Hi all.
I have a form (ClientOrders) with Orders and Payments as subforms in 2 tabs.
There is a text field (RemainingTotals) on the top of my form which
calculates
[OrdersTotal]-[PaymentsTotal] and gives me the remaining total.
I have another form (CustomerDetails) with Name and LastName, with a command
button which, on press, drives me to the ClientOrders form of the current
customer's record. CustomerDetails form is continous.
I would like to add the text box RemainingTotals (as shown in the
ClientOrders form) to my CustomerDetails form.

Any ideas how to do that? Tnx in advance!!
PS: I have created 2 queries calculating the OrdersTotal and PaymentsTotal
for every customer, based o their ID.

 

[Guest]

In the customer form you can create a text box, and in the text box control
source, using dlookup function you can write

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] =" &
[IdFieldNameInForm]),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] =" &
[IdFieldNameInForm]),0)

If the Id of the customer is text, use

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0)

 

[Guest]

Ofer, tnx for the answer, but i am not getting the results.
Just a minute to understand this. [OrdersTotal] refers to the column inside
the query. [OrdersQueryName] to the query's name, [Id] to the column
containing the customer's ID inside the query and [IdFieldNameInForm] is the
field's name from where the code will select the customer's ID. Am i correct?
Because i am having error when testing it..
There is something that i am not doing right...
Kindly ask for help again...

In the customer form you can create a text box, and in the text box control
source, using dlookup function you can write

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] =" &
[IdFieldNameInForm]),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] =" &
[IdFieldNameInForm]),0)

If the Id of the customer is text, use

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0)

--
Good Luck
BS"D

Hi all.
I have a form (ClientOrders) with Orders and Payments as subforms in 2 tabs.
There is a text field (RemainingTotals) on the top of my form which
calculates
[OrdersTotal]-[PaymentsTotal] and gives me the remaining total.
I have another form (CustomerDetails) with Name and LastName, with a command
button which, on press, drives me to the ClientOrders form of the current
customer's record. CustomerDetails form is continous.
I would like to add the text box RemainingTotals (as shown in the
ClientOrders form) to my CustomerDetails form.

Any ideas how to do that? Tnx in advance!!
PS: I have created 2 queries calculating the OrdersTotal and PaymentsTotal
for every customer, based o their ID.

 

[Guest]

In your form you have a text box where the customer id displayed, this text
box is bounded to a field in a table - that the name that should be in the
IdFieldNameInForm

--
Good Luck
BS"D

Ofer, tnx for the answer, but i am not getting the results.
Just a minute to understand this. [OrdersTotal] refers to the column inside
the query. [OrdersQueryName] to the query's name, [Id] to the column
containing the customer's ID inside the query and [IdFieldNameInForm] is the
field's name from where the code will select the customer's ID. Am i correct?
Because i am having error when testing it..
There is something that i am not doing right...
Kindly ask for help again...

In the customer form you can create a text box, and in the text box control
source, using dlookup function you can write

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] =" &
[IdFieldNameInForm]),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] =" &
[IdFieldNameInForm]),0)

If the Id of the customer is text, use

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0)

--
Good Luck
BS"D

Hi all.
I have a form (ClientOrders) with Orders and Payments as subforms in 2 tabs.
There is a text field (RemainingTotals) on the top of my form which
calculates
[OrdersTotal]-[PaymentsTotal] and gives me the remaining total.
I have another form (CustomerDetails) with Name and LastName, with a command
button which, on press, drives me to the ClientOrders form of the current
customer's record. CustomerDetails form is continous.
I would like to add the text box RemainingTotals (as shown in the
ClientOrders form) to my CustomerDetails form.

Any ideas how to do that? Tnx in advance!!
PS: I have created 2 queries calculating the OrdersTotal and PaymentsTotal
for every customer, based o their ID.

 

[Guest]

Thank you for your help Ofer!!!

In your form you have a text box where the customer id displayed, this text
box is bounded to a field in a table - that the name that should be in the
IdFieldNameInForm

--
Good Luck
BS"D

Ofer, tnx for the answer, but i am not getting the results.
Just a minute to understand this. [OrdersTotal] refers to the column inside
the query. [OrdersQueryName] to the query's name, [Id] to the column
containing the customer's ID inside the query and [IdFieldNameInForm] is the
field's name from where the code will select the customer's ID. Am i correct?
Because i am having error when testing it..
There is something that i am not doing right...
Kindly ask for help again...

In the customer form you can create a text box, and in the text box control
source, using dlookup function you can write

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] =" &
[IdFieldNameInForm]),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] =" &
[IdFieldNameInForm]),0)

If the Id of the customer is text, use

=Nz(DlookUp("[OrdersTotal]","[OrdersQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0) -
Nz(DlookUp("[PaymentsTotal]","[PaymentsQueryName]","[Id] ='" &
[IdFieldNameInForm] & "'"),0)

--
Good Luck
BS"D


:

Hi all.
I have a form (ClientOrders) with Orders and Payments as subforms in 2 tabs.
There is a text field (RemainingTotals) on the top of my form which
calculates
[OrdersTotal]-[PaymentsTotal] and gives me the remaining total.
I have another form (CustomerDetails) with Name and LastName, with a command
button which, on press, drives me to the ClientOrders form of the current
customer's record. CustomerDetails form is continous.
I would like to add the text box RemainingTotals (as shown in the
ClientOrders form) to my CustomerDetails form.

Any ideas how to do that? Tnx in advance!!
PS: I have created 2 queries calculating the OrdersTotal and PaymentsTotal
for every customer, based o their ID.