Dropdown Combo box with Sub Headings (1 Viewer)

[stu_c]

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

 

[bob fitz]

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

 

[MajP]

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

partTypeID	PartTypeName	PartTypeHeader	ParyTypeSort
1​	Engine	----- Engine ----	1​
2​	BodyWork	-- Body Work --	2​

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

then
tblParts tblParts

partID	partName	PartTypeID_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

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

partID	PartTypeHeader	partName
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)

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.

Now you must add code so you cannot select a Header

Private Sub cmboParts_BeforeUpdate(Cancel As Integer)
  If Me.cmboParts = -1 Then
   Cancel = True
   Me.cmboParts.Undo
  End If
End Sub

 

[MajP]

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.

 

[stu_c]

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

 

[MajP]

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

IDFKAutonumber	Category	CategoryLabel	DefaultTextTitle	DefaultText	SortOrder
1​	Body Work	--------- Body Work ---------	Front Bumper	Some long default text in here FB	6​
2​	Body Work	--------- Body Work ---------	Front Wing	Some long default text in here wing	5​
3​	Body Work	--------- Body Work ---------	Rear Bumper	Some long default text in here RB	7​
4​	Engine	----------- Engine ------------	Battery	Some long default text in here battery	3​
5​	Engine	----------- Engine ------------	Crank Sensor	Some long default text in here Crank Sensor	1​
6​	Engine	----------- Engine ------------	Manifold	Some long default text in here manifold	4​
7​	Engine	----------- Engine ------------	Timing Belt	Some long default text in here timing belt	2​

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

https://www.access-programmers.co.uk/forums/attachments/bodywork-png.111700/

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