Open ComboBox Drop-down at current value - possible?

[Marshall Brooks]

Hard to describe, but I have a combobox named Status with essentially, sequential values, e.g.:
Review 1
Review 1 Complete
Review 2
Review 2 Complete
Review 3
Review 3 Complete

This works fine, but if the status is Review 3 and I want to change it to Review 3 Complete, the drop-down opens at Review 1 and the user has to scroll all the way down to Review 3 Complete. It would be more convenient if the drop-down opened at Review 3 and the user could scroll down to Review 3 Complete or up to Review 2, if for some reason that was necessary.

Is this possible?

 

[MajP]

Marshall,
See if this is of interest. The user had a similar workflow, and this was one of the cases where rolling your own control made it more intuitive. Sounds similar.

How to present a list of items to the user where some of those rows may be unavailable (i.e. -greyed out /disabled)

Hello everyone. Disclaimer: this project & it's data is confidential so I cannot post any code or the DB itself. I want to track whether or not an entity has been issued warning(s) for an infraction. The Enforcement_T has the following fields: 1. entity_ID...

www.access-programmers.co.uk

This works if you have a set workflow and forces the user to move through that. Doing this in the combos was not intuitive.

 

[MajP]

If you do not want to roll your own. Why not simply use a combobox and only show the next available choice? Why show other choices if you cannot select them. If they are on Review 1 I assume they cannot skip to Review 3, but must log Review 1 complete.

Something like this on the combos on enter event

dim strSql as string
strSql = "Select Top 1 StatusID, StatusName from tblStatus where StatusID > " & nz(me.statusID,0) & " Order BY StatusID"
me.somecombo.rowsource = strSql

That should be the default. If for some reason they need to pick something out of order then maybe add a button next to the combo to show all status and change the rowsource by removing the filter or leave the filter and remove the Select top.

 

[Marshall Brooks]

@MajP - Similar but not exactly what I am looking for. Let me provide more info:

There are approximately 30 possible statuses. So having a record on Status 25 and clicking the down arrow and having to scroll down from the top to get to item 26 is inconvenient. (And the combobox only shows 15 items so the current status may not be in the list. The flow USUALLY follows sequentially, but it isn't really required to do so.

If I could just get the drop-down to open at Item 25, I'd be happy.

Alternately, I suppose I could come up with some solution where clicking the status field showed a pop-up and the user selected from that and that changed that value.

Actually, I'm having a hard time replicating the issue. Sometimes if the status is line 25 and I click the down arrow I see the selections starting at around line 20 with line 25 highlighted (which is fine) and sometimes it seems to start at line 1, which I want to avoid ...

 

[Marshall Brooks]

Disregard. Can't replicate my own issue. Marking as solved.

 

[MajP]

I could not recreate it either. Maybe check how many list rows it displays. If you have more choices than list rows it should still work but it does get somewhat confusing. If you have 30 choices then maybe bump it up to 30 from the default of 16.

 

[Marshall Brooks]

If you have more choices than list rows it should still work but it does get somewhat confusing. If you have 30 choices then maybe bump it up to 30 from the default of 16.

I didn't realize that was an option, but I found it and it helps a lot. If the forum had a way to award you status points I would do it!

Fortunately, it is a max number - I bumped it up to 40 but it only shows the number of items there are (not 10 extra blank rows).

 

[Marshall Brooks]

Wanted to reply back and close the loop on this. I figured out what was happening ...

The database is somewhat used as an automated workflow.

So the responsible user can select Review 1, Review 2, or Review 3 from the drop-down, which are the procedure steps. THEY can't select anything NOT in the drop-down.

However, other users can do the review and when they click a different field, it will change the status via VBA to Review 3 Complete.

Since Review 3 Complete isn't one of the selections, the drop-down starts at the top.

Ideally, what I need is a way to have Review 3 Complete be in the list of selections after Review 3, but not have it displayed when you click the down arrow, but I doubt that is possible, but I'm okay with it now that I know what is going on.

 

[theDBguy]

Ideally, what I need is a way to have Review 3 Complete be in the list of selections after Review 3, but not have it displayed when you click the down arrow, but I doubt that is possible, but I'm okay with it now that I know what is going on.

Hiding the option may not be possible, but another approach may be to display it but not allow the user to select it.

 

[Marshall Brooks]

Hiding the option may not be possible, but another approach may be to display it but not allow the user to select it.

Not ideal, but good thought. Let me kick that around ...

 

[MajP]

You can hide the option using the same trick that some people use when doing cascading combos in continuous forms.
The trick is to overly the combo with a textbox that shows the selection. This textbox may pull in the value from a dlookup. When you key or mouse down in the textbox it sets focus to the combo behind it. This way it appears that you are displaying a value that does not exist in the rowsource of the combo.
Here is an example of this technique.

Cascade combobox on continuous form

Hi all, I would like add a cascade combo box to my subfom, but it's in continuous form and I have always error about parameter. It works on second combobox ("ValoriCarrieraIDcmb"), only if I put manually the number of ElencoCarrieraID, for example "8". How can I filter it? Thank you very much

www.access-programmers.co.uk

 

[Marshall Brooks]

@MajP - Either you didn't understand where I was going, or I didn't understand what you meant. I'm already displaying a value (say Review 2 Complete) that is not shown in the drop-down. (Not sure if this is b/c VBA allows me to set values in code that aren't in the selection list, or b/c I actually do allow items not in the list, but then I cancel them out if they don't match - see https://www.access-programmers.co.uk/forums/threads/form-field-and-sendkeys.329402/page-2) The issue is the Status drop-down doesn't have that value, so it starts at the top of the list. What I would like is for the drop-down to highlight Review 2, so that it is obvious the next step would be review 3.

Actually, I think I am getting somewhere, but I can't figure out where to place the code. I'm thinking something like this should work:

If Me.Status = "Review 2 Complete" Then
    Me.Status.ListIndex = 2 ' Assuming index 2 is Review 2
    ' or
    Me.Status.DefaultValue = "Review 2"
End If

But I tried placing the code in Status_OnClick and Status_MouseDown and it didn't do anything in either one. I think the actual event would be Status_DropDownClick based on https://eileenslounge.com/viewtopic.php?t=37118 - but that doesn't seem to be a selectable option and manually creating it didn't do anything.

So I'm not sure what event I should be placing the code in, and I'm not sure what parameter the code should be setting.

 

[Marshall Brooks]

Closer ...
I found out .listindex is read only.

https://www.access-programmers.co.u...index-of-an-access-combobox-using-vba.323060/ - Using .ItemData, when the downarrow is clicked, the status can immediately change to Review 2, and the drop-down immediately closes, but that isn't what I want.

https://www.access-programmers.co.u...index-of-an-access-combobox-using-vba.323060/ - The .selected property seems like it would be what I want, but setting Me.Status.Selected(2) = True doesn't seem to do anything, although from this, it seems like it should: https://learn.microsoft.com/en-us/office/vba/api/access.combobox.selected

 

[MajP]

As far as I know there is no way to drop down the list and highlight that value without selecting that value as the value of the combo.
Again I think I would make a custom control somehow.
If I understand this is Kind of a workflow. Where you likely want to go to the next step, but maybe you need to select a previous step.
What if you had your combo and three buttons next to it. "Show future", "Show All", "Select Next". The default would be show only future values. So it they are on Review 2 Complete the list only shows Review 3 and later. If for some reason you want to show something besides future choices then you can reset the row source to All choices. You could even simply pick the Select Next Status that picks the next status.

 

[Marshall Brooks]

@MajP - A bit more involved than I'd like, but that would work for what I need. Thank you!

 

[MajP]

@Marshall Brooks, Maybe something like this.
You have a workflow that most of the time you go in order. But there may be cases you skip a status or have to go back to a status.
Most of the time the user simply wants to pick the next status. So that is the default.
If you have to you can show all future statuses or even all statuses.
This assumes that most of the times they just care about the next status.

 

[MajP]

I posted the wrong version. It did not work. This should work.