Populate Access combo box from a VBA array

In my quest to find Access VBA code to bubble sort a list box, I've discovered that it isn't possible, not directly anyway. The ItemData property of combo and list boxes is read-only. What good is iterating through a combo box if you can't rearrange the items?

So I had to roll my own. In this code sample, I am creating an array of all the form names, bubble sorting them and then putting the sorted array into a combo box on my main Access form. My end users can then select a form they want to open without having to give them direct access to the back-end tables/forms/queries menu.

The following code may be placed inside the Form_Load() event for your Startup form (that's where I put it, anyway).

' add form names to Open Form combo box
Dim arrList() As String
Dim formsCount As Long
formsCount = CurrentProject.AllForms.Count
ReDim arrList(1 To formsCount)

' populate array with list of scrubbed form names
With Me.cboFormToOpen
  Dim frm As Access.AccessObject
  Dim i As Long ' index counter
  i = 1
  For Each frm In CurrentProject.AllForms
    arrList(i) = Replace(Replace(frm.Name, "_", " "), "frm", "")
    i = i + 1
  Next frm

  ' sort array alphabetically using bubble sort
  Dim j As Long
  Dim strTemp As String
  For i = 1 To formsCount
    For j = i + 1 To formsCount
      If arrList(i) > arrList(j) Then
        strTemp = arrList(i)
        arrList(i) = arrList(j)
        arrList(j) = strTemp
      End If
    Next j
  Next i

  ' add sorted array items to combo box
  For i = 1 To formsCount
    .AddItem arrList(i), i - 1
  Next i

End With

Loops, loops loops! One loop to read all the form names in the current file into an array, another loop to sort them (a double loop), and a third loop to add them into the combo box. Even with around 15 forms, this code runs very quickly.

Notice I used the Replace function on the form name. That's because I use a special naming convention for my forms. For example, if I have a Cost form, I name it "frmCost_Form". By replacing "frm" with "" and "_" with " ", as my code does above, I can display my form in the combo box with a friendly name "Cost Form" that my end users will understand.

When I want to determine which form is selected (i.e. which form to open), all I have to do is prepend "frm" onto the combo box's current value, and replace " " with "_". It won't matter what is displayed in the combo box, as long as I name my forms the same way, the form name will always be correct.

Dim formToOpen As String
formToOpen = "frm" & Replace(Me.cboFormToOpen.Value, " ", "_")

i.e. "frmMy_Form" becomes "My Form"

When "My Form" is selected, formToOpen will be "frmMy_Form" which is the corresponding form name.

Related Articles:

• November 16, 2010 -- Table array hardcoding
• April 28, 2010 -- Building strings for arrays
• April 9, 2010 -- Building a string from an array