ACC97: How to Dynamically Populate a List Box (302779)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
IMPORTANT: Changes that you make to a value list are not saved by using the following code. To save changes, add list items to a TableDef rather than recreating them dynamically each time the form loads.

Creating a Value List That Is Based on Table Data

1. Open the sample database, Northwind.mdb, and then create a new form named TestList.
2. Add the following objects to the form:

      Text box
      ----------------------
      Name: NewItem
   
      List box
      -----------------------
      Name: List0
   
      Command button
      -----------------------
      Name: cmdAdd
      Caption: Add Item
   
   					

3. Add the following code to the On Load event of the form:

   Private Sub Form_Load()
   
   Dim db As Database
   
   Dim rs As Recordset
   
   Dim RowList As String
   
   
   
   Set db = CurrentDb()
   
   Set rs = CurrentDb.OpenRecordset("Employees")
   
   rs.MoveFirst
   
   
   
   Do Until rs.EOF
   
      RowList = RowList & rs!LastName & ";"
   
      rs.MoveNext
   
   Loop
   
   rs.Close
   
   Set rs = Nothing
   
   
   
   'Set List Box RowSource Type and enter list data.
   
   Me!List0.RowSourceType = "Value List"
   
   Me!List0.RowSource = RowList
   
   Me!List0.Requery
   
   Me.Refresh
   
   End Sub
   					

4. Add the following code to On Click event of the command button:

   Private Sub cmdAdd_Click()
   
   Dim ListText As String
   
   Dim NewItem As String
   
   Dim NewList As String
   
   Dim ErrMsg As String
   
   ErrMsg = "Must enter a new item."
   
   
   
   On Error GoTo ErrorHandler
   
   
   
   ErrorHandler:
   
      ' Display error information.
   
      If Err.Number = 94 Then
   
          MsgBox ErrMsg
   
          GoTo Last
   
      Else
   
          Resume Next
   
      End If
   
      Resume Next
   
   
   
   ListText = Me!List0.RowSource
   
   newitem = Me!NewItem
   
   NewList = ListText & newitem & ";"
   
   Me!List0.RowSource = NewList
   
   Me!List0.Requery
   
   
   
   Last:
   
   End Sub
   					

5. Open the form in Form view, and then note that the list box is populated with data from the table.

Creating a Value List That Uses Tables in the Database

1. Open the sample database, Northwind.mdb, and then create a new form named TestCombo_tbl.
2. Add the following objects to the form:

      Combo box
      ----------------------
      Name: cboTable1
   					

3. Enter the following code in the On Load event of the form:

   Private Sub Form_Load()
   
      'Make sure combo boxes do not display data at load
   
      Me!cboTable1 = ""
   
      'Create rowsource for tables combo box
   
      Dim db As Database
   
      Dim x As Variant
   
      Dim strTbls As String
   
      strTbls = "" 'initialize rowsource string variable
   
      Set db = CurrentDb  'specify current database
   
      'Loop through TableDefs and add non-system table names to string
   
      For Each x In db.TableDefs
   
          If Left(x.Name, 4) <> "MSys" Then
   
              strTbls = strTbls & ";'" & x.Name & "'"
   
          End If
   
      Next x
   
      Set db = Nothing 'recover memory specified for variable
   
      strTbls = Right(strTbls, Len(strTbls) - 1) 'remove first ; from string
   
      Me!cboTable1.RowSourceType = "Value List" 'specify rowsource type
   
      Me!cboTable1.RowSource = strTbls 'specify string variable as rowsource
   
      Me!cboTable1.Requery 'populate combo box
   
   End Sub
   					

4. Open the form in Form view, and then note that the list box is populated with table names from the database.

Creating a Value List That Uses All Reports in the Database

1. Open the sample database, Northwind.mdb, and then create a new form named TestCombo_rpt.
2. Add the following objects to the form:

      Combo box
      ----------------------
      Name: cboReport
   					

3. Enter the following code in the On Load event of the form:

   Private Sub Form_Load()
   
      'Make sure combo boxes do not display data at load
   
      Me!cboReport = ""
   
      'Create rowsource for tables combo box
   
      Dim db As Database
   
      Dim strRpts As String
   
      Dim doc As Document
   
      Dim con As Container
   
      
   
      strRpts = "" 'initialize rowsource string variable
   
      Set db = CurrentDb  'specify current database
   
      'Loop through TableDefs and add non-system table names to string
   
           
   
          Set con = db.Containers("Reports")
   
          DoCmd.Echo False
   
          For Each doc In con.Documents
   
             DoCmd.OpenReport doc.Name, acViewDesign
   
             strRpts = strRpts & ";'" & Reports(doc.Name).Name & "'"
   
             DoCmd.Close acReport, doc.Name, acSaveNo
   
          Next
   
          DoCmd.Echo True
   
   
   
      Set db = Nothing 'recover memory specified for variable
   
      strRpts = Right(strRpts, Len(strRpts) - 1) 'remove first ; from string
   
      Me!cboReport.RowSourceType = "Value List" 'specify rowsource type
   
      Me!cboReport.RowSource = strRpts 'specify string variable as rowsource
   
      Me!cboReport.Requery 'populate combo box
   
   End Sub
   					

4. Open the form in Form view, and then note that the list box is populated with report names from the database.