Results 1 to 8 of 8
  1. #1
    oollie2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Location
    UK
    Posts
    9

    When text box string is equal to value in table column, display value from that record



    I have two tables in my database, and a form that is opened when the user logs in.
    A text box in this form will display that users name e.g. "Ollie", I want another text box to display this users contracted hours from within the users table when the form is opened.


    I have tried using the DLookUp form in the before update section however nothing happens? Below is an example of what I have tried.
    Code:
    =DLookUp("[ContractedHours]","tblUser","[Operator] =[tblUser]![UserLogin]")
    So saying that the value in my form text box "Operator" is Ollie, the value in my table column "UserLogin" is Ollie, i'd like another text box on the form to display the contracted hours on the record for Ollie.
    tblUser contains these columns
    Code:
    ID    UserLogin   Contracted Hours  Password
    1      Ollie         8:00            *****
    2      Ryan          5:00            *****
    My form contains a text box that will equal either Ollie or Ryan, and I would like another text box that displays the relevant Contracted Hours




  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    The DLookup needs to use the text box on your form for the criteria;

    =DLookUp("[ContractedHours]","tblUser","[Operator] ='" & [YourUserNameTextBox] & "'")

  3. #3
    oollie2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Location
    UK
    Posts
    9
    The text box on my form is named "Operator", I have just tried this however the result was blank.

    Code:
    =DLookUp("[ContractedHours]","tblUser","[Operator] ='" & [UserLogin] & "'")

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    What is the Control Source of the Operator text box?

  5. #5
    oollie2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Location
    UK
    Posts
    9
    The operator text box is set via the login form when the user logs in, the control source is the field "Operator" in another table.

    Basically, the user logs in with their user name, which upon logging in the data entry form opens and the "Operator" field is set, I need a field to display the contracted hours of that operator? Thanks

  6. #6
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    You either need to use the name of the text box on the form, or the name of the Control Source of that text box. In this case both of these are "Operator", so the expression should be;

    =DLookUp("[ContractedHours]","tblUser","[Operator] ='" & [Operator] & "'")

    You can't reference [UserLogin] in the criteria if that field (or a control of that name) does not exist on the form in question.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Wrong:
    =DLookUp("[ContractedHours]","tblUser","[Operator] ='" & [Operator] & "'")[/CODE]

    The red field name must be from the table. So it should be:

    =DLookUp("[ContractedHours]","tblUser","[UserLogin] ='" & [Operator] & "'")

    In addition check the name of the field 'ContractedHours' in the table. In one place you show it as 'Contracted Hours'.
    The DLookup must match the table name.

  8. #8
    oollie2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Location
    UK
    Posts
    9
    Thanks for the help, I have solved this now.

    I set a query in the backend database to set the values for "ContractedHours" to the equal values in tblUser for that operator, then on the form I set the combo box to automatically select the first value.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-26-2017, 09:06 PM
  2. Replies: 6
    Last Post: 05-29-2015, 10:21 AM
  3. Replies: 1
    Last Post: 04-29-2013, 10:28 AM
  4. Replies: 2
    Last Post: 12-21-2012, 01:57 PM
  5. Replies: 1
    Last Post: 01-10-2012, 12:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums