Results 1 to 7 of 7
  1. #1
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93

    DLookup with value from CBO

    Hello Accessors,



    I am trying to make this line in the code work. I am unable to obtain the username from the STAFFList table based on the selection of a cbo in the form.

    When I run the code, the MsgBox only displays " -That's who!"

    What am I writing erroneously?

    Code:
        
    Dim DLook As Variant    
    Dim intsearch As Integer
        
    DLook = DLookup("[UserName]", "[tblAIA_STAFFList]", "[AMID]='" & Forms!frmAIA_WorkloadTrackingLog!cboAMID & "'")
    
    MsgBox DLook & "   -That's who!"
    Help?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is AMID a text type field?

    What is the RowSource of the combobox?

    Is this code behind frmAIA_WorkloadTrackingLog?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks okay presuming the AMID field is text. This might be simpler:

    BaldyWeb - Autofill

    and for what you're doing, if the name was in the combo row source:

    MsgBox Forms!frmAIA_WorkloadTrackingLog!cboAMID.Column(1) & " -That's who!"

    or the appropriate column number.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Think I'll get out of the way!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    Hi June7 and pbaldy,

    Thank you for your responses!

    The row source of AMID in SQL view is:
    Code:
    SELECT qrySupport_AMExtended.ID, qrySupport_AMExtended.EmployeeName, qrySupport_AMExtended.AllowAutoAssignFROM qrySupport_AMExtended
    WHERE (((qrySupport_AMExtended.AllowAutoAssign)=Yes))
    ORDER BY qrySupport_AMExtended.EmployeeName;
    The ID is autonumber field. AMExtended.ID connects to the ID of tblAIA_STAFFList. AMExtended is a query to return the first name and last name together in one field. Many of the reports/forms use this query. However, I am trying to achieve the scenario below:

    Desired Scenario:
    When a staff member name is selected on the form and a user clicks on 'Assign' button. The code for assign button would perform:

    -Cross check the username of the staff member that is selected on the form and the username the current user that had clicked on the "Assign" button.
    -IF both usernames match, do nothing.
    -IF the usernames do not match, perform the next code (already written and it works)

    Problem:
    I do not know what to write to cross check a such thing. I am attempting to use DLookUp. If that is wrong function, please let me know which one to use.

    Thank for your help!

  6. #6
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    Viola!! Thanks pbaldy!

    Your suggestion of Forms!frmAIA_WorkloadTrackingLog!cboAMID.Column(1) had me changing my code:

    OLD code:
    Code:
    Dim DLook As Variant
    Dim intsearch As Integer
        
    DLook = DLookup("[UserName]", "[tblAIA_STAFFList]", "[AMID]='" & Forms!frmAIA_WorkloadTrackingLog!cboAMID & "'")
    
    
    MsgBox DLook & "   -That's who!"
    NEW and working code:
    Code:
        
    If fOSUserName() = Me.cboAMID.Column(3) ThenMsgBox "Yes"
    Else
    MsgBox "No"
    End If
    Thanks for helping me think this out loud.

    Granting rep points to both June7 and pbaldy and marking this as SOLVED.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Dlookup an ID
    By JeroenMioch in forum Forms
    Replies: 15
    Last Post: 06-18-2014, 11:18 AM
  2. DLOOKUP Help
    By UTLee in forum Access
    Replies: 8
    Last Post: 08-29-2013, 09:48 AM
  3. VBA dlookup but with where
    By Ruegen in forum Forms
    Replies: 7
    Last Post: 08-20-2013, 12:23 AM
  4. DLookup help
    By denners05 in forum Access
    Replies: 1
    Last Post: 06-11-2011, 12:55 PM
  5. May it is Dlookup
    By cap.zadi in forum Programming
    Replies: 3
    Last Post: 05-09-2011, 05:58 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