Displaying customer's total

A

alexorin

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

Ofer Cohen

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)
 
A

alexorin

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...
Ofer Cohen said:
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


alexorin said:
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.
Click to expand...
Click to expand...
 
O

Ofer Cohen

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


alexorin said:
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...
Ofer Cohen said:
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


alexorin said:
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.
Click to expand...
Click to expand...
Click to expand...
 
A

alexorin

Thank you for your help Ofer!!!

Ofer Cohen said:
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


alexorin said:
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...
Ofer Cohen said:
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.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Apply Multiple Payments 2
Hyperlink to specific customer 1
Total Detail on Continuous Form 5
Using Combobox to navigate form 2
Running Total from a Subform 3
Subform within continuous form - workaround? 8
Form hangs access displaying caculating when loading 4
subform total behaving strangely 2

Top