asked on

using list box column for query criteria

Hello,

I have a 4-column list box. I am trying to create a select query that will use the contents of two columns in the list box as criteria.

For now the list box is called List0 and is on a form called frmMakeModelFix1. The SQL statement I have tried is:

SELECT CabinetRecords.Manufacturer, CabinetRecords.Model, CabinetRecords.Type, CabinetRecords.Size, CabinetRecords.In_Use
FROM CabinetRecords
WHERE (((CabinetRecords.Manufacturer)=[Forms]![frmMakeModelFix1]![List0].Column(0)));

It is supposed to select the records from the table CabinetRecords where the Manufacturer is the same as the Manufacturer
in the selected list box record. Once it works, I will add criteria to further specify that the Model must be the same as that in the
selected list box record.

It doesn't work and gives me the message: "Undefined function '[Forms]![frmMakeModelFix1]![List0].Column' in expression"

It works with the bound column when you don't need to specify the column, just the list name.

Will what I'm trying to do work?

wileedingo.

ASKER CERTIFIED SOLUTION

dathmagar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

heer2351

You can not reference a column in a query, but you can reference a control that is why dathmagar solution will work.

The other way to do this is to create the row source of the list box in the after update event of the combobox dynamically:

private sub List0_afterUpdate()

me!List0.rowsource = "SELECT Manufacturer, Model, Type, Size, In_Use FROM CabinetRecords WHERE Manufacturer = " & me![List0].Column(0)
end sub

So you are appending the value of the column in the listbox instead of putting it in the string.

wileedingo

ASKER

Thank you dathmagar and heer2351 for the further explanation.

I had noticed that I could reference the column from a text box as was required for the solution - and for that reason I felt
certain that I could reference the column as criteria in a query. I just thought I was doing it wrong.

thanks again.

wileedingo