Conditional formatting IN combobox dropdown

[hightekkrednek]

Good morning. I have a combobox on my form that I am working with. If a record has a bool field set to TRUE, I would like it to highlight that row in the combobox dropdown, while other rows in the combobox dropdown remain unaffected. Is this possible? Thank you very much.

[Bob Fitz]

Good morning. I have a combo box on my form that I am working with. If a record has a bool field set to TRUE, I would like it to highlight that row in the combobox dropdown, while other rows in the combo-box drop-down remain unaffected. Is this possible? Thank you very much.

I don't think that is possible

[Micron]

I have seen similar requests and the proposed idea was to replicate the list as a subform, but it was just an untried suggestion. Such a subform would never behave as a drop down list without a lot of code that is probably way more effort than it's worth. It would be an interesting exercise though.

[Micron]

As a follow up: I've been playing with the notion for some time and here are a few basics of what I've found when attempting this with textbox bound to a single field, assuming you want to approximate a combo look:
- you have to use a continuous form, otherwise the possibility of highlighting is zero, and you will not be able to eliminate headers if using a datasheet
- the subform needs several properties set (no border, cf default view, sized tight to textbox on all sides, vertical scrollbar only, no nav buttons or selectors, etc.)
- subform control must be tightly sized also
- the command button to drop down must be on the main form, otherwise there is a button for each record
- command button cannot overlay subform control, thus it can only get as close as the subform scrollbar (which is compressed into just buttons). Ended up turning scrollbar on and off with drop down/collapse and doesn't look too bad.
- drop down/collapse done in steps of 325 and -325 to simulate a combo
- drop down height is x times recordset count (325 for x is not bad for the font I used but not perfect)

I stopped there because conditional formatting the row would be the next step but I don't think I'll take it. That would likely need a transparent textbox whose background color property is controlled by cf. Seems like it should be possible though.

[Micron]

If anyone is interested, here's the code I just threw together for a test form upon which I didn't worry about proper controls names as I wasn't sure it would work at all. Note that I altered the size factor to 315 and it seems to look better.

Private Sub Command4_Click()
Dim ctl As Control
Dim frm As Form
Dim l As Long

Set frm = Me.Child0.Form
Set ctl = Me.Child0

If ctl.Height > 315 Then 'expanded now, so collapse it
    For l = 315 * frm.Recordset.RecordCount To 315 Step -315
        ctl.Height = l
        ctl.Form.ScrollBars = 0
        Me.Repaint
    Next
    Exit Sub
End If

If frm.Recordset.RecordCount > 0 Then
    For l = 315 To 320 * frm.Recordset.RecordCount Step 315
    ctl.Height = l
    Me.Repaint
    Next l
    ctl.Form.ScrollBars = 2
Else
    ctl.Height = 315
End If

End Sub

NOTE: obviously there would have to be a height limit based on the form. You wouldn't want to drop it down a thousand rows, for example. The fix then, would be to know the size factor (e.g. 315) and multiply that by a row factor that you pick (e.g. 10) and have the loop go from 315 to 315*10 (3150) for example. The scroll bars will allow access to the records that are below the bottom of the control. Alternately, you could code to use the record count if less than 11 but use the factors if it's 11 or more. That way, it would be dynamic up to a point, then fixed in height thereafter.