Combobox Gremlins

This time, I thought it would be nice to have a little fun for a change. So, in this blog, I will need your participation. I will post a question, and I hope many of you will provide me with "an" answer. It doesn't have to be "the" answer, just tell me what you "think" is the answer. Later on, we can all "vote" on the best one. Are you ready? Let's get started...

To participate in this exercise, you will have to download this file. When you open it, you will see that it contains one table and one form. The form only has one control on it, a combobox. If you click the dropdown for the combobox, you will be presented with several choices. So far so good.

Now, here's the "fun" part... If you go to the form's design view and select the combobox, you can take a look at the Row Source property and see what's in it. Did you see it? So, here's my question. Where are the choices for the combobox dropdown coming from?

Background

This sample database was actually part of something that I inherited recently. I was asked to modify the choices for the combobox and realized something was weird about it. I know where the choices originally came from, but I couldn't figure out how they are showing up in the dropdown. If I change the Row Source from "*" to something like "test," that change is not reflected in form view - I still see the old choices in the dropdown. If I remove the Row Source and change the Row Source Type from Value List to Table/Query, then it behaves normally with the dropdown showing empty. However, as soon as I change the Row Source Type back to Value List, the old dropdown list returns.

What do I think is happening?

Here is my speculation on the issue... You know how Microsoft said that a form can only have so much controls added to it over its lifetime? I was wondering how does it keep track of that number? So, my guess as to what's happening with this form is that the Row Source for the combobox is being tracked somehow and stored somewhere that when you change the Row Source Type back to Value List, the original Row Source returns. However, that doesn't explain why the old Row Source still displays even when I manually change the Row Source property.

What do you think?

I would like to hear your explanation of this issue, because I think it would be nice to learn the truth about something like this. You can either leave a comment below or send me an email.
I have always admitted that I don't know everything about Access, and this is one definite proof of that. But with your help, I hope to learn something new. Thank you for participating!

Original post date: May 28, 2015

Combobox Wizard

Almost everyone is familiar with the Combobox Wizard but the question that always comes up is why do you sometimes get three options but other times you only get two?

To activate the Combobox Wizard, make sure you have the "Use Control Wizards" option selected in the Ribbon before adding a Combobox to your form.



When you add a Combobox on your form, the Wizard might present you with the following screen:



But sometimes, you might see it like this:



Notice that in the first image, there is a third option as highlighted below:



So, why the difference? The standard answer has always been that the Combobox Wizard will present you the options based on whether the form is bound or not. In other words, if your form has a Record Source, then the Wizard adds the third option as a choice. Otherwise, it's not available if the Record Source is empty.

However, it was brought to my attention recently that the bound or unbound status of the form is only half of the reason whether you get the third option or not from the Wizard, and that is the main reason for this blog. It turns out that even when your form is definitely bound (has a Record Source), the Wizard might still not give you the third option when you try to add a Combobox to your form.

For instance, if you have either of the following Record Source for your form (see images below), then the Wizard will show the third option.



The above images show that the form is bound to a table or a query. But when you modify the record source to a SELECT statement as the one shown in the following image, then the Wizard does not present the third option although the form is clearly bound to a record source.

I'm not sure how the Combobox Wizard works, and this may be a limitation in its design; but if you use SELECT statements in your forms as a Record Source, which I often do, and you use the Combobox Wizard, then you need to be aware about this limitation.

However, just because the Combobox Wizard doesn't provide the third option in some cases, you can still implement that functionality on your form using VBA.

Here's a sample code to navigate the form to the record selected from the Combobox:

Dim rs As Object

Set rs = Me.RecordsetClone

With rs
.FindFirst "CustomerID=" & Me.cboCustomerID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

.Close

End With

Set rs = Nothing

Original post date: May 26, 2013