Display Columns of Row in ComboBox (1 Viewer)

[Ksabai]

Hi,

I have a table as below:

ID City1 City2 City3 City4 City5 City6
Michigan aa bb cc dd ee ff

i have a combox cboState to select the State Michigan
In combobox cbocity i need itemdata as aa;bb;cc;dd;ee;ff

Iam sure there should be way other than an union query.

Request anyone to Help.

 

[pbaldy]

The table is not normalized. Each city should be a record, not a field:

http://www.baldyweb.com/CascadingCombo.htm

With your design, a union query would be used to convert the fields to records.

 

[MajP]

You really should fix that table structure, but..

SELECT 
 ID,
 City1 as City
FROM 
 YourTableName
UNION
SELECT 
 ID,
 City2 as City
FROM 
 YourTableName
UNION
SELECT 
 ID,
 City3 as City
FROM 
 YourTableName
UNION
SELECT 
 ID,
 City4 as City
FROM 
 YourTableName
UNION
SELECT 
 ID,
 City5 as City
FROM 
 YourTableName
UNION
SELECT 
 ID,
 City6 as City
FROM 
  YourTableName

 

[June7]

Alternative is combobox RowSourceType set for ValueList and VBA builds the list and sets RowSource.