CODEWITHSUNDEEP
ms-accessms-access-2007
ASM2701
ASM2701

Reputation: 139

List a row of data in a listbox / combobox

I have a listbox that displays the result of a query. However how the database has been designed all the information is in rows as there is a magnitude of possible controls to one product.

I.e

Productname | Control 1 | Control 2 | Control 3 | Control 4 | Control 5 ........ | Control 14

Product 1 | 010101010 | 101010101 | ..........

I would like the List box to show the column heads on the left rather than the top, or the combo box to List the part no's.

Is this a property issue or is it done through VBA?

Upvotes: 0

Views: 280

Answers (1)

Mark C.
Mark C.

Reputation: 6460

So, for a combobox on one of my projects' main screens, I utilize a table as a Row Source. (Technically it's a query I suppose but it's only 2 fields from a table)

I have a table that looks like this:

+--------------+----------------+
| QuickEntryID | QuickEntryName |
+-------------------------------+
|      1       |   Add Part     |
|      2       |   Add Control  |
|      3       |   Add Product  |
+--------------+----------------+

(All of this can be done behind the scenes by making a few selections in the wizard.)

I use a SELECT statement to populate the combobox from this table. When right clicking on a combobox, in the Data tab, there is Row Source, this is where I put my query.

SELECT [QuickEntryLaunchTbl].[QuickEntryID], [QuickEntryLaunchTbl].[QuickEntryName] 
FROM QuickEntryLaunchTbl 
ORDER BY [QuickEntryName];

I format the column widths, under the Format tab: 0";1" (Or when you create the combobox, one of the steps is a checkbox to "Hide ID Field (recommended)" and I select that.

You should never have to try and arrange the Column name as the row source for a combobox, because that's not actual data. Now, if there was a table like this:

+--------------+----------------+
|    PartID    |   PartNumber   |
+-------------------------------+
|      1       |   010101010    |
|      2       |   110101010    |
|      3       |   210101010    |
+--------------+----------------+

Then your combobox would show the PartNumber in the drop down list, and it would actually be usable data.

I guess you could always create another table with column names as rows, but I'm not sure what use that would be.

Upvotes: 2

Related Questions