10 out of 16 rated this helpful - Rate this topic

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

Office 2007

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

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

Joel Krist, Akona Systems

May 2007

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.

Download the Code Sample

This section describes the two key steps:

Creating the custom function that adds the (All) option to a list or combo box.
Setting the RowSourceType property of the list or combo box to the name of the function.

To create the custom function

In Office Access 2007, select the Database Tools tab on the Office Fluent Ribbon.
In the Macro group, click Visual Basic.
To create a new module, on the Insert menu, click Module.

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

Copy

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

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.
Right-click the list or combo box on the form and select Properties.
In the Properties dialog box, select the Data tab to display the data-related properties.
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.

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.

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 icon Grab the Code

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Content Add

FAQ

Simple VBA for Selecting All

Here's a much simpler method for selecting all the items in a list box.

Private Sub btnSelectAll_Click()
    Dim n As Integer
    For n = 1 To Me.lstMyList.ListCount
        Me.lstMyList.Selected(n) = True
    Next
End Sub

To Unselect all items in the list box, use the same code but with "True" changed to "False."

Sheldon Penner

History

12/9/2011
Sheldon M. Penner

Access tools

I am at best a self-taught hack at Access so please forgive me if I am way off base. However, here is my two cents on Microsoft Office tools.

Some times I am completely amazed to find a tool that does exactly what I want the way I want it. I feel like I hit the lotery. Other times, I am totally disappointed by how limited options in tools can be. This discussion on creating an All option in a combo box is a great example. Why can't the inclusion of an All option be a simple property of the tool? No, I have to use about 50 lines of code to accomplish this.

Similarly, the other day I was working to add a drop down box in an Excel 2003 file. I came across an article that discussed VBA code for making a default value appear for the drop down. Wow! Perfect for my project. But, alas, I was never able to get it work. Once again a Holy Grail was waved in front of me but my arms were too short to reach it.

At that time, as again after reading this article, I wonder why don't the tool have such options built in? Some tools have so many properties that it seems teams of programmers toiled for years to think of them. Yet, it seems they sometimes miss not-so-far-fetched uses. I think if I am looking to achieve X with a tool, I am sure hundreds of other users and several Microsoft employees would have thought of it long before me. Yet, the answer is often using some VBA code.

History

6/9/2009
General Ledger

7/8/2010
Thomas Lee

Works great after changing to proper recordset type

Sometime ms will break there own rules and because old code in new programs. Change the

rst As Recordset to rst as DAO.Recordset and it will work. I could not get links and samples to download.

History

1/12/2010
pduckworth

7/8/2010
Thomas Lee