How to add '(all)' to a combo box or a list box control in an Access 2000 application (210290)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210290
For a Microsoft Access 97 version of this article, see 128881.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article shows you how to create a sample user-defined Microsoft Visual Basic function that is called AddAllToList(). This article also shows you how to create a Union Query. You can use either the function or the Union query to add "(all)" as the first row of any existing combo box or list box control in a Microsoft Access application.

The sample application, Solutions9.mdb, is available from the following Microsoft Web site:

Microsoft Access 2000: Building Applications with Forms and Reports
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bapp2000/html/mdbdownload.asp

The sample application demonstrates a similar technique for adding "(all)" to a list. However, the AddAllToList() function has the following advantages:
  • The AddAllToList() function works with any existing combo box or list box control.
  • You can specify in which column "(all)" is displayed.
  • You can specify what you want the optional text to be. For example, you can change "(all)" to "none" or "nothing."
  • You do not need to change the code to change the optional text.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

How to Create the AddAllToList() Function

To create and to use the sample user-defined Visual Basic function, AddAllToList(), follow these steps:
  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. Open any form that contains a combo box or a list box control in Design view.
  3. On the View menu, click Code to open the form module.
  4. Add the following code to the module:
    Function AddAllToList (C As Control, ID As Long, Row As Long, _
Col As Long, Code As Integer) As Variant

'***************************************************************
' FUNCTION: AddAllToList()
'
' PURPOSE:
'   Adds "(all)" as the first row of a combo box or list box.
'
' USAGE:
'   1. Create a combo box or list box that displays the data you
'      want.
'
'   2. Change the RowSourceType property from "Table/Query" to
'      "AddAllToList."
'
'   3. Set the value of the combo box or list box's Tag property to
'      the column number in which you want "(all)" to appear.
'
'   NOTE: Following the column number in the Tag property, you can
'   enter a semicolon (;) and then any text you want to appear
'   other than the default "all."
'
'         For example
'
'             Tag: 2;<None>
'
'         displays "<None>" in the second column of the list.
'
'***************************************************************
   Static DB As Database, RS As Recordset
   Static DISPLAYID As Long
   Static DISPLAYCOL As Integer
   Static DISPLAYTEXT As String
   Dim Semicolon As Integer

On Error GoTo Err_AddAllToList

   Select Case Code
      Case LB_INITIALIZE
         ' See if the function is already in use.
         If DISPLAYID <> 0 Then
            MsgBox "AddAllToList is already in use by another Control!"
            AddAllToList = False
            Exit Function
         End If

         ' Parse the display column and display text from the Tag
         ' property.
         DISPLAYCOL = 1
         DISPLAYTEXT = "(All)"
         If Not IsNull(C.Tag) Then
            SemiColon = InStr(C.Tag, ";")
            If SemiColon = 0 Then
               DISPLAYCOL = Val(C.Tag)
            Else
               DISPLAYCOL = Val(Left(C.Tag, SemiColon - 1))
               DISPLAYTEXT = Mid(C.Tag, SemiColon + 1)
            End If
         End If

         ' Open the recordset defined in the RowSource property.
         Set DB = DBEngine.Workspaces(0).Databases(0)
         Set RS = DB.OpenRecordset(C.RowSource, DB_OPEN_SNAPSHOT)

         ' Record and return the ID for this function.
         DISPLAYID = Timer
         AddAllToList = DISPLAYID

      Case LB_OPEN
         AddAllToList = DISPLAYID

      Case LB_GETROWCOUNT
         ' Return the number of rows in the recordset.
         RS.MoveLast
         AddAllToList = RS.RecordCount + 1

      Case LB_GETCOLUMNCOUNT
         ' Return the number of fields (columns) in the recordset.
         AddAllToList = RS.Fields.Count

      Case LB_GETCOLUMNWIDTH
         AddAllToList = -1

      Case LB_GETVALUE
         ' Are you requesting the first row?
         If Row = 0 Then
            ' Should the column display "(All)"?
            If Col = DISPLAYCOL - 1 Then
               ' If so, return "(All)."
               AddAllToList = DISPLAYTEXT
            Else
               ' Otherwise, return NULL.
               AddAllToList = Null
            End If
         Else
            ' Grab the record and field for the specified row/column.
            RS.MoveFirst
            RS.Move Row - 1
            AddAllToList = RS(Col)
         End If
      Case LB_END
         DISPLAYID = 0
         RS.Close
   End Select

Bye_AddAllToList:
   Exit Function

Err_AddAllToList:
   Beep: MsgBox Error$, 16, "AddAllToList"
   AddAllToList = False
   Resume Bye_AddAllToList
End Function
  5. Set the properties of the combo box or list box according to the instructions in the function header.

How to Use the Union Query Method

The following example demonstrates how you can use the Union query on the Employees form in the sample database Northwind.mdb. The query adds "All" as the first row of the ReportsTo combo box drop-down list so that you can clear the selection if you select a manager for an Employee.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Create a new Query in Design View.
  2. Close the Add Table dialog box.
  3. From the Query menu, select SQL Specific, and then click Union.
  4. Add the following SQL statement to the Query:
    SELECT LastName & ", " & FirstName, EmployeeID FROM Employees UNION SELECT '(All)', null FROM Employees
  5. Save the Query as EmpUnionAll, and then close the Query.
  6. Open the Employees form in Design view.
  7. Double-click on the ReportsTo combo box to bring up the control properties.
  8. On the Data tab of the Properties box, change the Row Source to the EmpUnionAll query that you created above.
  9. Change the Bound Column property on the Data tab from 1 to 2.
  10. On the Format tab, change the column width from 0 to 2;0.
  11. Open the form in Form view.

    Notice that you can now select '(All)' from the combo box list.

How to Use the AddAllToList() Function

The following example demonstrates how you can use the AddAllToList() function on the Orders form in the sample database Northwind.mdb. The function adds "<No Salesperson>" as the first row of the Salesperson combo box drop-down list so that you can clear the selection if you select a salesperson for an order in which no salesperson was involved.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. Open the Orders form in Design view, and then click Code on the View menu.
  3. Copy the AddAllToList() function that you created in the "How to Create the AddAllToList() Function" section, and then paste it into the form module. Be sure to remove any underscore characters.
  4. Compile the loaded modules to check whether you typed the function correctly, and then close the Module window.
  5. Set the following properties for the Salesperson combo box control: 
    RowSourceType: AddAllToList
Tag: 2;<No Salesperson>
  6. View the Orders form in Form view. Note that "<No Salesperson>" is displayed as the first row of the combo box drop-down list.

REFERENCES

For more information about combo boxes, click Microsoft Access Help on the Help menu, type combo boxes: what they are and how they work in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

To find information, instructions, and examples in the sample application (Solutions9.mdb), visit the following Microsoft Web site:

Microsoft Access 2000: Building Applications with Forms and Reports http://msdn.microsoft.com/library/en-us/bapp2000/html/mdbdownload.asp?frame=true

For more information about adding "(all)" to a combo box or list box, follow these steps:
  1. Open the Solutions9.mdb database.
  2. In the Select A Category Of Examples box, click Work with combo boxes, list boxes, subforms, and subreports.
  3. In the Select An Example box, click Add '(all)' to a list, and then click OK.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbfunctions kbhowto kbinfo kbusage KB210290
©2004 Microsoft Corporation. All rights reserved.