Access to Excel Linked table pulling first column only

[InfernoJaffa]

Hi Guys

I have a linked table from access to excel.
Some of my fields are drop down lists and the data linked is reading the first column, the ID column (which is hidden in access).

Is there a way of asking excel to look at column 2 rather than 1?

Thanks

 

[arnelgp]

edit your dropdown list by double clicking it. when you are vbe, check the Property pane of the combobox, (a window down to the left). set the bound column property to 2.

 

[InfernoJaffa]

edit your dropdown list by double clicking it. when you are vbe, check the Property pane of the combobox, (a window down to the left). set the bound column property to 2.

Thanks, half way there..

Now setting the value in access i get "the value you entered isn't valid for this field".
My Data type is set to number but the source grabs data from another table:
SELECT [tblProducts].[ID], [tblProducts].[ProductID] FROM tblProducts ORDER BY [ID];

 

[Gasman]

I think you would still need the bound field to be 1 (if that is the key field), but show the second field.

That is normally done by setting the first field width to zero.?

https://msdn.microsoft.com/en-us/library/office/ff822489.aspx


The leftmost visible column in a combo box (the leftmost column whose setting in the combo box's ColumnWidths property is not 0) contains the data that appears in the text box part of the combo box in Form view or in a report. The BoundColumn property determines which column's value in the text box or combo box list will be stored when you make a selection. This allows you to display different data than you store as the value of the control.

 

[arnelgp]

yes, to as mr.gasman suggest, on the property sheet again, bound column = 1, column count=2, columnwidths=0;1

you might also need to change the rowsource of your combo:
SELECT [tblProducts].[ID], [tblProducts].[ProductName] FROM tblProducts ORDER BY [ID];

 

[InfernoJaffa]

Hi, I can not get this working.