access data from linked excell sheet in access

[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

[NTC]

seems like an easy thing to do - unless I misunderstand your post;

the form's record source should be the excel table link.....and with the fields there then you should see all the data via this form....

when the form is in single view, rather than continuous view - - then in design mode you should be able to add a combobox into the header - and the wizard would step you thru set up to select a record based on it....

hope this is helpful