Referencing a specific list box column from a query

[Brian Martin]

PARAMETERS [Forms]![frmChoose]!

• .[Column(1)] Text ( 255 );
  SELECT [Product List].[Part Number], [Product List].[Short Description], [Product List].[Full Description]
  FROM [Product List]
  WHERE ((([Product List].[Part Number]) Like " [Forms]![frmChoose]!
  • .[Column(1)]*"))
    ORDER BY [Product List].[Part Number] DESC;
    
    I'm trying to reference the first column in my listbox from my query and the above sql is what I have. This doesn't seem to work. What is wrong with it? Am I referencing it properly?

 

[Tay]

Could you not reference it by using the actual field name which appears in column 1 rather than the column number?

 

[Brian Martin]

worth a try

worth a try but it didn't work either. anybody anymore ideas

 

[Rich]

The first column is actually column(0)
referenced as [ListBox].[Column](0)

 

[Brian Martin]

I found that out thanks anyway. I think the problem might be with the Like "parameter*" part.

 

[Pat Hartman]

You cannot refer to the properties of a control in a query and ".Column(x)" is a property. SQL cannot interpret the VBA syntax. If you are trying to refer to the bound column of the combo (which is usually the first one), you can just use the control name:

PARAMETERS [Forms]![frmChoose]!

• Text ( 255 );
  
  If you are trying to refer to a column other than the bound column, you'll need to store the value in an unbound text box and reference that. So if you really want to refer to .column(1) (which is actually the second column of the combo's rowsource), create a hidden unbound control and put the following in the combo's AfterUpdate event.
  
  Me.YourNewHiddenControl = Me.list.Column(1)
  
  Then in the query, refer to [forms]![frmChoose]![YourNewHiddenControl]