List a row of data in a listbox / combobox

Question

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

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?

You never mention part no's in your post beside that you want to list them. Where are they? — Mark C., Mar 06 '14 at 13:46
Also, I am intrigued to see how your table structure is set up. Instead of using rows to store all of your data fields, you utilize columns, is that what you're saying? — Mark C., Mar 06 '14 at 13:49
My apologies Mark i had written this in a rush before a meeting. The part No's are stored under the control 1 etc, where i have shown binary numbers. As this is a system that will be used to give out the part No's. There are up to 14 possible controls for each product. The table structure is set up as shown. However i did not design the structure of the database. I would like to change the way the information is displayed. If i load it into a combo box or listbox the data always shows horizontal i would prefer it vertical. If that makes more sense. — ASM2701, Mar 06 '14 at 15:14
I understand the way you want the Combobox to function. My issue with your question is the design of your database. It's not normalized. [See this normalization post](http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization). Comboboxes are meant to utilize a Row Source. In your scenario, if your table was normalized, I would expect to see a list of Part_No's, however in its' current state it would return the column headings. Your database is about as relational as an Excel Spreadsheet.. The entire schema needs to be reconfigured.. — Mark C., Mar 06 '14 at 15:31
I will pass the comments onto the guy who designed the structure. I am only trying to design the front end part. Thank you for your comments. If you post it as an answer I'll mark it as the answer for you and up vote. — ASM2701, Mar 06 '14 at 15:53
Sure, I'll post an example of how I use a Combobox and maybe you can go from there. — Mark C., Mar 06 '14 at 17:27

score 2 · Accepted Answer · answered Mar 06 '14 at 17:46

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.

List a row of data in a listbox / combobox

1 Answers1