DLookUp

[alhotch]

I have two tables - tblEmployees and tblActivities. The tblEmployees table
has three fields - fldEmployeeID (number type); fldLastName(text type); and
fldFirstName(text type). The tblActivities table has several fileds but the
one I want to reference is fldPerson(number type). The fldPerson value is a
number which corresponds to the fldEmployeeID in the tblEmployees table. I
have created a query called qryPeople which, using SQL looks like this:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities, fldPerson
WHERE (((tblEmployees.fldEmployeeID)=[tblActivities.fldPerson]));

How do I write a DLookUp expression for the "Control Source" field in a tex
box we'll call txtPersonName ? The "Record Source" for the form is a
different table from the above mentioned tables.

=DLookUp("[LastName]","[qryPeople]") yields only the first record of this
multi-record query.

 

[John W. Vinson]

I have two tables - tblEmployees and tblActivities. The tblEmployees table
has three fields - fldEmployeeID (number type); fldLastName(text type); and
fldFirstName(text type). The tblActivities table has several fileds but the
one I want to reference is fldPerson(number type). The fldPerson value is a
number which corresponds to the fldEmployeeID in the tblEmployees table. I
have created a query called qryPeople which, using SQL looks like this:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities, fldPerson
WHERE (((tblEmployees.fldEmployeeID)=[tblActivities.fldPerson]));

This query would be better written with a Join rather than a Where:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities INNER JOIN tblEmployees
ON tblEmployees.fldEmployeeID=tblActivities.fldPerson;

The FROM clause needs the name of a table, not that of a field.

How do I write a DLookUp expression for the "Control Source" field in a tex
box we'll call txtPersonName ? The "Record Source" for the form is a
different table from the above mentioned tables.

=DLookUp("[LastName]","[qryPeople]") yields only the first record of this
multi-record query.

You'll need to use the third argument to DLookUp to provide a criterion for
what to look up:

=DLookUp("[LastName]", "[qryPeople]", "[fldEmployeeID] = " & fldPerson)

You can get the same result without using the query at all - just replace
qryPeople with tblEmployees.

 

[alhotch]

I've tried both your query and DLookUp recommendations in the Control Source
for the text box txtPersonName and I get a #Name? error. I know this is
because a Name Field "name" can't be resolved.

I like the idae of just working with the DLookUp function. Here's what I
entered on the "Control Source" property:
=DLookUp("[LastName]", [tblEmployees]", "[fldEmployeeID] = " & fldPerson

LastName is in the tblEmployees table; fldEmployeeID is in the tblEmployee
table; and fldPerson is in the tblActivities table. My Record Source property
is tblReservations. I must be missing the linkage to the table that has
fldPerson.

I have two tables - tblEmployees and tblActivities. The tblEmployees table
has three fields - fldEmployeeID (number type); fldLastName(text type); and
fldFirstName(text type). The tblActivities table has several fileds but the
one I want to reference is fldPerson(number type). The fldPerson value is a
number which corresponds to the fldEmployeeID in the tblEmployees table. I
have created a query called qryPeople which, using SQL looks like this:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities, fldPerson
WHERE (((tblEmployees.fldEmployeeID)=[tblActivities.fldPerson]));

This query would be better written with a Join rather than a Where:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities INNER JOIN tblEmployees
ON tblEmployees.fldEmployeeID=tblActivities.fldPerson;

The FROM clause needs the name of a table, not that of a field.

How do I write a DLookUp expression for the "Control Source" field in a tex
box we'll call txtPersonName ? The "Record Source" for the form is a
different table from the above mentioned tables.

=DLookUp("[LastName]","[qryPeople]") yields only the first record of this
multi-record query.

You'll need to use the third argument to DLookUp to provide a criterion for
what to look up:

=DLookUp("[LastName]", "[qryPeople]", "[fldEmployeeID] = " & fldPerson)

You can get the same result without using the query at all - just replace
qryPeople with tblEmployees.

 

[alhotch]

Quick update: The tblEmployee shoud read tb;Employees
AND, the fldPerson IS JUST A FIELD and NOT an INDEX or Primary Key. Does
this make a difference ?

I've tried both your query and DLookUp recommendations in the Control Source
for the text box txtPersonName and I get a #Name? error. I know this is
because a Name Field "name" can't be resolved.

I like the idae of just working with the DLookUp function. Here's what I
entered on the "Control Source" property:
=DLookUp("[LastName]", [tblEmployees]", "[fldEmployeeID] = " & fldPerson

LastName is in the tblEmployees table; fldEmployeeID is in the tblEmployee
table; and fldPerson is in the tblActivities table. My Record Source property
is tblReservations. I must be missing the linkage to the table that has
fldPerson.

I have two tables - tblEmployees and tblActivities. The tblEmployees table
has three fields - fldEmployeeID (number type); fldLastName(text type); and
fldFirstName(text type). The tblActivities table has several fileds but the
one I want to reference is fldPerson(number type). The fldPerson value is a
number which corresponds to the fldEmployeeID in the tblEmployees table. I
have created a query called qryPeople which, using SQL looks like this:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities, fldPerson
WHERE (((tblEmployees.fldEmployeeID)=[tblActivities.fldPerson]));

This query would be better written with a Join rather than a Where:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities INNER JOIN tblEmployees
ON tblEmployees.fldEmployeeID=tblActivities.fldPerson;

The FROM clause needs the name of a table, not that of a field.

How do I write a DLookUp expression for the "Control Source" field in a tex
box we'll call txtPersonName ? The "Record Source" for the form is a
different table from the above mentioned tables.

=DLookUp("[LastName]","[qryPeople]") yields only the first record of this
multi-record query.

You'll need to use the third argument to DLookUp to provide a criterion for
what to look up:

=DLookUp("[LastName]", "[qryPeople]", "[fldEmployeeID] = " & fldPerson)

You can get the same result without using the query at all - just replace
qryPeople with tblEmployees.

 

[alhotch]

All is NOW WELL ! The query argument (argument #2 in the DLookUp) was
incorrect and not passing the correct name. That query name is qryPeople. Now
the DlookUp works as advertised.

Thanks again, John. You've been a BIG HELP !

Quick update: The tblEmployee shoud read tb;Employees
AND, the fldPerson IS JUST A FIELD and NOT an INDEX or Primary Key. Does
this make a difference ?

I've tried both your query and DLookUp recommendations in the Control Source
for the text box txtPersonName and I get a #Name? error. I know this is
because a Name Field "name" can't be resolved.

I like the idae of just working with the DLookUp function. Here's what I
entered on the "Control Source" property:
=DLookUp("[LastName]", [tblEmployees]", "[fldEmployeeID] = " & fldPerson

LastName is in the tblEmployees table; fldEmployeeID is in the tblEmployee
table; and fldPerson is in the tblActivities table. My Record Source property
is tblReservations. I must be missing the linkage to the table that has
fldPerson.

I have two tables - tblEmployees and tblActivities. The tblEmployees table
has three fields - fldEmployeeID (number type); fldLastName(text type); and
fldFirstName(text type). The tblActivities table has several fileds but the
one I want to reference is fldPerson(number type). The fldPerson value is a
number which corresponds to the fldEmployeeID in the tblEmployees table. I
have created a query called qryPeople which, using SQL looks like this:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities, fldPerson
WHERE (((tblEmployees.fldEmployeeID)=[tblActivities.fldPerson]));

This query would be better written with a Join rather than a Where:

SELECT tblActivities.fldPerson, tblEmployees.fldEmployeeID,
tblEmployees.FirstName, tblEmployees.LastName
FROM tblActivities INNER JOIN tblEmployees
ON tblEmployees.fldEmployeeID=tblActivities.fldPerson;

The FROM clause needs the name of a table, not that of a field.

How do I write a DLookUp expression for the "Control Source" field in a tex
box we'll call txtPersonName ? The "Record Source" for the form is a
different table from the above mentioned tables.

=DLookUp("[LastName]","[qryPeople]") yields only the first record of this
multi-record query.

You'll need to use the third argument to DLookUp to provide a criterion for
what to look up:

=DLookUp("[LastName]", "[qryPeople]", "[fldEmployeeID] = " & fldPerson)

You can get the same result without using the query at all - just replace
qryPeople with tblEmployees.