Adding (All) Options to Combo Boxes or List Boxes in Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to add an (All) entry to the list of items displayed in a list box or combo box in Access 2007.

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Joel Krist, Akona Systems

May 2007

Overview

Developers frequently use list boxes and combo boxes in Microsoft Office Access 2007 forms to let users specify selection criteria. Adding an (All) option to the list of items can be helpful in scenarios when users might want to select all the items in the list.

This Office Visual How To illustrates how to add an (All) entry to the list of items displayed in a list box or combo box in Access 2007.

Code It

Download the Code Sample

This section describes the two key steps:

  1. Creating the custom function that adds the (All) option to a list or combo box.

  2. Setting the RowSourceType property of the list or combo box to the name of the function.

To create the custom function

  1. In Office Access 2007, select the Database Tools tab on the Office Fluent Ribbon.

  2. In the Macro group, click Visual Basic.

  3. To create a new module, on the Insert menu, click Module.

  4. Next, add the following code for the AddAllToList function to the new module, and then save the module.

    Function AddAllToList(ctl As Control, lngID As Long, _
        lngRow As Long, lngCol As Long, _
        intCode As Integer) As Variant
    
        Static dbs As Database, rst As Recordset
        Static lngDisplayID As Long
        Static intDisplayCol As Integer
        Static strDisplayText As String
        Dim intSemiColon As Integer
    
        On Error GoTo Err_AddAllToList
            Select Case intCode
                Case acLBInitialize
    
                    ' See if function is already in use.
                    If lngDisplayID <> 0 Then
                        MsgBox "AddAllToList is already in use!"
                        AddAllToList = False
                        Exit Function
                    End If
    
                    ' Parse the display column and display text
                    ' from the Tag property.
                    intDisplayCol = 1
                    strDisplayText = "(All)"
                    If Not IsNull(ctl.Tag) And (ctl.Tag <> "") Then
                        intSemiColon = InStr(ctl.Tag, ";")
                        If intSemiColon = 0 Then
                            intDisplayCol = Val(ctl.Tag)
                        Else
                            intDisplayCol = _
                            Val(Left(ctl.Tag, intSemiColon - 1))
                            strDisplayText = Mid(ctl.Tag, intSemiColon + 1)
                        End If
                    End If
    
                    ' Open the recordset defined in the 
                    ' RowSource property.
                    Set dbs = CurrentDb
                    Set rst = dbs.OpenRecordset(ctl.RowSource, _
                        dbOpenSnapshot)
    
                    ' Record and return the lngID for this 
                    ' function.
                    lngDisplayID = Timer
                    AddAllToList = lngDisplayID
    
                Case acLBOpen
                    AddAllToList = lngDisplayID
    
                Case acLBGetRowCount
    
                    ' Return number of rows in recordset.
                    On Error Resume Next
    
                    rst.MoveLast
                    AddAllToList = rst.RecordCount + 1
    
                Case acLBGetColumnCount
    
                    ' Return number of fields (columns) in recordset.
                    AddAllToList = rst.Fields.Count
    
                Case acLBGetColumnWidth
                    AddAllToList = -1
    
                Case acLBGetValue
                    If lngRow = 0 Then
                        If lngCol = 0 Or lngCol = intDisplayCol Then
                            AddAllToList = strDisplayText
                        End If
                    Else
                        rst.MoveFirst
                        rst.Move lngRow - 1
                        AddAllToList = rst(lngCol)
                    End If
                Case acLBEnd
                    lngDisplayID = 0
                    rst.Close
            End Select
    
    Bye_AddAllToList:
        Exit Function
    
    Err_AddAllToList:
        MsgBox Err.Description, vbOKOnly + vbCritical, "AddAllToList"
        AddAllToList = False
        Resume Bye_AddAllToList
    End Function

The next step is to direct Access to call the new AddAllToList function when it inserts items into the item list of a list or combo box.

To set the RowSourceType property

  1. To open the form that contains the list box or combo box in design view, in the Navigation Pane, right-click the form's name, and then select Design View.

  2. Right-click the list or combo box on the form and select Properties.

  3. In the Properties dialog box, select the Data tab to display the data-related properties.

  4. Locate the RowSourceType property and set its value to AddAllToList.

Save the form and then display it in Form View. The list of items in the list or combo box should now display an (All) item.

Read It

Developers use list boxes and combo boxes in Access 2007 forms to let users specify selection criteria. For example, a common scenario is to bind a combo box display list to an Access database table and map the list items to the items in the table rows. In some scenarios, it might be useful to add an (All) option to the list of items so that the user can specify all items in the combo box.

To add an (All) item to the list, you first create a custom function that adds the option to the list box or combo box, and then you set the RowSourceType property to the name of this function.

See It Adding All Options to Combo Boxes or List Boxes

Watch the Video

Video Length: 00:07:26

File Size: 6.12 MB WMV

Visual How-To Code iconGrab the Code

Explore It