Access 2007 DLOOKUP

  • Follow


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!
0
Reply Utf 4/27/2010 1:13:01 PM

This is the expression I am trying to use:=DLookUp("[Name]","[tbl 
_DEALERS]","[ ID]=" & [Forms]![frmTRIP_TICKETS_ISSUED_TO]![ ID])

"JMD.Park" wrote:

> 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!
0
Reply Utf 4/27/2010 1:56:01 PM

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" wrote:

> 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!
0
Reply Utf 4/27/2010 2:00:01 PM

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" wrote:

> 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!
0
Reply Utf 4/27/2010 2:02:02 PM

On Tue, 27 Apr 2010 06:13:01 -0700, JMD.Park
<JMDPark@discussions.microsoft.com> wrote:

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


>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!
0
Reply Tom 4/27/2010 2:17:54 PM

"JMD.Park" <JMDPark@discussions.microsoft.com> schreef in bericht 
news:71B435AE-5556-40DD-AE61-58E0E851EAE9@microsoft.com...
> 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
>
>
> 


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

The message was checked by ESET Smart Security.

http://www.eset.com



0
Reply gerjan 4/27/2010 2:31:48 PM

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" wrote:

> 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" wrote:
> 
> > 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!
0
Reply Utf 4/27/2010 2:35:01 PM

SEAFOODID is numeric

"Daryl S" wrote:

> 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" wrote:
> 
> > 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!
0
Reply Utf 4/27/2010 2:36:01 PM

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

"RonaldoOneNil" wrote:

> 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" wrote:
> 
> > 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!
0
Reply Utf 4/27/2010 2:39:01 PM

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" wrote:

> When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID = 
> " & SEAFOODID) I get #Name? in the form field
> What am I doing wrong?
> 
> "RonaldoOneNil" wrote:
> 
> > 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" wrote:
> > 
> > > 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!
0
Reply Utf 4/27/2010 3:04:01 PM

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!

"Tom van Stiphout" wrote:

> On Tue, 27 Apr 2010 06:13:01 -0700, JMD.Park
> <JMDPark@discussions.microsoft.com> wrote:
> 
> 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
> 
> 
> >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!
> .
> 
0
Reply Utf 4/27/2010 3:04:01 PM

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" wrote:

> 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" wrote:
> 
> > When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID = 
> > " & SEAFOODID) I get #Name? in the form field
> > What am I doing wrong?
> > 
> > "RonaldoOneNil" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > 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!
0
Reply Utf 4/27/2010 3:09:01 PM

On Apr 27, 11:09=A0am, JMD.Park <JMDP...@discussions.microsoft.com>
wrote:
> The names are as I have shown recently, sorry just trying to simplify. Gu=
ess
> I just made it more difficult. The SEAFOODID is numeric.
> Tried taking the spaces out
> =3DDLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID =3D " &
> SEAFOODID) still receiving #Name? in form field.
>
>
>
> "RonaldoOneNil" wrote:
> > 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 dat=
a type.
>
> > "JMD.Park" wrote:
>
> > > When I use =3DDLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," S=
EAFOODID =3D
> > > " & SEAFOODID) I get #Name? in the form field
> > > What am I doing wrong?
>
> > > "RonaldoOneNil" wrote:
>
> > > > 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' fie=
ld on
> > > > your form
> > > > =3DDLookup("[Name]","Dealer","ID =3D " & ID)
>
> > > > "JMD.Park" wrote:
>
> > > > > Can someone please help me with this expression? I am trying to u=
se an ID to
> > > > > bring up a Name in a form. The form is named ISSUE. The table tha=
t has the
> > > > > ID's and Name is DEALER. The form and table fields are named exac=
tly 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 m=
e what
> > > > > fields and table names to put in the expression and where that wo=
uld be
> > > > > wonderful. Thank you in advance for your assistance in this matte=
r!- Hide quoted text -
>
> - Show quoted text -

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

=3DDLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =3D
me![SEAFOODID]")
0
Reply vvariety 4/27/2010 3:23:43 PM

On Tue, 27 Apr 2010 08:04:01 -0700, JMD.Park
<JMDPark@discussions.microsoft.com> wrote:

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

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!

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

             John W. Vinson [MVP]
0
Reply John 4/27/2010 4:01:10 PM

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" wrote:

> On Apr 27, 11:09 am, JMD.Park <JMDP...@discussions.microsoft.com>
> wrote:
> > 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" wrote:
> > > 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" wrote:
> >
> > > > When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
> > > > " & SEAFOODID) I get #Name? in the form field
> > > > What am I doing wrong?
> >
> > > > "RonaldoOneNil" wrote:
> >
> > > > > 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" wrote:
> >
> > > > > > 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 -
> 
> based on the field names that you have provided you can try this.
> 
> =DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
> me![SEAFOODID]")
> .
> 
0
Reply Utf 4/27/2010 4:12:01 PM

Receive #Error in BusinessName field of form, SEAFOODID does not accept 
characters 
NOT CHARACTERS, I MEANT NUMBERS

"JMD.Park" wrote:

> 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" wrote:
> 
> > On Apr 27, 11:09 am, JMD.Park <JMDP...@discussions.microsoft.com>
> > wrote:
> > > 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" wrote:
> > > > 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" wrote:
> > >
> > > > > When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
> > > > > " & SEAFOODID) I get #Name? in the form field
> > > > > What am I doing wrong?
> > >
> > > > > "RonaldoOneNil" wrote:
> > >
> > > > > > 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" wrote:
> > >
> > > > > > > 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 -
> > 
> > based on the field names that you have provided you can try this.
> > 
> > =DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
> > me![SEAFOODID]")
> > .
> > 
0
Reply Utf 4/27/2010 4:16:01 PM

Which is text?

"Daryl S" wrote:

> 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" wrote:
> 
> > 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!
0
Reply Utf 4/27/2010 4:19:01 PM

Still will not allow me to type in SEAFOODID and BusinessName is #Name?


"John W. Vinson" wrote:

> On Tue, 27 Apr 2010 08:04:01 -0700, JMD.Park
> <JMDPark@discussions.microsoft.com> wrote:
> 
> >When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID 
> >=" & Me. SEAFOODID) I receive #Name? in form field
> 
> 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!
> 
> 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!
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Reply Utf 4/27/2010 4:53:02 PM

On Tue, 27 Apr 2010 09:53:02 -0700, JMD.Park
<JMDPark@discussions.microsoft.com> wrote:

>Still will not allow me to type in SEAFOODID and BusinessName is #Name?
>

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

             John W. Vinson [MVP]
0
Reply John 4/27/2010 5:16:16 PM

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" wrote:

> Which is text?
> 
> "Daryl S" wrote:
> 
> > 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" wrote:
> > 
> > > 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!
0
Reply Utf 4/27/2010 6:10:05 PM

This is a form. The form name is frm_TRIP_TICKETS_ISSUED_TO. I want to be 
able to type in the SEAFOODID on the form frm_TRIP_TICKETS_ISSUED_TO and have 
the expression bring up the BusinessName on the form 
frm_TRIP_TICKETS_ISSUED_TO.
It is in the Control Source of a Form textbox named BusinessName.
Record Source for the form is tblTRIP_TICKETS_ISSUED_TO.
There are hundreds of SEAFOODID's (numberic) that have a corresponding 
number of BusinessNames' (text).
I hope I have answered all your questions.
Ah, the combo box, if I have to resort to this option if I choose the 
SEAFOODID will it save the BusinessName in the table?

"John W. Vinson" wrote:

> On Tue, 27 Apr 2010 09:53:02 -0700, JMD.Park
> <JMDPark@discussions.microsoft.com> wrote:
> 
> >Still will not allow me to type in SEAFOODID and BusinessName is #Name?
> >
> 
> 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?
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Reply Utf 4/27/2010 6:55:02 PM

When I use =DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] 
= " & 
Forms![frm_TRIP_TICKETS_ISSUED_TO].[SEAFOODID]) 
I receive #Name? and a red corner I'm assuming indicates an error.

"Daryl S" wrote:

> 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" wrote:
> 
> > Which is text?
> > 
> > "Daryl S" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > 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!
0
Reply Utf 4/27/2010 6:57:02 PM

Also with the combo box on form frm_TRIP_TICKETS_ISSUED_TO, is it able to 
look up SEAFOODID and BusinessName from a table named tbl_Historical_Dealers?

"John W. Vinson" wrote:

> On Tue, 27 Apr 2010 09:53:02 -0700, JMD.Park
> <JMDPark@discussions.microsoft.com> wrote:
> 
> >Still will not allow me to type in SEAFOODID and BusinessName is #Name?
> >
> 
> 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?
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Reply Utf 4/27/2010 7:04:01 PM

JMD -

Red Corner? That sounds like Excel, not Access...  Where is there a red 
corner? 

Where are you putting this code?  Can you post either the entire procedure 
or SQL so we can help?

 -- 
Daryl S


"JMD.Park" wrote:

> When I use =DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] 
> = " & 
> Forms![frm_TRIP_TICKETS_ISSUED_TO].[SEAFOODID]) 
> I receive #Name? and a red corner I'm assuming indicates an error.
> 
> "Daryl S" wrote:
> 
> > 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" wrote:
> > 
> > > Which is text?
> > > 
> > > "Daryl S" wrote:
> > > 
> > > > 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" wrote:
> > > > 
> > > > > 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!
0
Reply Utf 4/27/2010 7:32:01 PM

JMD -

Another thought...  Check for proper fieldnames.  Is SEAFOODID the name of a 
numeric field in tbl_HISTORICAL_DEALERS, and is it also the name of the 
control on the form frm_TRIP_TICKETS_ISSUED_TO?  If the name of the control 
is say, txtSEAFOODID, then you must use that instead, like this:

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

You can also test these out while debugging using the debug.print command, 
which will display the results in the immediate window.  Try putting these 
before your DLookup code:

Debug.Print Forms![frm_TRIP_TICKETS_ISSUED_TO].[txtSEAFOODID]
Debug.Print 
=DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] = " & 
Forms![frm_TRIP_TICKETS_ISSUED_TO].[txtSEAFOODID])  

-- 
Daryl S


"JMD.Park" wrote:

> When I use =DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] 
> = " & 
> Forms![frm_TRIP_TICKETS_ISSUED_TO].[SEAFOODID]) 
> I receive #Name? and a red corner I'm assuming indicates an error.
> 
> "Daryl S" wrote:
> 
> > 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" wrote:
> > 
> > > Which is text?
> > > 
> > > "Daryl S" wrote:
> > > 
> > > > 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" wrote:
> > > > 
> > > > > 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!
0
Reply Utf 4/27/2010 7:39:01 PM

WORKS! Typed this in the after update event 
Private Sub SEAFOOD_ID_AfterUpdate()
BusinessName.Value = DLookup("BusinessName", "tbl_Historical_Dealers", 
"SeafoodID =" & Me.[SEAFOOD_ID])
Me.Form.Refresh
End Sub

THANKS TO EVERYONE WHO TRIED TO HELP! Especially John in Montgomery who 
finally figured it out. As you will notice I did NOT have one of the field 
names typed properly. 

"Daryl S" wrote:

> JMD -
> 
> Another thought...  Check for proper fieldnames.  Is SEAFOODID the name of a 
> numeric field in tbl_HISTORICAL_DEALERS, and is it also the name of the 
> control on the form frm_TRIP_TICKETS_ISSUED_TO?  If the name of the control 
> is say, txtSEAFOODID, then you must use that instead, like this:
> 
> =DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] = " & 
>  Forms![frm_TRIP_TICKETS_ISSUED_TO].[txtSEAFOODID]) 
> 
> You can also test these out while debugging using the debug.print command, 
> which will display the results in the immediate window.  Try putting these 
> before your DLookup code:
> 
> Debug.Print Forms![frm_TRIP_TICKETS_ISSUED_TO].[txtSEAFOODID]
> Debug.Print 
> =DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] = " & 
> Forms![frm_TRIP_TICKETS_ISSUED_TO].[txtSEAFOODID])  
> 
> -- 
> Daryl S
> 
> 
> "JMD.Park" wrote:
> 
> > When I use =DLookup("[BusinessName]","[tbl_HISTORICAL_DEALERS]","[SEAFOODID] 
> > = " & 
> > Forms![frm_TRIP_TICKETS_ISSUED_TO].[SEAFOODID]) 
> > I receive #Name? and a red corner I'm assuming indicates an error.
> > 
> > "Daryl S" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > Which is text?
> > > > 
> > > > "Daryl S" wrote:
> > > > 
> > > > > 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" wrote:
> > > > > 
> > > > > > 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!
0
Reply Utf 4/27/2010 8:20:01 PM

WORKS! Typed this in the after update event SEAFOODID
Private Sub SEAFOOD_ID_AfterUpdate()
BusinessName.Value = DLookup("BusinessName", "tbl_Historical_Dealers", 
"SeafoodID =" & Me.[SEAFOOD_ID])
Me.Form.Refresh
End Sub

Also Enabled Content in 2007!

THANKS TO EVERYONE WHO TRIED TO HELP! Especially John in Montgomery who 
finally figured it out. As you will notice I did NOT have one of the field 
names typed properly. 


"John W. Vinson" wrote:

> On Tue, 27 Apr 2010 09:53:02 -0700, JMD.Park
> <JMDPark@discussions.microsoft.com> wrote:
> 
> >Still will not allow me to type in SEAFOODID and BusinessName is #Name?
> >
> 
> 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?
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Reply Utf 4/27/2010 9:11:01 PM

26 Replies
476 Views

(page loaded in 0.4 seconds)


Reply: