Categorise combobox values

[munkee]

I have yet to see this but I would like to be using it in the near future on one of my forms.

I basically want to categorise my combo box/drop down menu in the following manner:

Documents
-Missing information
-Late
-Errors
-Incorrect version
Equipment
-Faulty
-Breakdown

etc etc

Thanks,
Chris

 

[John Big Booty]

You could do something similar with a set of Cascading Combo Boxes. The first Combo would select your Category, and the second would then be filtered to show the items related to that category.

 

[missinglinq]

John's response here reflects the way most of us here would address this problem. If you absolutely have to use a single Combobox for this task, as your post would seem to suggest, it can be done, but how depends on the RowSource for the Combobox.

If it's based on a table, as it should be (making later modification easier) you need to add a field for Categories.

For Missing information, Late, Errors and Incorrect version the Category would be Documents.

Likewise, for Faulty and Breakdown the Category would be Equipment.

You'd then sort on the Category field.

If the RowSource is a List that you've enered, you'd need to modify the list like this:

Missing information
Late
Errors
Incorrect version

would become

Documents Missing information
Documents Late
Documents Errors
Documents Incorrect version

and so forth.

Linq ;0)>

 

[John Big Booty]

Another potential solution is a Tree View Control. A little more complex to implement than a combo box but much closer, I believe, to what you are looking for.

 

[munkee]

Thank you for the info guys after a couple of hiccups I have got it working now.

I decided to create a table.

CategoryID
EventCat - The main category names
EventSubCat - The sub category potential errors

I realised I can easily combine the fields in most of my row sources by modifying the sql string for example:

tblEventCategories.EventCat & " - " & tblEventCategories.EventSubCat

So I would end up with the desired visual effect but they will always be bound by the CategoryID which I use for my graphing etc.


Something which I would like some information on is that I am using the dreaded Data Access Pages.

I have one which I basically use as a webform for users to submit nonconformance events, meaning anything that has gone wrong in the company that costs us money which the customer will not pay for. Anyway, unlike a normal access form I could not create a combobox/drop down list that would allow me to change a sql statement as I provided above.

There is:
ListBoundField
ListDisplayField
ListRowSource

I have tried to change the ListDisplayField in various ways to show the EventCat and the EventSubCat together but I can not. I have had to denormalize and create a 4th column in my tblEventCategories with the joined fields just so I can get them to display correctly on my DAP. This is not a huge issue I know but is there a better way?

 

[munkee]

Solved the problem:

In order to use sql with DAP dropdown/combo boxes simply create your string as you normally would, for example:

SELECT tblEventCats.NCEventID, tblEventCats.[NCEventCat] & " - " & [NCEventSubCat] AS NCType2 FROM tblEventCats

Then paste this in to the ListRowSource field within the properties menu of the combobox.

The page will refresh suddenly and your sql string will be replaced with a new value in ListRowSource. Example:

Recordset: DropdownList0_ListRowsource

Now ensure your control source is set to the table that your sql string looks up from, so for me that is:

EventCat

Change the boundfield to your ID values in that table

Change ListDisplayField to the concatenated field you created within your sql string. In mine I combined two columns within the table EventCat and named it as NCType.

NCType is now available for selection within the ListDisplayField.

All sorted =]