locked
Use DLookup to display value from one table based on the value from the other table RRS feed

  • Question

  • I have the following:

    1. tblEmployee with EmployeeID (PK), txtEmployeeName and EmployeePositionID (FK) 

    2. tblPosition with EmployeePositionID (PK) and txtEmployeePosition

    Basically, EmployeePositionID on tblEmployee is a combo box that looks up data from tblPosition

    3. On the frmTimeEntry I have a field where EmployeeID is displayed when this form is just opened to a new record. There is another field txtEmployeePosition where respective employee position should be filled out automatically.

    Using DLookup I can display EmployeePositionID - the number from tblEmployee. I need txtEmployeePosition - the description that should come from tblPosition.

    How the DLookup should look like in this case?

    Please take the description as is and do not challenge the design, etc. It is too time consuming to explain the actuals so the example above is the approximation of the situation.

    Friday, February 3, 2017 4:57 PM

Answers

  • You cannot join tables in DLookup as far as I know, but you can restrict the results of a Dlookup by placing a Subquery in the where criteria using IN.
    Maybe something like: Dlookup("EmployeePositionDescription", "tblPosition", "EmployeePositionID IN (SELECT EmployeePositionID FROM tblEmployee WHERE EmployeeID = " & txtEmployeeId)
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:09 PM
  • I use Control Source for =DLookup... Where do I put your code in? The form is just opened, nothing is clicked and Employee Position must be displayed together with EmployeeID.  
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:28 PM
  • Re: "Basically, EmployeePositionID on tblEmployee is a combo box that looks up data from tblPosition"

    Are you using a combobox on the form? If so, is the position description included as one of its columns? If so, you don't need DLookup() - just display the column info in the textbox with something like:

    =[EmployeePositionID].[Column](x)

    Where "x" is the column number for the position minus one. For example, if the position is the third column, you would use (2).

    Hope it helps...

    
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:40 PM
  • Yes. However, it always displays 1st record - 1st EmployeePositionID no matter what actual EmployeeID is.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:51 PM
  • I don't ever use DLookup functions in ControlSource properties. But I created a table tblEmployees and bound a form to it. Then I created a Textbox txtEmplyeeId and bound that to field EmployeeId. I have tblPositions with PositionId, and PositionDescription fields.

    In same form as txtEmployeeId, I created a Textbox txtPositionDescription and in Property Sheet gave the ControlSource this value: =DLookUp("PositionDescription","tblPositions","PositionId IN (Select PositionId FROM tblPositions WHERE PositionID = " & [txtEmployeeId] & " )")

    I created 2 Employees with 2 different positions. As I move from one record to the next, the txtPositionDescription textbox shows the description.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:52 PM
  • Yes. However, it always displays 1st record - 1st EmployeePositionID no matter what actual EmployeeID is.

    It sounds like you may not have the control bound properly to the data. When you go to a new record, the combobox should be empty unless you have a Default Value set up.

    You could then select the applicable position from the combobox and the corresponding column should also display with it.

    Just my 2 cents...

    • Edited by .theDBguy Friday, February 3, 2017 5:59 PM
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:58 PM
  • I never go to another record in this form. It is only a data entry form. No view of previously created records is allowed, nor it is needed.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:00 PM
  • I never go to another record in this form. It is only a data entry form. No view of previously created records is allowed, nor it is needed.

    This means the record is empty, which also means you must have a default value for the combobox. If you're entering a new record, how will the form know which position to assign to the new employee? The user must somehow have to select or assign the position, right?

    Or, if this is a form for an existing employee, then you might be better off using a form/subform setup. The main form is bound to the employee data and the subform is bound to the time entry data. So, when you open the form/subform, you can filter it to the current employee from the other form.

    Just my 2 cents...

    • Edited by .theDBguy Friday, February 3, 2017 6:04 PM
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:02 PM
  • Not exactly. The user logs in on Login form. This login id or user id is bound to EmployeeID (both user id and employee id is on the same tblEmployee).  Using Dlookup I display EmployeeID based on user id on frmTimeEntry when it is opened.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:06 PM
  • Not exactly. The user logs in on Login form. This login id or user id is bound to EmployeeID (both user id and employee id is on the same tblEmployee).  Using Dlookup I display EmployeeID based on user id on frmTimeEntry when it is opened.

    You should also be able to use DLookup() to display the position. For example, try the following:

    =DLookup("Position","tblPositions","PositionID=" & Forms!LoginFormName.EmployeePositionID)

    Or something like it... We really need to see your table structure to understand better how the tables relate to each other.

    Hope it helps...

    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:14 PM
  • On the frmTimeEntry I have a field where EmployeeID is displayed when this form is just opened to a new record. There is another field txtEmployeePosition where respective employee position should be filled out automatically.

    Bind the form to a query which joins its current table(s) to the positions tables on the position ID columns.  In the query return all relevant columns from the current table(s) and the text position column from the positions table.  You can then bind a text box in the form to the text position column.  Set its Locked property to True (Yes) and its Enable property to False (No) to make it read-only.

    Ken Sheridan, Stafford, England

    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 7:00 PM
  • I have done it as per your description other than locking it. It does not have an impact on displaying values. Never mind. Thank you any way.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 7:02 PM

All replies

  • You cannot join tables in DLookup as far as I know, but you can restrict the results of a Dlookup by placing a Subquery in the where criteria using IN.
    Maybe something like: Dlookup("EmployeePositionDescription", "tblPosition", "EmployeePositionID IN (SELECT EmployeePositionID FROM tblEmployee WHERE EmployeeID = " & txtEmployeeId)
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:09 PM
  • I use Control Source for =DLookup... Where do I put your code in? The form is just opened, nothing is clicked and Employee Position must be displayed together with EmployeeID.  
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:28 PM
  • Re: "Basically, EmployeePositionID on tblEmployee is a combo box that looks up data from tblPosition"

    Are you using a combobox on the form? If so, is the position description included as one of its columns? If so, you don't need DLookup() - just display the column info in the textbox with something like:

    =[EmployeePositionID].[Column](x)

    Where "x" is the column number for the position minus one. For example, if the position is the third column, you would use (2).

    Hope it helps...

    
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:40 PM
  • Yes. However, it always displays 1st record - 1st EmployeePositionID no matter what actual EmployeeID is.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:51 PM
  • I don't ever use DLookup functions in ControlSource properties. But I created a table tblEmployees and bound a form to it. Then I created a Textbox txtEmplyeeId and bound that to field EmployeeId. I have tblPositions with PositionId, and PositionDescription fields.

    In same form as txtEmployeeId, I created a Textbox txtPositionDescription and in Property Sheet gave the ControlSource this value: =DLookUp("PositionDescription","tblPositions","PositionId IN (Select PositionId FROM tblPositions WHERE PositionID = " & [txtEmployeeId] & " )")

    I created 2 Employees with 2 different positions. As I move from one record to the next, the txtPositionDescription textbox shows the description.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:52 PM
  • Yes. However, it always displays 1st record - 1st EmployeePositionID no matter what actual EmployeeID is.

    It sounds like you may not have the control bound properly to the data. When you go to a new record, the combobox should be empty unless you have a Default Value set up.

    You could then select the applicable position from the combobox and the corresponding column should also display with it.

    Just my 2 cents...

    • Edited by .theDBguy Friday, February 3, 2017 5:59 PM
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 5:58 PM
  • I never go to another record in this form. It is only a data entry form. No view of previously created records is allowed, nor it is needed.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:00 PM
  • I never go to another record in this form. It is only a data entry form. No view of previously created records is allowed, nor it is needed.

    This means the record is empty, which also means you must have a default value for the combobox. If you're entering a new record, how will the form know which position to assign to the new employee? The user must somehow have to select or assign the position, right?

    Or, if this is a form for an existing employee, then you might be better off using a form/subform setup. The main form is bound to the employee data and the subform is bound to the time entry data. So, when you open the form/subform, you can filter it to the current employee from the other form.

    Just my 2 cents...

    • Edited by .theDBguy Friday, February 3, 2017 6:04 PM
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:02 PM
  • Not exactly. The user logs in on Login form. This login id or user id is bound to EmployeeID (both user id and employee id is on the same tblEmployee).  Using Dlookup I display EmployeeID based on user id on frmTimeEntry when it is opened.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:06 PM
  • Not exactly. The user logs in on Login form. This login id or user id is bound to EmployeeID (both user id and employee id is on the same tblEmployee).  Using Dlookup I display EmployeeID based on user id on frmTimeEntry when it is opened.

    You should also be able to use DLookup() to display the position. For example, try the following:

    =DLookup("Position","tblPositions","PositionID=" & Forms!LoginFormName.EmployeePositionID)

    Or something like it... We really need to see your table structure to understand better how the tables relate to each other.

    Hope it helps...

    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 6:14 PM
  • On the frmTimeEntry I have a field where EmployeeID is displayed when this form is just opened to a new record. There is another field txtEmployeePosition where respective employee position should be filled out automatically.

    Bind the form to a query which joins its current table(s) to the positions tables on the position ID columns.  In the query return all relevant columns from the current table(s) and the text position column from the positions table.  You can then bind a text box in the form to the text position column.  Set its Locked property to True (Yes) and its Enable property to False (No) to make it read-only.

    Ken Sheridan, Stafford, England

    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 7:00 PM
  • I have done it as per your description other than locking it. It does not have an impact on displaying values. Never mind. Thank you any way.
    • Marked as answer by Oleg.bv Friday, February 3, 2017 7:03 PM
    Friday, February 3, 2017 7:02 PM