dlookup problems

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

Guest

=DLookUp("[lname]","[tblSite]","[staid]='" & [Forms]![staid] & " ' ")

I am trying to get an expression to lookup the value of 'lname' from table
'tblSite', based on the value of 'staid' in the active form. I get #ERROR,
but cannot figure out why. I have searched through the posts, but canot
figure out the deal. All fields are text values. Any help is appreciated.

Thanks
 
=DLookUp("[lname]","[tblSite]","[staid]='" & [Forms]![staid] & " ' ")

I am trying to get an expression to lookup the value of 'lname' from table
'tblSite', based on the value of 'staid' in the active form. I get #ERROR,
but cannot figure out why. I have searched through the posts, but canot
figure out the deal. All fields are text values. Any help is appreciated.

Thanks
Click to expand...

Two possible problems:

The syntax [Forms]![staid] will give you the Form object named staid.
My guess is that the *field* is named staid, not the Form; if it's on
the current form, just leave off the [Forms]! and concatenate [staid]
alone.

Secondly, unless staid is a Text field (possible but a bit unusual for
an ID), you need to leave off the quotemarks. In any case you should
leave off the blanks surrounding the last ' since you're asking to
search for a value of staid ending in a single blank.

Try

=DLookUp("[LName]", "[tblSite]", "[staid] = " & [staid])

John W. Vinson[MVP]
 
Thanks, that worked great! That leads to the next issue with 'dlookup'...
is it possible to use a 'dlookup' statement in a form that uses the result
from another'dlookup' statement in the same form? Example: 1st 'dlookup'
returns site# (staid), next 'dlookup' uses the value from the first to search
a separate table for site name (sname). It seems like, b/c the value (from
1st dlookup) isn't being tored, thehn it isn't available for use later, is
that the case? is there a fix?

Seds

John Vinson said:
=DLookUp("[lname]","[tblSite]","[staid]='" & [Forms]![staid] & " ' ")

I am trying to get an expression to lookup the value of 'lname' from table
'tblSite', based on the value of 'staid' in the active form. I get #ERROR,
but cannot figure out why. I have searched through the posts, but canot
figure out the deal. All fields are text values. Any help is appreciated.

Thanks
Click to expand...

Two possible problems:

The syntax [Forms]![staid] will give you the Form object named staid.
My guess is that the *field* is named staid, not the Form; if it's on
the current form, just leave off the [Forms]! and concatenate [staid]
alone.

Secondly, unless staid is a Text field (possible but a bit unusual for
an ID), you need to leave off the quotemarks. In any case you should
leave off the blanks surrounding the last ' since you're asking to
search for a value of staid ending in a single blank.

Try

=DLookUp("[LName]", "[tblSite]", "[staid] = " & [staid])

John W. Vinson[MVP]
Click to expand...
 
Thanks, that worked great! That leads to the next issue with 'dlookup'...
is it possible to use a 'dlookup' statement in a form that uses the result
from another'dlookup' statement in the same form? Example: 1st 'dlookup'
returns site# (staid), next 'dlookup' uses the value from the first to search
a separate table for site name (sname). It seems like, b/c the value (from
1st dlookup) isn't being tored, thehn it isn't available for use later, is
that the case? is there a fix?
Click to expand...

I'd try to avoid excessive reliance on DLookUp, especially nested
DLookUps. It's a useful tool but it is slow, can be inefficient, and
is generally not necessary if all you want to do is see data. With
proper table design, Queries joining tables to retrieve the needed
data, and (especially) judicious use of Combo Boxes on your form, you
should be able to see the data you need; for instance, you could use a
Combo Box based on the sites table to *store* the staID while
displaying the sname.

John W. Vinson[MVP]
 
Back
Top