DLookup Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
 
Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string value, and
can be used directly, without quotes.
 
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field on my
Table.

Al Camp said:
Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hoopster said:
I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
 
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


Hoopster said:
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field on my
Table.

Al Camp said:
Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hoopster said:
I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
Sorry guy's,

Still the same. Access is still looking for a field named Forms.

Klatuu said:
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


Hoopster said:
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field on my
Table.

Al Camp said:
Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
After rereading this a few times, something pops out:
Forms![ReturnData]![Description]
The way this line is coded should not be a problem if:
Your form name is ReturnData
And
The control name on the form is Description

You never did identify the error number or actual error description you are
getting.

If that is the case, then the code should work and Me.Description should
also work.

Hoopster said:
Sorry guy's,

Still the same. Access is still looking for a field named Forms.

Klatuu said:
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


Hoopster said:
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field on my
Table.

:

Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
Hoopster

That code only works if the form that holds the value is actually open.

Otherwise, Access thinks you are asking for a prompt...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hoopster said:
Sorry guy's,

Still the same. Access is still looking for a field named Forms.

Klatuu said:
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am
not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


Hoopster said:
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field
on my
Table.

:

Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description]
is a string value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a
Form but
with no success. It seams that Access is looking for a Forms! Field
when I
try to enter the Funtion into the Control Source. What am I doing
wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
Klatuu,
I've used that method many many times, and it works fine, for either numeric or text
criteria.

=DLookUp("[Addr1]","tblCustomers","CustID = Forms!Form2!CustID")
uses a LongInteger to lookup a Text value.

=DLookUp("[CustID]","tblCustomers","Addr1 = Forms!Form2!Addr1")
uses a Text value to lookup a LongInteger value.

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Klatuu said:
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


Hoopster said:
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field on my
Table.

Al Camp said:
Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string
value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
I have tried every example you guy's have listed without any luck. Is there a
chance that one of my Tables or Fields are set up wrong? I have gone as far
as to copy & paste the Table, Form and Field Names just to make sure they
were right and not misspelled. Some times I don't get an Error, it just won't
work but most of the time I get the following:"Microsoft Access can't find
the field 'forms' referred to in your expression. - You may have misspelled
the field name, or the field may have been renamed or deleted.
I have noticed that most of the time using the Expression:
=DLookUp("[ItemNumber]","DescriptionInfo","[Description] = '" &
Forms!ReturnData![Description] & "'"), when I exit out of the Control Source
in the Properties Box, my Expression will have changed to:
=DLookUp("[ItemNumber]","DescriptionInfo","[Description] = '" &
[Forms]![ReturnData]![Description] & "'").


Al Camp said:
Klatuu,
I've used that method many many times, and it works fine, for either numeric or text
criteria.

=DLookUp("[Addr1]","tblCustomers","CustID = Forms!Form2!CustID")
uses a LongInteger to lookup a Text value.

=DLookUp("[CustID]","tblCustomers","Addr1 = Forms!Form2!Addr1")
uses a Text value to lookup a LongInteger value.

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Klatuu said:
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


Hoopster said:
Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field on my
Table.

:

Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description] is a string
value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a Form but
with no success. It seams that Access is looking for a Forms! Field when I
try to enter the Funtion into the Control Source. What am I doing wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
Hoopster

Can you confirm that the form is open when this runs?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hoopster said:
I have tried every example you guy's have listed without any luck. Is there
a
chance that one of my Tables or Fields are set up wrong? I have gone as
far
as to copy & paste the Table, Form and Field Names just to make sure they
were right and not misspelled. Some times I don't get an Error, it just
won't
work but most of the time I get the following:"Microsoft Access can't find
the field 'forms' referred to in your expression. - You may have
misspelled
the field name, or the field may have been renamed or deleted.
I have noticed that most of the time using the Expression:
=DLookUp("[ItemNumber]","DescriptionInfo","[Description] = '" &
Forms!ReturnData![Description] & "'"), when I exit out of the Control
Source
in the Properties Box, my Expression will have changed to:
=DLookUp("[ItemNumber]","DescriptionInfo","[Description] = '" &
[Forms]![ReturnData]![Description] & "'").


Al Camp said:
Klatuu,
I've used that method many many times, and it works fine, for either
numeric or text
criteria.

=DLookUp("[Addr1]","tblCustomers","CustID = Forms!Form2!CustID")
uses a LongInteger to lookup a Text value.

=DLookUp("[CustID]","tblCustomers","Addr1 = Forms!Form2!Addr1")
uses a Text value to lookup a LongInteger value.

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Klatuu said:
The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am
not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


:

Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field
on my
Table.

:

Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description]
is a string
value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a
Form but
with no success. It seams that Access is looking for a Forms!
Field when I
try to enter the Funtion into the Control Source. What am I doing
wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
Mr. Boyce,

Yes sir. The Form is open. I have even went as far as to have the Table
open as well.

Jeff Boyce said:
Hoopster

Can you confirm that the form is open when this runs?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hoopster said:
I have tried every example you guy's have listed without any luck. Is there
a
chance that one of my Tables or Fields are set up wrong? I have gone as
far
as to copy & paste the Table, Form and Field Names just to make sure they
were right and not misspelled. Some times I don't get an Error, it just
won't
work but most of the time I get the following:"Microsoft Access can't find
the field 'forms' referred to in your expression. - You may have
misspelled
the field name, or the field may have been renamed or deleted.
I have noticed that most of the time using the Expression:
=DLookUp("[ItemNumber]","DescriptionInfo","[Description] = '" &
Forms!ReturnData![Description] & "'"), when I exit out of the Control
Source
in the Properties Box, my Expression will have changed to:
=DLookUp("[ItemNumber]","DescriptionInfo","[Description] = '" &
[Forms]![ReturnData]![Description] & "'").


Al Camp said:
Klatuu,
I've used that method many many times, and it works fine, for either
numeric or text
criteria.

=DLookUp("[Addr1]","tblCustomers","CustID = Forms!Form2!CustID")
uses a LongInteger to lookup a Text value.

=DLookUp("[CustID]","tblCustomers","Addr1 = Forms!Form2!Addr1")
uses a Text value to lookup a LongInteger value.

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


The reference to the form control needs to be outside the quotes.
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = " &
Forms![ReturnData]![Description])

I notices Al said it did not need the single quote delimiters, but I am
not
sure that is correct. Omitting them usually means you are looking up a
numeric value. I would code it as:

=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] = '" &
Forms![ReturnData]![Description] & "'")


:

Mr. Camp,
I get the same error. Access still thinks that Forms! is a Field
on my
Table.

:

Try...
=DLookUp("[ItemNumber]","DescriptionInfo", "[Description] =
Forms![ReturnData]![Description]")

No brackets on the table name, and Forms![ReturnData]![Description]
is a string
value, and
can be used directly, without quotes.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to use DLookup to automatically fill in a Value on a
Form but
with no success. It seams that Access is looking for a Forms!
Field when I
try to enter the Funtion into the Control Source. What am I doing
wrong. The
Funtion I am trying to use is
=DLookUp("[ItemNumber]","[DescriptionInfo]","[Description] = ' " &
Forms![ReturnData]![Description] & " ' ").
Click to expand...
Click to expand...
Click to expand...
 
Back
Top