combo box with a list of values

[vicgirl]

Hi,

I've created a combo box called Purpose_combo in Access. I populate this combo box with a list of values (such as "Response";"Reply"; "Advice")that are not generated from an SQL query. I only get its indexed number instead of the string when I use debug.print Purpose_Combo, such as I have got 1 for Response, 2 for Reply, 3 for Advice. When I use this value as a conditional statement in a sql query (such as, select * from table where table.Purpose like "'" & Purpose_Combo & "'"), the query doesn't work. The data type of table.Purpose is text.

I have tried it using Purpose_Combo.Column(1). However, it is only working in the VBA program, not in SQL query. When I put Purpose_Combo.Column(1) in the query and ran it, an error message is displayed saying that "Undefined function Column() in expression" . I think that Column() function is not recognized by SQL.

I have also tried to use Decode () in the query, such as select * from table where table.Purpuose like Decode(Purpose_Combo, 1, 'Response', 2, 'Reply', 3, 'Advice', ''), but I got the same error as using Purpose_Combo.Column(1).

Does anyone know how to solve this problem? Thanks in advance.



vicgirl

 

[Len Boorman]

The combo box has a number of properties.
among them
No of columns,
Bound column etc
Column widths

No of Columns is obvious
Bound Column. This is the column who value will be used. Not necessarily the displayed value

Column widths sounds obvious but if say you have 2 columns and the bound column is 1 then if Column widths is like 0cm;1.5cm then column 1 will not be shown but it will be the value used and saved in any add/edit record operation

Len B

 

[Rich]

Your combo box should be based on a table, you can then easily add further values, and if you add the table to the query the text will become available.
I'd only use a value list for something that's never going to need any further values added to the list, like say Am. Pm.
If that's the case then you have the wrong bound column.

 

[vicgirl]

However, there is only one Column in my combo box, which stores the string. And I have set Bound Column = 0. The value of Purpose_combo is a number. I think that is the way how Access stores the value list.

 

[Len Boorman]

I have set Bound Column = 0.

Think this is your problem. If only one column the set bound column to 1

L