Access 2007 DLOOKUP

  • Thread starter Thread starter JMD.Park
  • Start date Start date
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?
 
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 said:
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


:

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...
 
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?
 
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 said:
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 said:
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?

:

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


:

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...
 
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 said:
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 said:
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?

:

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


:

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


:

Which is text?

:

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


:

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