Programming combo box and list box controls in Microsoft Access

Here are a couple of posts from the MSDN archive. They had been taken out of general circulation, but have since been reintroduced to the library. Together they make up one of the most comprehensive reviews of developing with combo boxes and list boxes I've seen.

Programming Combo Box and List Box Controls in Microsoft Access, Part 1
Part one reviews the various options for supplying a list of values to a combo box or list box (all but one), including one I didn't know existed, the Field List setting. It also covers off how to find out what the user selected, including in a multi-select list box. It talks about using the NotInList event to make your lists dynamic. The only thing I can see missing is cascading combo boxes, which I cover off in these posts.

Programming Combo Box and List Box Controls in Microsoft Access, Part 2
Part two goes into using a function to provide values to the list. This one's a little more complex, but really not difficult, and provides functionality the other options don't.

Filtering Using Multi-Select List Boxes

Another really common Access question goes like this:

• How do I use a multi-select list box to filter my report?
• or How do I let my user select multiple items and then open a form or report showing those items?

List boxes allowing multiple selections are a pretty natural user interface. Think "print a report for three different departments" or "include these four employees in the sales graph". Creating and using criteria for your form or report with a multi-select list box is a lot different than a combo box or single select list box, but it's not hard at all once you've seen it in action and done it a couple of times.

Read more »

How to Synchronize Two Combo Boxes on a Form in Access

"I have two combo boxes (or list boxes) on my form. When the user picks an items in one list I want the choices in the second list to be narrowed down".

This remains one of the most frequent questions Access database users run up against early in their work with Access forms. It is sometimes referred to as cascading combo boxes, or dependant combo boxes, or synchronizing combo boxes.

Read more »

Adding Next and Previous buttons to your Combo Box or List Box

I often build forms with a combo box to select an item (by item I mean whatever the form's about- a project, a person, a product...) and subforms that show the related record(s). In a form I did recently I wanted to be able to browse from product to product. So I decided to add "Next" and "Previous" buttons.

It turns out that moving to the next or previous item in a combo box or list box is as simple as adding one to, or subtracting one from, the ListIndex of the combo box or list box.

There are only a few things to watch out for:

1. you have to SetFocus to the list box or combo box before you change it's ListIndex
2. if you try to move past the end or beginning of the list you'll get an error
3. when you want to check whether or not you're at the end of the list, remember that the ListIndex is zero-based; this means you're at the end when the ListIndex is equal to one less than the ListCount (the ListCount is the number of items in the list

The code below makes the Next and Previous buttons work. The basic code is pretty simple. There are a few niceties added, such as moving to the first item if you click the button and nothing is selected.

Then there's some fancy stuff I did for fun. The buttons have their tool tip changed to the list text for the item they'll move to- it's a kind of preview. To do that I had to write a routine to find the text for the next (or previous) item- it includes as many of the columns from the list box as are shown in the list. It does that by reading through the column widths to figure out which are not zero.

Download the
demo database

One more thing I found- when you change the ListIndex the control's AfterUpdate will fire. This works out fine, I think, it's just different than what would have happened if you had assigned a value to the control

Here's the code- give it a try!

Code:

Private Sub cmbProduct_AfterUpdate()
    SetToolTips ' update product preview tool tips
End Sub

Private Sub cmdNextItem_Click()
    ' advance to next item in combo box
    
    With Me.cmbProduct
        .SetFocus ' the combobox has to have the focus to set the ListIndex
        If IsNull(.Value) Then ' if combo box is null set it to the first item
            .ListIndex = 0
        ElseIf .ListIndex < .ListCount - 1 Then ' make sure it's not at the last item already
            .ListIndex = .ListIndex + 1 ' select the next item
        End If
    
    End With
End Sub

Private Sub cmdPreviousItem_Click()
    ' move to the previous item in the combo box

    With Me.cmbProduct
        .SetFocus
        If IsNull(.Value) Then ' if combo box is null set it to the first item
            .ListIndex = 0
        ElseIf .ListIndex > 0 Then ' make sure we're not already at the first item
            .ListIndex = .ListIndex - 1 ' select the previous item
        End If
                
    End With
    
End Sub

Private Function SetToolTips()
    ' this function sets the tool tips for the next and previous buttons to a
    ' preview of the product each will move the list to
    
    With Me.cmbProduct
        '  next item button
        If .ListIndex < .ListCount - 1 Then ' if not at the end of the list
            Me.cmdNextItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex + 1)
        Else
            Me.cmdNextItem.ControlTipText = "(At end of list)"
        End If
        
        ' previous product button
        If .ListIndex > 0 Then ' if not now at the beginning of the list
            Me.cmdPreviousItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex - 1)
        Else
            Me.cmdPreviousItem.ControlTipText = "(At beginning of list)"
        End If
    End With
End Function

Private Function ListItemText(ByVal ctlList As Control, ByVal lngRowNumber As Long) As String
    ' this function returns a string made up of all the visible columns for the
    ' selected row of a listbox or column box
    
    
    Dim varItems As Variant ' to hold array of column widths
    Dim lngCounter As Long ' counter for loop
    
    varItems = Split(ctlList.ColumnWidths, ";") ' split the list of column widths for reading
    For lngCounter = LBound(varItems) To UBound(varItems) ' read through the list of column widths
        If Val(CStr(varItems(lngCounter))) > 0 Then ' if columns is shown (width >0)
            If ListItemText <> vbNullString Then ' if list not empty add a separator
                ListItemText = ListItemText & " | "
            End If
            ListItemText = ListItemText & ctlList.Column(lngCounter, lngRowNumber)
        End If
    Next lngCounter
End Function

Private Sub Form_Load()
    SetToolTips ' inititalize product preview tool tips
End Sub