Referring to columns in a listbox in the criteria of a query

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

A2K, on Win XP.

Whenever I try to use a reference to a column of a listbox in the criteria
of a query, I get an error that Access cannot interpret the value. I have
tried making it a parmeter in the query, but without success. I usually
just end up writing code to define the SQL with the actual values from the
columns, then setting the querys sql property to this text string, then
requerying the control that contains these values.

Does anyone know of another way to write a query that looks similiar to the
following, and get it to work.

SELECT * FROM myTable where myTable.ID = Forms!myForm.list_myList.column(2)

Thanks!
Dale
 
One method is to use Eval().
SELECT * FROM myTable where myTable.ID =
Eval("Forms!myForm.list_myList.column(2)")
Another is to add a hidden text box on the form that has a control source of
=list_myList.column(2)
You can then use this text box in your query.
 
Create a hidden unbound textbox on your form. Put the following code in the
AfterUpdate event of your listbox:
Me!NameOfHiddenTextbox = Me!list_myList.column(2)

Change your SQL to:
SELECT * FROM myTable where myTable.ID = Forms!myForm!NameOfHiddenTextbox
 
And to add one more option, you can create a public function that reads the
value of the form's combo box's column, and returns that value. Then use
that function in your query.

Public Function GetMyColumn()
GetMyColumn = Forms!myForm.list_myList.column(2)
End Function


SELECT * FROM myTable where myTable.ID =
GetMyColumn();
 
Ken,

I like this idea best, with the addition of a parameter for the control, I
could use it for any listbox. Cool idea.
 
Back
Top