Solved Problem with the format property of a combo box.

[AHeyne]

My goal is to display gray text in a combo box that does not contain a value (null), indicating that the user should select a value from the list. Once a value has been selected, it should be displayed in black text.

This works fine in a combo box with a single column of type Text (see attached sample database).

Now I would like to implement this behavior for a combo box that contains two columns, a hidden bound numeric value column and a text column to be displayed.

However, this does not work. I have already tried various combinations of column order and format property values. Nothing works.

Does anyone have a solution for this?

 

[isladogs]

To get the formatting you want, you must have the default text specified in the bound column.
So either
1. Change the column widths to e.g. 2cm;2cm and modify the format spec
Or
2. change the bound column to 2

However doing the latter will generate a new issue for you to deal with....

 

[arnelgp]

you can add 2 dummy textbox in front of those comboboxes.

 

[AHeyne]

To get the formatting you want, you must have the default text specified in the bound column.
So either
1. Change the column widths to e.g. 2cm;2cm and modify the format spec
Or
2. change the bound column to 2

However doing the latter will generate a new issue for you to deal with....

- Using suggestion 1 results in having the first number column and not the text column beiing displayed after selecting a value.
- Usind suggestion 2, as you already mentioned, has the disadvantage that I can no more read out the value property but have to read `uxMultiColumnSample.Column(0)` instead. But that would be bearable if there were no other option.

 

[AHeyne]

you can add 2 dummy textbox in front of those comboboxes.

That's a completely different approach (thanks for the idea), but I'm inclined to use Colin's option 2.

 

[xavier.batlle]

There's not much formatting I have been able to do with combo box controls, other than change background colors. I tried using conditional formatting to change font color from black to gray for discontinued values which users cannot select, but need to remain in the source table for historical purposes. Instead, I had to display a 2nd column with an X to indicate the value is discontinued.

I also wanted to display placeholder labels inside combo and textbox controls, like commonly done in web forms.

I do something similar, but I also show all inactive elements appear at the end, after all the active ones.

 

[CJ_London]

Another option (but more work) is to create a bespoke combo using a textbox and a continuous form that looks like the dropdown. Has other benefits such as being able to use colour and/or richtext in the dropdown

 

[sonic8]

My goal is to display gray text in a combo box that does not contain a value (null), indicating that the user should select a value from the list.

The Windows API way to achieve this would be to set a cue banner.
I'm not sure if this works with an Access control that not currently has the focus.

EM_SETCUEBANNER message (Commctrl.h) - Win32 apps

Sets the textual cue, or tip, that is displayed by the edit control to prompt the user for information.

learn.microsoft.com

 

[RonPaii]

With the use of a union query, conditional formatting and because the form is unbound setting the combo box value to 0 in Form_Load, I was able to get your affect.

Private Sub Form_Load()
mTboxWidth = Me.txtTemp1.Width
Me.cmbUnion = 0
End Sub

Added tblOne and qryOne for combo box source.

SELECT
    tblOne.ID,
    tblOne.Field1
FROM
    tblOne;

UNION ALL
SELECT
    TOP 1 0 AS ID,
    "<Select a Value>" AS Field1
FROM
    tblOne
ORDER BY
    tblOne.ID;

 

[AHeyne]

With the use of a union query, conditional formatting and because the form is unbound setting the combo box value to 0 in Form_Load, I was able to get your affect.

Private Sub Form_Load()
mTboxWidth = Me.txtTemp1.Width
Me.cmbUnion = 0
End Sub

Added tblOne and qryOne for combo box source.

SELECT
    tblOne.ID,
    tblOne.Field1
FROM
    tblOne;

UNION ALL
SELECT
    TOP 1 0 AS ID,
    "<Select a Value>" AS Field1
FROM
    tblOne
ORDER BY
    tblOne.ID;

View attachment 122167
View attachment 122168

That's also a completely different approach, so thank you too for this idea. But I'm still inclined to use Colin's option 2.

 

[AHeyne]

The Windows API way to achieve this would be to set a cue banner.
I'm not sure if this works with an Access control that not currently has the focus.

EM_SETCUEBANNER message (Commctrl.h) - Win32 apps

Sets the textual cue, or tip, that is displayed by the edit control to prompt the user for information.

learn.microsoft.com

Interesting approach, I'm not familiar with this API yet. I'll take a closer look at it.

 

[AHeyne]

@sonic8 : Unfortunately, the API does not seem to work with Microsoft Access form controls (only with 'real' Windows controls as used in a UserForm).

 

[Imb]

@sonic8 : Unfortunately, the API does not seem to work with Microsoft Access form controls (only with 'real' Windows controls as used in a UserForm).

What is the difference between a Access form control and a 'real' Windows control?
The kind of binding?

 

[arnelgp]

here is another demo, the code provided by Mr.DougSteel.

 

[AHeyne]

What is the difference between a Access form control and a 'real' Windows control?
The kind of binding?

Access controls (text boxes, combo boxes, etc.) are not classic Windows edit control windows, but rather Access-specific containers.
I can't describe it any other way, as I can't find any reference for this statement.
However, that's how I remember it from the past.
In classic Windows edit controls, for example, each control has its own window handle.
Access controls do not seem to have this, or at least Access does not disclose it.

 

[AHeyne]

here is another demo, the code provided by Mr.DougSteel.

That's also a nice solution that works. But I find the boilerplate code for this type of solution too large.
Sure, I could encapsulate it in a class, but I would still have additional controls. I still tend to favor the solution mentioned above.

 

[arnelgp]

if you have already a solution just ignore the sample db.
the demo db is for others, who will find your post and will
find it useful for them.

 

[sonic8]

In classic Windows edit controls, for example, each control has its own window handle.
Access controls do not seem to have this, or at least Access does not disclose it.

This is the essential difference in the context of my suggested solution.
And most Access controls are only real windows with their own window handle while having the focus. Once they loose the focus they become just "pixels on the screen" without existing the Windows window hierarchy.

 

[AHeyne]

This is the essential difference in the context of my suggested solution.

I'm not sure if you're trying to tell me that your suggestion should work... ...or not.

But I tried something yesterday (quickly, with ChatGPT's help) and didn't get any results either.
I've attached this non-functioning database (I've not yet tried to invest more time to check if there's maybe any bug in the api usage, maybe later).

 

[sonic8]

I'm not sure if you're trying to tell me that your suggestion should work... ...or not.

Neither. I just wanted to elaborate on the difference of standard Windows controls and Access controls with regards to my suggestion.

I expected the API approach to work at least while the control has the focus. Sadly, even this is not the case. I also quickly tried it now.
My approach is very similar to your ChatGPT based approach. Your code to get the control's Windows handle could be reduced to just:

ctrl.SetFocus
hWndFocus = GetFocus()

It doesn't make any difference though.