Lookup in the Form

[Eric]

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

 

[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]

Ronaldo

Thanks a lot and let me try..

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

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

 

[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

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

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

 

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

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

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

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

 

[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

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.

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

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

:

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

 

[Rick Brandt]

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

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.

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.

 

[Eric]

Rick

You mean, i should wrote like this

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

I tried but invalid.

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

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.

:

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.

 

[Eric]

Ronaldo

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

Thanks

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.

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

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

:

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

 

[Eric]

Ronaldo

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

Appreciated

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.

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

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

:

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

 

[John W. Vinson]

Ronaldo

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

"Did not work" is not much use, Eric. Did you get an error message? no data?
#Error? Incorrect data?

You say the data are saved in "file" - [Agent]. I presume you mean a Table
named Agent? Access doesn't have "files".

 

[Eric]

John

Yes..it prompt "error"

The database designed like these:

I've a database that save the detail of agent, the table name is [Agent] and
has 2 fields - a) agent b) address

Now i build up a form - called [invoice] has 2 fields
a) agent
b) agent_add

Now, i want to to input the data in [invoice]'s agent field and the
agent_add field will have the address to shown.

So what should i do

Thanks for your assistance, a million thanks
Eric

Ronaldo

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

"Did not work" is not much use, Eric. Did you get an error message? no
data?
#Error? Incorrect data?

You say the data are saved in "file" - [Agent]. I presume you mean a Table
named Agent? Access doesn't have "files".

 

[Eric]

John

So.. can you tell me what is the code

i tried many ways but invain..

Eric

Ronaldo

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

"Did not work" is not much use, Eric. Did you get an error message? no
data?
#Error? Incorrect data?

You say the data are saved in "file" - [Agent]. I presume you mean a Table
named Agent? Access doesn't have "files".

 

[John W. Vinson]

If the field Agent is numeric (or a Lookup Field) try

=DLookup("[add]","agent","[agent] = " & [agent])

If Agent is a text field, such as a person's name, try

=DLookup("[add]","agent","[agent] = """ & [agent] & """")

In a control source (unlike VBA code) you don't need the Me.

 

[Eric]

John

Thanks a million, it works ...

Frankly, i've look up at the help in the Access2003.. but they tell us
differently.

For the benefit of the other reader. Let me explain it in layman terms

Please the following code in the "field"

=dlookup ("[add]" - this is the sources of the database, "agent" - this is
where the lookup field based on, "[agent]=""&[agent]&"" - this is where the
data sources

Thanks

John.. i've no idea why it prompt an error again , i add a button to open
this " form" but every time when i click. error button occurred and the
field was automatically backup by the system.

Eric

 

[John W. Vinson]

Frankly, i've look up at the help in the Access2003.. but they tell us
differently.

For the benefit of the other reader. Let me explain it in layman terms

Please the following code in the "field"

=dlookup ("[add]" - this is the sources of the database, "agent" - this is
where the lookup field based on, "[agent]=""&[agent]&"" - this is where the
data sources

To put it in different words:

The first argument is the name of the Field that you want to look up.
The second argument is the name of the Table or Query which contains that
field.
The third argument is a text string which is a valid SQL WHERE clause - a
criterion, or multiple criteria, specifying which record in the table should
be used.

Thanks

John.. i've no idea why it prompt an error again , i add a button to open
this " form" but every time when i click. error button occurred and the
field was automatically backup by the system.

I have NO idea what this sentence means. "the field was automatically
backup"... !?

If you're getting a Windows error and the *database* is being backed up, your
database is corrupt. You'll need to use (at least) Tools... Utilities...
Compact and Repair to fix it. That may not be enough; see
http://www.granite.ab.ca/access/corruptmdbs.htm for an extensive reference on
corruption.

 

[Eric]

Dear John

Is me again; i try again on the following but it is not working, can you
tell me what is wrong

I want to lookup the field "address" which is in file of [voucher], and the
address is located in [hotel]'s field of 'supplier'.


=DLookUp("[address]","address","[hotel] = """ & [hotel] & """")

Thanks
Eric

Frankly, i've look up at the help in the Access2003.. but they tell us
differently.

For the benefit of the other reader. Let me explain it in layman terms

Please the following code in the "field"

=dlookup ("[add]" - this is the sources of the database, "agent" - this is
where the lookup field based on, "[agent]=""&[agent]&"" - this is where
the
data sources

To put it in different words:

The first argument is the name of the Field that you want to look up.
The second argument is the name of the Table or Query which contains that
field.
The third argument is a text string which is a valid SQL WHERE clause - a
criterion, or multiple criteria, specifying which record in the table
should
be used.

Thanks

John.. i've no idea why it prompt an error again , i add a button to open
this " form" but every time when i click. error button occurred and the
field was automatically backup by the system.

I have NO idea what this sentence means. "the field was automatically
backup"... !?

If you're getting a Windows error and the *database* is being backed up,
your
database is corrupt. You'll need to use (at least) Tools... Utilities...
Compact and Repair to fix it. That may not be enough; see
http://www.granite.ab.ca/access/corruptmdbs.htm for an extensive reference
on
corruption.

 

[John W. Vinson]

Dear John

Is me again; i try again on the following but it is not working, can you
tell me what is wrong

I want to lookup the field "address" which is in file of [voucher], and the
address is located in [hotel]'s field of 'supplier'.


=DLookUp("[address]","address","[hotel] = """ & [hotel] & """")

Thanks
Eric

The first argument of DLookup is the name of the field you want to look up.
The second argument is the *name of the Table* in which you want to look it up
- you have "address", and I suspect it should be either "[Voucher]" or
"[Supplier]", depending on what you mean by "file of" and "field of supplier".

So... first argument is a fieldname; second argument is a table or query name;
third argument is a string which is a valid SQL WHERE clause specifying which
record to look up. Since I don't know your table or fieldnames it's hard to be
sure!

 

[Eric]

John

You are wonderful

I've 2 tables - a) voucher b) hotel

i use voucher as a form where have fields - "supplier" and "address" i want
user to input the name of supplier in "supplier" and the "address" field
will show the address of the supplier
Both data are saved in table - hotel
In other word, my sources of data is [hotel]

the field that i need to look up is " address" and it depends on the input
of "supplier"

= Dlookup("[address]","hotel","[hotel]="""&[hotel] & """)

But this is not working

Eric

Dear John

Is me again; i try again on the following but it is not working, can you
tell me what is wrong

I want to lookup the field "address" which is in file of [voucher], and
the
address is located in [hotel]'s field of 'supplier'.


=DLookUp("[address]","address","[hotel] = """ & [hotel] & """")

Thanks
Eric

The first argument of DLookup is the name of the field you want to look
up.
The second argument is the *name of the Table* in which you want to look
it up
- you have "address", and I suspect it should be either "[Voucher]" or
"[Supplier]", depending on what you mean by "file of" and "field of
supplier".

So... first argument is a fieldname; second argument is a table or query
name;
third argument is a string which is a valid SQL WHERE clause specifying
which
record to look up. Since I don't know your table or fieldnames it's hard
to be
sure!

 

[John W. Vinson]

John

You are wonderful

I've 2 tables - a) voucher b) hotel

i use voucher as a form where have fields - "supplier" and "address" i want
user to input the name of supplier in "supplier" and the "address" field
will show the address of the supplier
Both data are saved in table - hotel
In other word, my sources of data is [hotel]

the field that i need to look up is " address" and it depends on the input
of "supplier"

= Dlookup("[address]","hotel","[hotel]="""&[hotel] & """)

But this is not working

I'm sorry, Eric, but this isn't making any sense at all.

You refer to fields named Supplier and Address.

Your query refers to a field named Hotel (and also to a table named Hotel).

You say you have a table named voucher that you "use as a form". A table is
one kind of object; a form is a different kind of object. You CAN'T "use a
table as a form".

GUESSING that you have a field named Supplier in the table named Hotel then

=DLookUp("[Address]", "[Hotel]", "[Supplier] = """ & [something] & """")

might work. I don't know what you should put in for [something] because I
cannot see your form and do not know the context - put the name of whatever
control on the form would identify the supplier. If supplier is a Lookup Field
this will need more work - is it?