Microsoft KB Archive/302779

= ACC97: How to Dynamically Populate a List Box =

Article ID: 302779

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q302779



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article explains how to dynamically populate a list box that is based on table data or database object names.



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
 Open the sample database, Northwind.mdb, and then create a new form named TestList.  Add the following objects to the form:   Text box --  Name: NewItem

List box ---  Name: List0

Command button ---  Name: cmdAdd Caption: Add Item

  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(&quot;Employees&quot;)

rs.MoveFirst

Do Until rs.EOF

RowList = RowList & rs!LastName & &quot;;&quot;

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

'Set List Box RowSource Type and enter list data.

Me!List0.RowSourceType = &quot;Value List&quot;

Me!List0.RowSource = RowList

Me!List0.Requery

Me.Refresh

End Sub   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 = &quot;Must enter a new item.&quot;

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 & &quot;;&quot;

Me!List0.RowSource = NewList

Me!List0.Requery

Last:

End Sub  Open the form in Form view, and then note that the list box is populated with data from the table.</ol>

Creating a Value List That Uses Tables in the Database
<ol> Open the sample database, Northwind.mdb, and then create a new form named TestCombo_tbl.</li>  Add the following objects to the form: <pre class="fixed_text">  Combo box --  Name: cboTable1 </li>  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 = &quot;&quot;

'Create rowsource for tables combo box

Dim db As Database

Dim x As Variant

Dim strTbls As String

strTbls = &quot;&quot; '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) <> &quot;MSys&quot; Then

strTbls = strTbls & &quot;;'&quot; & x.Name & &quot;'&quot;

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 = &quot;Value List&quot; 'specify rowsource type

Me!cboTable1.RowSource = strTbls 'specify string variable as rowsource

Me!cboTable1.Requery 'populate combo box

End Sub </li> Open the form in Form view, and then note that the list box is populated with table names from the database.</li></ol>

Creating a Value List That Uses All Reports in the Database
<ol> Open the sample database, Northwind.mdb, and then create a new form named TestCombo_rpt.</li>  Add the following objects to the form: <pre class="fixed_text">  Combo box --  Name: cboReport </li>  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 = &quot;&quot;

'Create rowsource for tables combo box

Dim db As Database

Dim strRpts As String

Dim doc As Document

Dim con As Container

strRpts = &quot;&quot; '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(&quot;Reports&quot;)

DoCmd.Echo False

For Each doc In con.Documents

DoCmd.OpenReport doc.Name, acViewDesign

strRpts = strRpts & &quot;;'&quot; & Reports(doc.Name).Name & &quot;'&quot;

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 = &quot;Value List&quot; 'specify rowsource type

Me!cboReport.RowSource = strRpts 'specify string variable as rowsource

Me!cboReport.Requery 'populate combo box

End Sub </li> Open the form in Form view, and then note that the list box is populated with report names from the database.</li></ol>

Additional query words: inf

Keywords: kbhowto KB302779

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.