ACC: How to Delete Items from Combo/List Box After Selection

Last reviewed: August 29, 1997
Article ID: Q132026
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 demonstrates how you can remove an entry from a list box or combo box after you have selected that entry. This technique uses a parameter query to delete the selected entry from the underlying recordset and then requeries the list box to update the list. This is useful in situations in which, once an item has been selected, that item cannot be selected again.

This technique is not useful for large lists (more than 1000 items), because it would take too long to requery the list.

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

To create a list box whose items are deleted from the list box list after they have been selected, follow these steps:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0) and create the following query based on the Customers table:

          Query: ListTemp
          ----------------------
          Type: Make Table Query
    

          Field: Country
    
             Table: Customers
             Total: Group By
    
       You can start by creating a regular select query, adding the Customers
       table, and including only the Country field. Then, on the View menu,
       click Totals. Leave the Totals row as Group By. Then, on the Query menu,
       click Make Table.
    
       Specify ListTemp as the Table Name in the Make Table dialog box.
    
    

  2. Run the MakeListTemp query to create the ListTemp table.

  3. Create the following query based on the Customers table:

          Query: AppendListTemp
          ---------------------
          Type: Append Query
    

          Field: Country
    
             Table: Customers
             Total: Group By
             Sort: Ascending
             Append To: Country
    
       Specify ListTemp as the Table Name in the Append dialog box.
    
    

  4. Create the following query based on the ListTemp table:

          Query: DeleteListTemp
          ---------------------
          Type: Delete Query
    

          Field: Country
    
             Table: ListTemp
             Delete: Where
    
    

  5. Create the following form not based on any table or query:

          Form: Test
          -----------------------------
          Caption: TestForm
    

          List box:
    
             Name: ShrinkingList
             RowSourceType: Table/Query
             RowSource: ListTemp
             ColumnCount: 1
    
    

  6. Set the Test form's OnOpen property to the following event procedure:

             Private Sub Form_Open(Cancel As Integer)
                 Dim db As DATABASE
                 Dim qy As QueryDef
                 Set db = CurrentDb()
                 Set qy = db.QueryDefs("DeleteListTemp")
                 qy.Execute
                 Set qy = db.QueryDefs("AppendListTemp")
                 qy.Execute
                 qy.Close
             End Sub
    
       NOTE: In Microsoft Access version 2.0, omit the "Private" keyword. In
       that version Private is not valid for Sub procedures.
    
    

  7. Set the Test form's OnLoad property to the following event procedure:

             Private Sub Form_Load()
                 Me!ShrinkingList.Requery
             End Sub
    
    

  8. Set the ShrinkingList list box's AfterUpdate property to the following event procedure.

    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.

          Private Sub ShrinkingList_AfterUpdate ()
    
             On Local Error GoTo ShrinkingList_AfterUpdate_Err
    
       'NOTE: In Microsoft Access version 2.0, omit the Private keyword for the
       'Sub statement and the Local keyword for the On Error sentence. In
       'version 2.0, those keywords are not valid for these statements.
    
             Dim db As Database, rs As Recordset, criteria As String
             Dim UserMessage As String
             Set db = CurrentDB()
             ' Create recordset based on the list box RowSource.
             Set rs = db.OpenRecordset(Me!ShrinkingList.RowSource, _
             DB_OPEN_DYNASET)
             ' Check for the existence of Null values.
             If IsNull(Me!ShrinkingList) Then
                MsgBox "Shrinking List is Null!"
                Exit Sub
             End If
             UserMessage = Me!ShrinkingList
             criteria = "[Country] = '" & Me!ShrinkingList & "'"
             ' Locate the record that was selected in the list box
             ' list and delete it.
             rs.FindFirst criteria
             rs.Delete
             ' Refresh the List Box's RowSource property entries.
             Me!ShrinkingList.Requery
             ' Set the contents of the List Box to blank.
             Me!ShrinkingList = Null
             ' Prompt user.
             MsgBox "The Item " & UserMessage & " has been deleted!"
             Exit Sub
    
          ShrinkingList_AfterUpdate_Err:
              MsgBox Error$
              Exit Sub
          End Sub
    
          NOTE: The FindFirst criteria can be based on any column of the
          list box. If the list box column is not the column set in the
          BoundColumn property, change the criteria's list box reference to
          ShrinkingList.Column(n), where (n) is the column's zero-based ordinal
          position in the RowSource property field list.
    
    

  9. Save the Test form and view it in Form view. Click any element in the list box to delete it. Note that a message appears that states that the item you selected was deleted.

REFERENCES

For more information about list box properties, search the Help Index for "List Boxes, properties," or "List box: properties" or ask the Microsoft Access 97 Office Assistant.

Keywords          : kbprg PgmHowTo FmsCmbo
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.