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 11:13.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    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 20:05.
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •