Adding and Deleting Listbox Items, Part 1

How to add and delete listbox items, and how to get the most out of the AddItem and RemoveItem methods.

Access Archon #172

Introduction

About six years ago, I wrote two Access Archon articles on working with listboxes, adding and deleting items from the lists.  I used tables as the listboxes’ row source, with append and delete queries to add or remove records from these tables.  Since that time, Microsoft has added two methods for working with listboxes, AddItem and RemoveItem.  These methods have their limitations, though, so in this article I will describe use of the new methods (and how to overcome their limitations), as well as row source tables (using VBA code, not queries).

Using Tables and DAO

The sample database, Listbox Items (AA 172).mdb, opens to a form where you can test adding and deleting listbox items, using tables and DAO recordsets.  This form is shown in Figure A:

Figure A.  A form with no items selected

Initially, the Available Items list is full, and the Selected Items list is empty.  You can select one or more items in the Available Items list, and click the right arrow button to add them to the Selected Items list, and similarly select items in that list and click the left arrow button to put them back in the Available Items list.  No matter how you move the items back and forth, they will always be in alphabetical order, as shown in Figure B.

Figure B.  A form with some items selected

VBA Code

The cmdAdd_Click procedure is listed below; the cmdRemove procedure is similar, just switching Available and Selected.

Private Sub cmdAdd_Click()

On Error GoTo ErrorHandler

   Set lstSelected = Me![lstSelectedItems]

   Set lstAvailable = Me![lstAvailableItems]

   ‘Check that at least one item has been selected

   If lstAvailable.ItemsSelected.Count = 0 Then

      MsgBox “Please select at least one item”

      lstAvailable.SetFocus

      GoTo ErrorHandlerExit

   End If

   Set dbs = CurrentDb

   Set rstSelected = dbs.OpenRecordset(“tblSelectedItems”, _

      dbOpenDynaset)

   Set rstAvailable = dbs.OpenRecordset(“tblAvailableItems”, _

      dbOpenDynaset)

   For Each varItem In lstAvailable.ItemsSelected

      strItem = Nz(lstAvailable.Column(0, varItem))

      Debug.Print “Selected item: ” & strItem

      ‘Append selected item to Selected Items list

      With rstSelected

         strSearch = “[CategoryName] = ” & Chr$(39) _

            & strItem & Chr$(39)

         .FindFirst strSearch

         If .NoMatch = True Then

            .AddNew

            ![CategoryName] = strItem

            .Update

         End If

      End With

      ‘Delete selected item from Available Items list

      With rstAvailable

         strSearch = “[CategoryName] = ” & Chr$(39) _

            & strItem & Chr$(39)

         .FindFirst strSearch

         If .NoMatch = False Then

            .Delete

         End If

      End With

   Next varItem

   rstSelected.Close

   rstAvailable.Close

   ‘Clear listbox selections

   intRows = lstAvailable.ListCount – 1

   For intIndex = 0 To intRows

      lstAvailable.Selected(intIndex) = False

   Next intIndex

   intRows = lstSelected.ListCount – 1

   For intIndex = 0 To intRows

      lstSelected.Selected(intIndex) = False

   Next intIndex

   lstAvailable.Requery

   lstSelected.Requery

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

End Sub

Private Sub Form_Load()

On Error GoTo ErrorHandler

   Dim strSQL As String

   DoCmd.RunCommand acCmdSizeToFitForm

   DoCmd.SetWarnings False

   ‘Clear tables of available and selected items

   strSQL = “DELETE * FROM tblSelectedItems”

   DoCmd.RunSQL strSQL

   strSQL = “DELETE * FROM tblAvailableItems”

   DoCmd.RunSQL strSQL

   ‘Fill table of available items from table of categories

   strSQL = “INSERT INTO tblAvailableItems (CategoryName) ” _

      & “SELECT CategoryName FROM tblCategories;”

   DoCmd.RunSQL strSQL

   Me![lstAvailableItems].Requery

   Me![lstSelectedItems].Requery

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” _

      & Err.Description

   Resume ErrorHandlerExit

End Sub

Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch172.zip, which is the last entry in the table of Access Archon columns for Access Watch.

See Also

• Adding and Deleting Listbox Items, Part 2