Dropdown Combo box with Sub Headings (1 Viewer)

stu_c

Registered User.
Joined
Sep 20, 2007
Messages
492
Hi All
we have a combo box list showing numerous different products, to make it a little easier is there a way to put sub heading (ENGINE & BOSYWORK) within it that cannot be clicked on and maybe even Bold?

There isn't enough on the list to warrant a second dropdown list to choose the type but to make it easier to read would be handy?
at the minute we have them all in a table in a sorting order with numbers for example

0 : ---- ENGINE ----
1: Crank Senor
2: Timing Belt
3: Battery
4: Manifold

10: --- BODYWORK ---
11: Front wing
12: Front Bumber
13: Rear Bumber
 
There isn't enough on the list to warrant a second dropdown list to choose the type but....
So how many products are in the list and how large will it need to become before you decide to structure things properly by creating a "ProductType" related to the "Products" table
 
Sorry that was accidently posted in this thread and probably overkill for this question.

If I was going to do this, I would assume you want to add more parts and more Part Types.
I would make two tables
tblPartTypes tblPartTypes

partTypeIDPartTypeNamePartTypeHeaderParyTypeSort
1​
Engine----- Engine ----
1​
2​
BodyWork-- Body Work --
2​

Should be "PartTypeSort" not Pary so you can fix.

then
tblParts tblParts

partIDpartNamePartTypeID_FK
1​
Crank Sensor
1​
2​
Timing Belt
1​
3​
Battery
1​
4​
Manifold
1​
5​
Front Wing
2​
6​
Front Bumper
2​
7​
Rear Bumper
2​
Now create the query
Code:
SELECT tblParts.partID, tblPartTypes.PartTypeHeader, tblParts.partName
FROM tblPartTypes INNER JOIN tblParts ON tblPartTypes.partTypeID = tblParts.PartTypeID_FK
ORDER BY tblPartTypes.ParyTypeSort, tblParts.partName;

qryParts qryParts

partIDPartTypeHeaderpartName
3​
----- Engine ----Battery
1​
----- Engine ----Crank Sensor
4​
----- Engine ----Manifold
2​
----- Engine ----Timing Belt
6​
-- Body Work --Front Bumper
5​
-- Body Work --Front Wing
7​
-- Body Work --Rear Bumper

Then to load the combo box (must be a value list, and have two columns, with the first column hidden)

Code:
Private Sub Form_Load()

  Dim cmbo As Access.ComboBox
  Dim rs As DAO.Recordset
  Dim tempHeader As String
  Dim tempLength

  Set cmbo = Me.cmboParts
  cmbo.RowSourceType = "value list"
  cmbo.ColumnCount = 2
  cmbo.ColumnWidths = "0in;2in"
  Set rs = CurrentDb.OpenRecordset("qryParts")
  Do While Not rs.EOF
    If rs!parttypeheader <> tempHeader Then
       cmbo.AddItem "-1;" & rs!parttypeheader
      tempHeader = rs!parttypeheader
    End If
    cmbo.AddItem rs!partID & ";" & rs!partName
    rs.MoveNext
  Loop

End Sub

You could hard wire a table, to do this but then there is no flexibility to add parts and part types.

BodyWork.png


Now you must add code so you cannot select a Header
Code:
Private Sub cmboParts_BeforeUpdate(Cancel As Integer)
  If Me.cmboParts = -1 Then
   Cancel = True
   Me.cmboParts.Undo
  End If
End Sub
 

Attachments

Last edited:
I put the Header in the table because if not you have to calculate how many characters to add making the code more complicated. If you only added a set amount (say 3) then the headers would be uneven

-- Engine --
-- Body Work --

I think it looks better even width.
Also adding some white space to the headers makes it easier to decipher. Having the headers in the table makes editing easy.
BodyWork.png
 
Last edited:
Hello,
I am a little confused as I currently have these in a table already called TBLDefaultText with the following field names
IDFKAutonumber
Category (will be Level 1)
Default TextTitle (shows on the Level 2 list)
DefaultTextBody (Once the user has selected this data is pasted into the TxtBox)


So if the user Selects Manifold, the DefaultTextBody is pasted into the box

ENGINE (LEVEL 1)
Crank Senor
Timing Belt
Battery
Manifold

BODYWORK (LEVEL 1)
Front wing
Front Bumber
Rear Bumber
 
I am a little confused as I currently have these in a table already called TBLDefaultText with the following field names
IDFKAutonumber
Category (will be Level 1)
Default TextTitle (shows on the Level 2 list)
DefaultTextBody (Once the user has selected this data is pasted into the TxtBox)
This type of information would be helpful to provide in the beginning so we are not guessing.

I provided a solution with a no code alternative and a code required alternative. In both cases you pick an item and it saves the default text to a text box. Not sure if that makes sense, but that is what was requested.

The demo is based on a table as described. I added a sort order column to force the sort, and a column for my label to use in the combobox

tblDefaultText


IDFKAutonumberCategoryCategoryLabelDefaultTextTitleDefaultTextSortOrder
1​
Body Work--------- Body Work ---------Front BumperSome long default text in here FB
6​
2​
Body Work--------- Body Work ---------Front WingSome long default text in here wing
5​
3​
Body Work--------- Body Work ---------Rear BumperSome long default text in here RB
7​
4​
Engine----------- Engine ------------BatterySome long default text in here battery
3​
5​
Engine----------- Engine ------------Crank SensorSome long default text in here Crank Sensor
1​
6​
Engine----------- Engine ------------ManifoldSome long default text in here manifold
4​
7​
Engine----------- Engine ------------Timing BeltSome long default text in here timing belt
2​

The solution is still the same as posted above. Is this the effect you want?

If you are confused, please provide more details of what you mean.
 

Attachments

Last edited:

Users who are viewing this thread

Top Bottom