Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: DLookup Fieldname referance a Textbox

    DLookup("FieldName" , "TableName" , "Criteria")

    I have a form that has a dlookup I’m using a dlookup as control source for a textbox which does the following

    Look up value in column header 97 in Table Costset where resource_code on current form is equal to Ing_resource_code on Costset Table

    =DLookUp("[97]","CostSet","[Resource_Code]=costset![ing_resource_code]")

    What I would like is have a text box on the current form Text11 with the value of 97 & have the DLookup reference Fieldname text box Text11 i.e.

    =DLookUp("[Text11]","CostSet","[Resource_Code]=costset![ing_resource_code]")

    I want the ability to enter a new number ie 98 in the text box Text11 & the Dlookup to find the correct column on the table

    Thanks
    Attached Thumbnails Attached Thumbnails Access Dloopuk 1.jpg  
    Last edited by Newwales; 05-27-11 at 10:13.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    I can see 2 problems

    you are telling the dlookup to lookup the [text11] as a feild name

    but you want to dlookup the value of the [text11] so

    lookupfeild = "[" & me.[text11] & "]"

    =DLookUp(lookupfeild,"CostSet","[Resource_Code]=costset![ing_resource_code]")

    an as [ing_resource_code] is a text value base on the image
    so
    =dlookup(looupfeild,"CostSet","[Resource_Code]='" & costset![ing_resource_code] & "'")

    note the '
    if I was writing the code i would do it this way


    lookupfeild = "[" & me.[text11] & "]"

    Whereis = "[Resource_Code]='[WHEREIS]'"

    whereis = replace(Whereis,"[WHEREIS]",costset![ing_resource_code])

    now you have the ' in the right and the " in the right place sometimes it hard to get the "'""'" in the right order

    =dlookup(looupfeild,"CostSet",whereis)

    ok It a bit long but it easyer to read
    Last edited by myle; 05-27-11 at 19:05.
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    Just seen your Other Post about adding cols to a table

    That got me think Next you be asking how can i List those feild names in a combo box

    ok

    Here some code that Lists all feilds in a table

    code this in a Module

    Code:
    Function Get_colName()
    Dim StrFeild As String
    Dim Fi As Field
    Dim db As Database
    Set db = CurrentDb
    lo = 0
    For Each Fi In db.TableDefs("Costsettest").Fields
        If lo > 2 Then 'start at what col
            StrFeild = StrFeild & Fi.Name & ";"
        End If
        lo = lo + 1
    Next
    StrFeild = Mid(StrFeild, 1, Len(StrFeild) - 1)
    
    Get_colName = StrFeild
    
    End Function
    now in the combobox on the form

    set the RowSourceType to valuelist

    now in the form_open

    make shour the combobox name is call Costsettest

    Code:
    Private Sub Form_Open(Cancel As Integer)
    Me.Costsettest.RowSource = Get_colName()
    End Sub
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #4
    Join Date
    May 2011
    Posts
    6
    Quote Originally Posted by myle View Post
    I can see 2 problems


    lookupfeild = "[" & me.[text11] & "]"

    =DLookUp(lookupfeild,"CostSet","[Resource_Code]=costset![ing_resource_code]")
    Thanks for the replay, but where do I put this code, in the text box Control Source or somewhere else, thanks.

    I have put the code in the control box & I get #Name?

    Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •