Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    6

    Red face Unanswered: Retreive data from linked excel sheet

    Hi,

    need some advise on below

    I have a excel table (lets say the format is as follows)
    col1 col2
    dataset1 value1
    dataset2 value2

    (where col1 and col2 are the column names and dataset x and value x are the respective data values of each column)
    I have a access form and it has a combo box and a text box
    From Combo box I need to retrieve data available in col1 o excell
    once a particular dataset in col1 has been selected by Combo box the respective value in col2 should be displayed in text box in access form

    I was trying to create a linked table in excel to access ( I don't want to export data from excell to access since my excell sheet is getting changing time to time. so i don't need to change the access table every time)

    for combo box a simple query like below works to select the col1 values from sheet 1

    select col1 from Sheet1 (where Sheet1 is the linked table name in access)
    I wrote a code similar to below using DLookup to get respective values from col2 however it gives a error '#Name?

    =DLookup(col2,Sheet1,col1=[Forms]![FormName]![ComboBox Name])
    (here sheet 1 is the linked table name in access, I didnt give the absolute path of the excell file)

    this returns the above #Name?

    can someone help in this. this can be resolved by correcting my query or using another method (thats why I posted this in lengthy)

    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    The arguments for DLookup need to be strings. Try this:
    Code:
    If Col1 contains text values:
    =DLookup("Col2", "Sheet1", "Col1 = '" & [Forms]![FormName]![ComboBoxName] & "'")
    If Col1 contains numeric values:
    =DLookup("Col2", "Sheet1", "Col1 = " & [Forms]![FormName]![ComboBoxName])
    If Col1 contains date/time values:
    =DLookup("Col2", "Sheet1", "Col1 = #" & [Forms]![FormName]![ComboBoxName] & "#")
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's easier and faster to have the values from both col1 and col2 in the combo. In my example, Text2 and Text4 are the text boxes and Combo0 is the combox with:
    - Column Count: 2
    - Column Widths: 2cm;0cm (translate in inches if necessary)
    - Row Source: SELECT Sheet1.col1, Sheet1.col2 FROM Sheet1;
    Code:
    Private Sub Combo0_AfterUpdate()
    
        '
        ' Retrieve the value for the text box from the second column of the combo box.
        '
        Me.Text2.Value = Me.Combo0.Column(1)
        '
        ' Retrieve the value for the text box directly from the table.
        '
        Me.Text4.Value = DLookup("col2", "Sheet1", "col1 = '" & Me.Combo0.Value & "'")
        
    End Sub
    Have a nice day!

  4. #4
    Join Date
    Feb 2013
    Posts
    6
    well, i figured out the problem with your help.
    I need to correctly define the parameters using the " and '. it gives the expected results as I have tested the proposed solutions with some sample data.
    I'll check this with my actual data sets

    Thanks for the advise and feedback

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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