Access 2007 DLOOKUP

  • Thread starter Thread starter JMD.Park
  • Start date Start date
J

JMD.Park

Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
This is the expression I am trying to use:=DLookUp("[Name]","[tbl
_DEALERS]","[ ID]=" & [Forms]![frmTRIP_TICKETS_ISSUED_TO]![ ID])
 
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)
 
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.
 
On Tue, 27 Apr 2010 06:13:01 -0700, JMD.Park

Why not use a dropdown with names? It would have 2 columns: a hidden
ID and a visible Name column.
"Name" is a reserved word. Rename the field to DealerName or some
such.

If you insist on DLookup: the ControlSource for the DealerName field
would be:
=DLookup("DealerName", "DEALER", "ID=" & Me.ID)

-Tom.
Microsoft Access MVP
 
JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID
to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID
or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!

__________ Information from ESET Smart Security, version of virus
signature database 4436 (20090918) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...


__________ Information from ESET Smart Security, version of virus signature database 4436 (20090918) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
When I use =DLookup("[BusinessName]","[ tbl_HISTORICAL_DEALERS]","[
SEAFOODID] = '" & Me. SEAFOODID & "'") I receive #Name? in the form field

When I use =DLookup("[BusinessName]","[ tbl_HISTORICAL_DEALERS]","[
SEAFOODID] = " & Me. SEAFOODID) I receive #Name? in the form field

What am I doing wrong?
Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
 
SEAFOODID is numeric

Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
 
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

RonaldoOneNil said:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
 
When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID
=" & Me. SEAFOODID) I receive #Name? in form field

I would use a drop down but there are hundreds of dealers.
Thanks for trying to help!
 
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.

JMD.Park said:
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

RonaldoOneNil said:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
Click to expand...
 
The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.

RonaldoOneNil said:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.

JMD.Park said:
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

RonaldoOneNil said:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)


:

Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
Click to expand...
 
The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.



RonaldoOneNil said:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must containthe
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.
Click to expand...
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?
:
First of all, 'Name' is not a good word to use for a field as it isa
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)
:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

based on the field names that you have provided you can try this.

=DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
me![SEAFOODID]")
 
When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID
=" & Me. SEAFOODID) I receive #Name? in form field
Click to expand...

I think you still have problems with blanks and brackets; and the Me. keyword
applies only in VBA code, not on forms. Try

=DLookup("BusinessName", "tbl_HISTORICAL_DEALERS",
"[SEAFOODID] = " & [SEAFOODID])
I would use a drop down but there are hundreds of dealers.
Thanks for trying to help!
Click to expand...

So what? If it's just for display you'll be fine. A Combo can hold up to 65536
rows (not that I'd ever use one a tenth that size for user input). Depends on
how you're using it!
 
Receive #Error in BusinessName field of form, SEAFOODID does not accept
characters
This =DLookUp("BusinessName","tbl_HISTORICAL_DEALERS","SEAFOODID =" &
[Me].[SEAFOODID]) accepts without errors but will not allow me to type in
SEAFOODID on the form and BusinessName gives #Name?
Any ideas?

vvariety said:
The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.



RonaldoOneNil said:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.
Click to expand...
"JMD.Park" wrote:
Click to expand...
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?
Click to expand...
"RonaldoOneNil" wrote:
Click to expand...
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)
Click to expand...
"JMD.Park" wrote:
Click to expand...
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

based on the field names that you have provided you can try this.

=DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
me![SEAFOODID]")
.
Click to expand...
 
Receive #Error in BusinessName field of form, SEAFOODID does not accept
characters
NOT CHARACTERS, I MEANT NUMBERS

JMD.Park said:
Receive #Error in BusinessName field of form, SEAFOODID does not accept
characters
This =DLookUp("BusinessName","tbl_HISTORICAL_DEALERS","SEAFOODID =" &
[Me].[SEAFOODID]) accepts without errors but will not allow me to type in
SEAFOODID on the form and BusinessName gives #Name?
Any ideas?

vvariety said:
The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.



:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.

:

When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

:

First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)

:

Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!- Hide quoted text -

- Show quoted text -
Click to expand...

based on the field names that you have provided you can try this.

=DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
me![SEAFOODID]")
.
Click to expand...
Click to expand...
 
Which is text?

Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
 
Still will not allow me to type in SEAFOODID and BusinessName is #Name?


John W. Vinson said:
When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID
=" & Me. SEAFOODID) I receive #Name? in form field
Click to expand...

I think you still have problems with blanks and brackets; and the Me. keyword
applies only in VBA code, not on forms. Try

=DLookup("BusinessName", "tbl_HISTORICAL_DEALERS",
"[SEAFOODID] = " & [SEAFOODID])
I would use a drop down but there are hundreds of dealers.
Thanks for trying to help!
Click to expand...

So what? If it's just for display you'll be fine. A Combo can hold up to 65536
rows (not that I'd ever use one a tenth that size for user input). Depends on
how you're using it!
Click to expand...
 
Still will not allow me to type in SEAFOODID and BusinessName is #Name?
Click to expand...

I don't understand.

This DLookUp is looking up a value from a table based on a (numeric) value in
a form. It has NOTHING TO DO with "typing in".

Back up a bit. What is the context? Is this an expression in the Control
Source of a Form textbox? What's the Recordsource for the form (post the SQL)?
Where are you typing? Why would you be typing in a numeric SEAFOODID anyway,
rather than concealing it and picking from a user-friendly combo box?
 
JMD -

If you are using this expression in a query (that is not within code behind
the form), then you need to provide the form name rather than use Me. I also
see a space between the Me. and SEAFOODID, so if this is in the form, you
might only have to remove the space.

If you are using this outside of the form (say in a query or report), then
you will use it like this (use your form name):

=DLookup("[BusinessName]","[ tbl_HISTORICAL_DEALERS]","[SEAFOODID] = " &
Forms![yourformname].[SEAFOODID])

--
Daryl S


JMD.Park said:
Which is text?

Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top