Lookup in the Form

On Sat, 24 Jan 2009 12:39:07 +0800, "Eric" wrote:

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

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


"John W. Vinson"
...
On Sat, 24 Jan 2009 12:39:07 +0800, "Eric" wrote:

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

John

So.. can you tell me what is the code

i tried many ways but invain..

Eric
"John W. Vinson"
...
On Sat, 24 Jan 2009 12:39:07 +0800, "Eric" wrote:

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

On Sat, 24 Jan 2009 23:02:57 +0800, "Eric" wrote:

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

John W. Vinson [MVP]

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"
...
On Sat, 24 Jan 2009 23:02:57 +0800, "Eric" wrote:

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

John W. Vinson [MVP]

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

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

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

John W. Vinson [MVP]

On Mon, 2 Feb 2009 23:50:19 +0800, "Eric" wrote:

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

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

"John W. Vinson"
...
On Mon, 2 Feb 2009 23:50:19 +0800, "Eric" wrote:

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

On Tue, 3 Feb 2009 10:04:31 +0800, "Eric" wrote:



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?
--

John W. Vinson [MVP]