ACC1x: How to Move List Box Selections to a Second List Box

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes how to create a pair of list boxes to imitate the multiple selection behavior used in Microsoft Access wizards.

MORE INFORMATION

The example below demonstrates how to create a new form that contains two list boxes and three command buttons. You can add items to or delete items from the list using the command buttons or the properties of the list boxes:

1. Create the following new table:
   

   
         Table: Table1
         -----------------------------------------------
         Field Name: List (primary key)
            DataType: Text
            Field Size: 15
            Caption: Items that will be provided in list
         Field Name: Selected
            DataType: Text
            Field Size: 5
            Caption: Indicates if the item has been selected 




2. View the table in Datasheet view. Add five records to the table. For each record, type any text in the List field (for example a list of colors or cities), and type "YES" (without quotation marks) in the Selected field.



3. Create the following two new queries based on Table1:
   

   
         Query: Select Yes
         -------------------------------
         Field Name: List
            Show: Yes
            Criteria: [selected] = "YES"
   
         Query: Select No
         ------------------------------
         Field Name: List
            Show: Yes
            Criteria: [selected] = "NO" 




4. Enter the following three sample functions in a new or existing module:

   
         '=======================================================
         'The following function opens the table and changes the
         'selected value from YES to NO, and then executes the
         'query for the two list boxes so that they will display
         'the updated values.
         '=======================================================
         Function ADD ()
            Dim MyDB As Database
            Dim MyTable As Table
            Dim y As Control
   
            Set MyDB = CurrentDB()
            Set MyTable = MyDB.OpenTable("Table1")
            Set y = forms!SelectList!field0
   
            MyTable.index = "PrimaryKey"
            MyTable.Seek "=", y
   
            MyTable.Edit
            MyTable.[selected] = "no"
            MyTable.Update
   
            MyTable.Close
   
            DoCmd Requery "field0"
            DoCmd Requery "field1"
         End Function
   
         '=======================================================
         'The following function opens the table and changes the
         'selected value from NO to YES, and then executes the
         'query for the two list boxes so that they will display
         'the updated values.
         '=======================================================
         Function Del ()
            Dim MyDB As Database
            Dim MyTable As Table
            Dim y As Control
   
            Set MyDB = CurrentDB()
            Set MyTable = MyDB.OpenTable("Table1")
            Set y = forms!SelectList.field1
   
            MyTable.index = "primarykey"
            MyTable.Seek "=", y
            MyTable.Edit
            MyTable.[selected] = "yes"
            MyTable.Update
            MyTable.Close
   
            DoCmd Requery "field0"
            DoCmd Requery "field1"
         End Function
   
         '=======================================================
         'The following function sets all values in the Selected
         'field to YES, and then executes the query for the two
         'list boxes so that they will display the updated
         'values.
         '=======================================================
         Function Clear ()
            Dim MyDB As Database
            Dim MyTable As Table
   
            Set MyDB = CurrentDB()
            Set MyTable = MyDB.OpenTable("Table1")
   
            On Error GoTo erhandle
            MyTable.MoveFirst
   
            Do Until MyTable.EOF
               MyTable.Edit
               MyTable.[selected] = "yes"
               MyTable.Update
               MyTable.MoveNext
            Loop
   
         erhandle:
            Resume Next
   
            DoCmd Requery "field0"
            DoCmd Requery "field1"
         End Function 




5. Create the following new, unbound form, and then save it as SelectList:
   

   
         Object: list box
         -----------------------------
         ControlName: field0
            ControlSource:
            RowSourceType: Table/Query
            RowSource: Select Yes
            OnDblClick: =ADD()
   
         Object: list box
         -----------------------------
         ControlName: field1
            ControlSource:
            RowSourceType: Table/Query
            RowSource: Select No
            OnDblClick: =Del()
   
         Object: command button
         -----------------------
         ControlName: Button One
            Caption: Clear
            OnPush: =Clear()
   
         Object: command button
         -----------------------
         ControlName: Button Two
            Caption: Add item
            OnPush: =Add()
   
         Object: command button
         -------------------------
         ControlName: Button Three
            Caption: Delete item
            OnPush: =Del() 

REFERENCES

For more information about list boxes, search for "list box: creating" then "List Box Control" using the Microsoft Access Help menu.

Additional query words: listbox combo multiple fill function wizard

Keywords : kbusage FmsCmbo
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto