get data from a linked excell sheet

[asela115]

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

 

[CJ_London]

Why won't a linked table work? which is the simplest solution