ACC: How to Add "(all)" to a Combo Box or List Box Control

ID: Q128881


The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97


SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

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

The Developer Solutions sample application (Solutions.mdb) that comes with Microsoft Access demonstrates a similar technique for adding "(all)" to a list, but the AddAllToList() function has the following advantages:

  • It 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 read "none" or "nothing."


  • You do not need to change the code to change the optional text.


This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.


MORE INFORMATION

This section demonstrates how to create and use the sample user-defined Visual Basic function, AddAllToList().

How to Create the AddAllToList()function

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).


  2. Open any form that contains a combo box or list box control in Design view.


  3. On the View menu, click Code to open the form module. Add the following code to the module.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.


  4. 
          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 AddAllToList() Function

The following example demonstrates how you can use the AddAllToList() function on the Orders form in the sample database Northwind.mdb (or NWIND.MDB in version 2.0). 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 choose a salesperson for an order in which no salesperson was involved.
  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).


  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," and then paste it into the form module. Be sure to remove the underscore characters.


  4. Compile the loaded modules to check if you entered the function correctly, and then close the Module window.


  5. Set the following properties for the Salesperson combo box control:


  6. RowSourceType: AddAllToList
    Tag: 2;<No Salesperson>
  7. 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

You can find information,instructions, and examples in the Developer Solutions sample application (Solutions.mdb) included with Microsoft Access. For more information about adding "(all)" to a combo box or list box, open the Solutions.mdb database usually located in the ACCESS\SAMPLES folder. Select "Get more mileage from combo boxes, list boxes, subforms, and subreports" in the Select A Category Of Examples box, then "Add '(all)' to a list" in the Select An Example box.

Additional query words:

Keywords : kbusage FmsHowto
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: September 29, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.