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