Dlookup syntax problems

[Denys]

I need help even after reading most of the post regarding dlookup.

I want to look up the name of a plant corresponding to the IDnumber of that
plant.
I have a tblinventory with a idnumber property set to "autonumber".

From the IDnumber in the tblinventory, I want the name of the plant to
appears in the name text field on the subfrmhistory.
I tried the following but it does work.

=DLookUp("[Name]","[tblInventory]![idnumber]=Forms![subfrmhistory].[plantid]")

Plantid in the subfrmhistory is the same idnumber corresponding to the
tblinventory.

What am I doing wrong???

Thanks for your help

Denys

 

[Jeff Boyce]

Have you tried checking Access HELP for the syntax that DLookup uses?

I seem to recall that you need to tell it which table, which field, and
[optionally] the WHERE condition(s).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Linq Adams via AccessMonster.com]

And here you've omitted the table name, which should be the second argument.

Have you tried checking Access HELP for the syntax that DLookup uses?

I seem to recall that you need to tell it which table, which field, and
[optionally] the WHERE condition(s).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need help even after reading most of the post regarding dlookup.

[quoted text clipped - 17 lines]

Denys

 

[Denys]

I'm sorry guys, but nothing is working. I just cannot wrap my brain about the
syntax.

Tx though for you replies.

Denys

And here you've omitted the table name, which should be the second argument.

Have you tried checking Access HELP for the syntax that DLookup uses?

I seem to recall that you need to tell it which table, which field, and
[optionally] the WHERE condition(s).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need help even after reading most of the post regarding dlookup.

[quoted text clipped - 17 lines]

Denys

 

[John W. Vinson/MVP]

I need help even after reading most of the post regarding dlookup.

I want to look up the name of a plant corresponding to the IDnumber of that
plant.
I have a tblinventory with a idnumber property set to "autonumber".

From the IDnumber in the tblinventory, I want the name of the plant to
appears in the name text field on the subfrmhistory.
I tried the following but it does work.

=DLookUp("[Name]","[tblInventory]![idnumber]=Forms![subfrmhistory].[plantid]")

Plantid in the subfrmhistory is the same idnumber corresponding to the
tblinventory.

What am I doing wrong???

Let's try again, step by step.

DLookUp as a function takes three arguments.

The first argument is the name of the field that you want to look up.

The second argument, after a comma in the function call, is the name
of the Table or the Query in which you want to look up the field.

The third argment, after another comma, is a text string containing a
valid SQL WHERE clause which selects which record or records to view
for the lookup. This argument can be omitted; if it is, the function
will return the first row it comes upon.

Your DLookUp call apparently has the first argument ([Name]) and the
third argument (the form reference) but it does not have the second,
so Access doesn't know to look in tblInventory.


A second problem is how you're referencing the subform. A subform is
not open in its own right, and therefore is not in the Forms
collection. You need to reference the name of the mainform, and then
the name of the subform control on that mainform.

I don't know enough about your database to be absolutely certain but
try:

=DLookUp("[Name]", "tblInventory",
"[idnumber]=Forms![yourmainformname]![subfrmhistory].Form![plantid]")

This will look up the field [Name] in the table [tblInventory], using
as a criterion the value in the control named PlantID in the form
within the subform control named subfrmhistory, which is on the form
yourmainformname.

Note that Name is a bad Name for a field, since a table has a Name
property, a field has a Name property, a form control has a Name
property - Access can and will get confused about what Name you want.
Also note that a Subform Control on a form - the box on the main form
- has its own name; it's *that* name that you need in the Forms!
reference. it might or might not be the same as the name of the Form
object you're displaying *in* that subform control. The name of the
form itself (the one you're using as a subform) is irrelevant in this
context.

 

[Denys]

Thanks John. I will try your suggestions this upcoming weekend.

Denys

I need help even after reading most of the post regarding dlookup.

I want to look up the name of a plant corresponding to the IDnumber of that
plant.
I have a tblinventory with a idnumber property set to "autonumber".

From the IDnumber in the tblinventory, I want the name of the plant to
appears in the name text field on the subfrmhistory.
I tried the following but it does work.

=DLookUp("[Name]","[tblInventory]![idnumber]=Forms![subfrmhistory].[plantid]")

Plantid in the subfrmhistory is the same idnumber corresponding to the
tblinventory.

What am I doing wrong???

Let's try again, step by step.

DLookUp as a function takes three arguments.

The first argument is the name of the field that you want to look up.

The second argument, after a comma in the function call, is the name
of the Table or the Query in which you want to look up the field.

The third argment, after another comma, is a text string containing a
valid SQL WHERE clause which selects which record or records to view
for the lookup. This argument can be omitted; if it is, the function
will return the first row it comes upon.

Your DLookUp call apparently has the first argument ([Name]) and the
third argument (the form reference) but it does not have the second,
so Access doesn't know to look in tblInventory.


A second problem is how you're referencing the subform. A subform is
not open in its own right, and therefore is not in the Forms
collection. You need to reference the name of the mainform, and then
the name of the subform control on that mainform.

I don't know enough about your database to be absolutely certain but
try:

=DLookUp("[Name]", "tblInventory",
"[idnumber]=Forms![yourmainformname]![subfrmhistory].Form![plantid]")

This will look up the field [Name] in the table [tblInventory], using
as a criterion the value in the control named PlantID in the form
within the subform control named subfrmhistory, which is on the form
yourmainformname.

Note that Name is a bad Name for a field, since a table has a Name
property, a field has a Name property, a form control has a Name
property - Access can and will get confused about what Name you want.
Also note that a Subform Control on a form - the box on the main form
- has its own name; it's *that* name that you need in the Forms!
reference. it might or might not be the same as the name of the Form
object you're displaying *in* that subform control. The name of the
form itself (the one you're using as a subform) is irrelevant in this
context.