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

MORE INFORMATION

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.