pass column2 from list box as query criteria??

[accessilliterate]

Hi gang,

I have a form in Access2001 we shall call frmOne and on it is a Listbox we shall call listOne. I want to pass the value (or information) from the 2nd column to a query but cant work out the correct syntax or where in the query to put it.
Can someone kindly point me in the right direction.

I have tried several methods including putting the following in the query criteria:
=[Forms]![frmOne]![listOne].Column(2)

But access just gives an invalid function error. Your help would be most appreciated.

 

[turtlemaster]

With that code you are trying to return the whole column.

[Forms]![frm1]![lstOne].Column(2, [Forms![frm1]![lstOne].ListIndex)

This should return only the second column of the selected item (I don't have time to try it though)

 

[accessilliterate]

Hi turtlemaster,

Thank you for your reply and suggestion but it did not work.
Access gives an "undefined function in expression" error - any more suggestions please ??

 

[turtlemaster]

try hard coding it to return the first value only.

[Forms]![frm1]![lstOne].Column(2, 1)

I might have jsut screwed up the syntax. If that returns the first or second value then make it pull the selected value later.

 

[DougP]

For whatever reason a query will NOT let you refer to anythign but the "Bound" field in a Combo or List box
But by virtue of the Access Gods and my decree there is a workaround.

Put a new textbox on your form and in the On_click event of the combo box pass any column to the textbox (Make the textboxes .visible property False when it's done working)

On_click event ....
me!newtextbox = me!Combo1.column(2)


So then in your query you can refer to the new text box like normal
On_click event ....
me!netextbox = me!Combo1.column(2)

In query
=Forms![formname].form!Newtextbox
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive