Lookup in the Form

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data and
print the form out as an invoice. I did not realise there are some many
problems:

a) I've 2 fields, Client name and address , i want to input the client name
and the address field will automatically show their address. ( of course,
the data are saved in other tables that has Names and addresess.. ) How can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to have the
prices , and subtotal field to calculate the [price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '" &
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data and
print the form out as an invoice. I did not realise there are some many
problems:

a) I've 2 fields, Client name and address , i want to input the client name
and the address field will automatically show their address. ( of course,
the data are saved in other tables that has Names and addresess.. ) How can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to have the
prices , and subtotal field to calculate the [price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

Ronaldo

Thanks a lot and let me try..

Eric



"RonaldoOneNil" ...
You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '" &
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data and
print the form out as an invoice. I did not realise there are some many
problems:

a) I've 2 fields, Client name and address , i want to input the client
name
and the address field will automatically show their address. ( of course,
the data are saved in other tables that has Names and addresess.. ) How
can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to have
the
prices , and subtotal field to calculate the [price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the address
would be pop up..

how should i wrote the code?!

Thanks



"RonaldoOneNil" ...
You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '" &
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data and
print the form out as an invoice. I did not realise there are some many
problems:

a) I've 2 fields, Client name and address , i want to input the client
name
and the address field will automatically show their address. ( of course,
the data are saved in other tables that has Names and addresess.. ) How
can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to have
the
prices , and subtotal field to calculate the [price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

The control source property of the address field on your invoice form should be
=DLookup("[add]","agent","[agent] = '" & Me.[agent] & "'")

This is assuming the agent field is text so be aware of the single
apostrophies around the Me.[agent] part.

"Eric" wrote:

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the address
would be pop up..

how should i wrote the code?!

Thanks



"RonaldoOneNil" ...
You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '" &
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data and
print the form out as an invoice. I did not realise there are some many
problems:

a) I've 2 fields, Client name and address , i want to input the client
name
and the address field will automatically show their address. ( of course,
the data are saved in other tables that has Names and addresess.. ) How
can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to have
the
prices , and subtotal field to calculate the [price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

Ronaldo

Thanks for your help again.. but i try it does not work.

i put the following into the property of the "address" filed


=DLookup("[address]","agent","[agent] = '" & Me.[agent] & "'")



The form that i'm using is [inv-billings], the name of the fields - Agent -
agent, and the address is 'agent_add'

The database for the agent and address :
The name of the file is [agent]
The agent field is name 'agent'
the address fild is name 'address"
All are in text format







"RonaldoOneNil" ...
The control source property of the address field on your invoice form
should be
=DLookup("[add]","agent","[agent] = '" & Me.[agent] & "'")

This is assuming the agent field is text so be aware of the single
apostrophies around the Me.[agent] part.

"Eric" wrote:

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it
has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the address
would be pop up..

how should i wrote the code?!

Thanks



"RonaldoOneNil" ...

You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '"
&
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data
and
print the form out as an invoice. I did not realise there are some
many
problems:

a) I've 2 fields, Client name and address , i want to input the client
name
and the address field will automatically show their address. ( of
course,
the data are saved in other tables that has Names and addresess.. )
How
can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to
have
the
prices , and subtotal field to calculate the
[price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

On Wed, 21 Jan 2009 10:28:57 +0800, Eric wrote:

Ronaldo

Thanks for your help again.. but i try it does not work.

i put the following into the property of the "address" filed


=DLookup("[address]","agent","[agent] = '" & Me.[agent] & "'")



The form that i'm using is [inv-billings], the name of the fields -
Agent - agent, and the address is 'agent_add'

The database for the agent and address : The name of the file is [agent]
The agent field is name 'agent'
the address fild is name 'address"
All are in text format







"RonaldoOneNil"
...
The control source property of the address field on your invoice form
should be
=DLookup("[add]","agent","[agent] = '" & Me.[agent] & "'")

This is assuming the agent field is text so be aware of the single
apostrophies around the Me.[agent] part.

"Eric" wrote:

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it
has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the
address would be pop up..

how should i wrote the code?!

In a control expression you cannot use "Me.". That only works in VBA
code. Try getting rid of that.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick

You mean, i should wrote like this

==DLookUp("[address]","agent","[agent]= & ' &")

I tried but invalid.

Eric



"Rick Brandt" bc.com...
On Wed, 21 Jan 2009 10:28:57 +0800, Eric wrote:

Ronaldo

Thanks for your help again.. but i try it does not work.

i put the following into the property of the "address" filed


=DLookup("[address]","agent","[agent] = '" & Me.[agent] & "'")



The form that i'm using is [inv-billings], the name of the fields -
Agent - agent, and the address is 'agent_add'

The database for the agent and address : The name of the file is [agent]
The agent field is name 'agent'
the address fild is name 'address"
All are in text format







"RonaldoOneNil"
...
The control source property of the address field on your invoice form
should be
=DLookup("[add]","agent","[agent] = '" & Me.[agent] & "'")

This is assuming the agent field is text so be aware of the single
apostrophies around the Me.[agent] part.

"Eric" wrote:

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it
has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the
address would be pop up..

how should i wrote the code?!

In a control expression you cannot use "Me.". That only works in VBA
code. Try getting rid of that.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Ronaldo

Is the code write like this : ==DLookUp("[address]","agent","[agent]= & '
&")

Thanks

"RonaldoOneNil" ...
The control source property of the address field on your invoice form
should be
=DLookup("[add]","agent","[agent] = '" & Me.[agent] & "'")

This is assuming the agent field is text so be aware of the single
apostrophies around the Me.[agent] part.

"Eric" wrote:

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it
has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the address
would be pop up..

how should i wrote the code?!

Thanks



"RonaldoOneNil" ...

You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '"
&
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data
and
print the form out as an invoice. I did not realise there are some
many
problems:

a) I've 2 fields, Client name and address , i want to input the client
name
and the address field will automatically show their address. ( of
course,
the data are saved in other tables that has Names and addresess.. )
How
can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to
have
the
prices , and subtotal field to calculate the
[price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric

Ronaldo

Can you help me againon the Dlookup .... the code you suggested did not work
!

Appreciated

Eric


"RonaldoOneNil" ...
The control source property of the address field on your invoice form
should be
=DLookup("[add]","agent","[agent] = '" & Me.[agent] & "'")

This is assuming the agent field is text so be aware of the single
apostrophies around the Me.[agent] part.

"Eric" wrote:

Ronaldo

Can you help me further !

The data of the agent and address are saved in file - [agent] where it
has
the field of "agent' and "add"

Now, i have the form [invoice], where i got "agent" and "address".

Which i want to let user input the data in the [invoice]. and the address
would be pop up..

how should i wrote the code?!

Thanks



"RonaldoOneNil" ...

You should really use a report to print out rather than the form.
a) Set the control source property of the address field to
=DLookup("[AddressField]","Name and Address Table","[Client Name] = '"
&
Me.[Client Name] & "'")

b) =Nz([Price1],0) + Nz([Price2],0) + Nz([Price3],0)

c) This is why you should use a report, you can have a text box on the
report with the control source set to
=IIf(IsNull([Price1]),"",[Price1])

"Eric" wrote:

Dear all

I hope you can help ..

I want to create a form as an invoice. So that i can input all data
and
print the form out as an invoice. I did not realise there are some
many
problems:

a) I've 2 fields, Client name and address , i want to input the client
name
and the address field will automatically show their address. ( of
course,
the data are saved in other tables that has Names and addresess.. )
How
can
i do it like the excel - Lookup ...the name and get the address

b) The calculation : i've 3 fields, price 1 , price 2, price 3 , to
have
the
prices , and subtotal field to calculate the
[price1]+[price2]+[price3],
however, when one of the field are null. it does not work !
What can i get around with it
c ) how can i print only the fields that are not null...

appreciated your help

Eric