If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookUp
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. |
#2
|
|||
|
|||
DLookUp
On Sat, 6 Feb 2010 13:22:01 -0800, alhotch
wrote: 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 Whe 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
DLookUp
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. "John W. Vinson" wrote: On Sat, 6 Feb 2010 13:22:01 -0800, alhotch wrote: 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 Whe 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. -- John W. Vinson [MVP] . |
#4
|
|||
|
|||
DLookUp
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 ? "alhotch" wrote: 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. "John W. Vinson" wrote: On Sat, 6 Feb 2010 13:22:01 -0800, alhotch wrote: 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 Whe 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. -- John W. Vinson [MVP] . |
#5
|
|||
|
|||
DLookUp
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 ! "alhotch" wrote: 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 ? "alhotch" wrote: 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. "John W. Vinson" wrote: On Sat, 6 Feb 2010 13:22:01 -0800, alhotch wrote: 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 Whe 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. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|