Access listbox non bound column as criteria for query

Question

I am trying to filter a query based on value of an item in a listbox in specific column other than the bound column

Here is what I have so far.

I have a listbox with 2 column inv_id and prod_id Name of listbox is lst_MainList. inv_id is the bound column (column 1) List is on a form called Projects

I am trying to create 2 queries.

First is intended to filter query based on inv_id This is easy because inv_id is my bound column

For getting the value of the second column, I tried creating a function

Public Function GetColumnValue(col As Integer, ByRef lst As Access.ListBox)
    GetColumnValue = lst.Column(col)
End Function

The idea is to pass the column number and the listbox name to the function and the function will return the value of that column

Function seems OK. The problem is when I enter this function to my query as criteria

=GetColumnValue(2,[Forms]![Projects]![lst_MainList])

hoping to filter my query based on column 2 of the current active item, I get this error when query runs.

This expression is typed incorrectly or is too complex

I know my last option here is to add an invisible text box bound to column 2 of the listbox but I want to try the function approach because it seems much cleaner and reusable

Any idea what is wrong with my function?

Gustav Gustav · Accepted Answer · 2016-03-22T16:25:19

That's because, in the query, [Forms]![Projects]![lst_MainList] will return the Value property of the control, not a reference to the control.

You may have to do something like this:

Public Function GetColumnValue(col As Integer, FormName As String, ControlName As String)
    GetColumnValue = Forms(FormName).Controls(ControlName).Column(col)
End Function

and then:

=GetColumnValue(2,"Projects","lst_MainList")

Access listbox non bound column as criteria for query

1 Answers