Results 1 to 9 of 9
  1. #1
    Hurumpf is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    4

    Is it possible to dynamically assign the field to look up with Dlookup?

    Hi

    I'm attempting to base the field that I look up on the record I click on, in a continuous form so if I have

    Code:
     Dlookup("Field","Table","PK =" & Me.Another Field)
    'Another Field' Would be a combo box and 'Field' would vary depending on the record I selected on the continuous form

    I would be most grateful for a prod in the right direction



    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I guess thats possible. But they have a field called AUTONUM where the key is assigned automatically and the user needs to do nothing.

  3. #3
    Hurumpf is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    4
    Quote Originally Posted by ranman256 View Post
    I guess thats possible. But they have a field called AUTONUM where the key is assigned automatically and the user needs to do nothing.
    I'm hoping it's possible but I have no Idea how to do it. The field part of the lookup is usually static and I don't know how to ask it to look up anything based on another field?
    Something like

    =Dlookup(me.formfield,"table", etc..... (I know that doesn't work but I'm hoping it illustrates what I'm after?

    PK f1 f2 f3 f4
    1 a b c d
    2 e f g h
    3 i j k l

    So I want to be able to decide which field to look up. Please look up f2 where the PK=1 or please look up f4 where the PK =3 etc

    Am I making sense?

    Thanks

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you can declare the lookup field as a string - try this (untested);

    Code:
    Dim sField as String
    
    sField = Me.cmbFields
    
    DLookup(sField,"YourTable","[PKField = " & Me.YourPK)
    Either that or I'm completely missing the point.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Hurumpf is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    4
    Quote Originally Posted by Minty View Post
    I think you can declare the lookup field as a string - try this (untested);

    Code:
    Dim sField as String
    
    sField = Me.cmbFields
    
    DLookup(sField,"YourTable","[PKField = " & Me.YourPK)
    Either that or I'm completely missing the point.
    That's exactly what I'm after and I tried it this way and got a missing operator error.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay add the following to "See" what the problem is

    Code:
    Dim sField as String
    
    sField = Me.cmbFields
    
    Debug.Print "Field: " & sField & " : PK= " & Me.YourPK
    
    DLookup(sField,"YourTable","[PKField = " & Me.YourPK)
    
    
    In the immediate window in the VBA editor (Press Ctrl + G to show it) you should see the values you are passing to the DLookup.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Hurumpf is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    4
    I think I've worked out what the issue is. The field names I'm using are names of activities and for it to look better in the message box that will show the result of the Dlookup to the user, I put spaces in the field names (I know I shouldn't). If I click on a field without a space in the name the Dlookup works fine. If there is a space I get the error. The Dbug was showing field as false.

    Thanks for your help I really appreciate you taking the time to point me in the right direction.

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You could cure that by using the caption property of the field for the combo but use the actual field name as the bound column...?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Missing bracket:
    DLookup(sField,"YourTable","[PKField] = " & Me.YourPK)


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

Similar Threads

  1. Replies: 7
    Last Post: 06-01-2017, 06:34 AM
  2. Replies: 12
    Last Post: 11-22-2015, 02:34 PM
  3. Replies: 5
    Last Post: 08-13-2015, 10:16 AM
  4. Replies: 7
    Last Post: 05-02-2013, 11:14 PM
  5. Replies: 1
    Last Post: 06-01-2009, 04:05 PM

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