Populate Access combo box from a VBA array
June 16, 2009 • JP • 2 Comments • Rate This Article
• Links to this article
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).
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.
Access ·
Rate This Article:
(1 votes, average: 4.00 out of 5)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Unbind an Access Form
Next Post: Create Tiny URLs using VBA
Hi There, what i basically need is where and how to add a "Prograss Bar" to my existing VBA programme. I'm a beginner and doesn't know what section of my VBA programme to add the Progress Bar code.
Any help and advice from you would be much appreciated. I'm happy to post the existing code, though very long, if you wish. Alternatively i can attach it to a mail and send it to you directly.
Thanks,
Ola
Search the VBA Search Engine for "vba progress bar". It should turn up several useful hits.
http://www.codeforexcelandoutlook.com/vba-search-engine/