Microsoft KB Archive/124344

= ACC: How to Fill a List Box with Database Object Names =

Article ID: 124344

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q124344



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use Data Access Objects (DAO) to fill a list box or a combo box with the names of database objects. The following two sample user-defined functions are used:
 * The GetNames function. This function fills an array with object names from the active database.
 * The FillNameList function. This function calls the GetNames function and fills the list box or combo box.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



MORE INFORMATION
The following steps demonstrate how to fill a list box or combo box with database object names:  Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0). Create a blank new form called frmFillListBox.  Add the following two list box controls to the form:      Name: ListBox1 --     RowSourceType: Value List RowSource: 1;Tables;2;Queries;3;Forms;4;Reports;5;Macros;6;Modules ColumnCount: 2 ColumnWidths: 0.25 in;0.75 in     BoundColumn: 2

Name: ListBox2 ---     RowSourceType: FillNameList   Set the AfterUpdate property of ListBox1 control to the following event procedure: Private Sub ListBox1_AfterUpdate

ListBox2.Requery

End Sub  On the Tools menu, click References. Then, scroll down to the "Microsoft DAO 3.5 Object Library" and select it. The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.5 Object Library.  Create a new module called ListBoxes, and enter the following sample code in the module. Note that the FillNameList function requires that two variables be declared in the module's Declarations section so that data can be shared with the GetNames function. The GetNames function requires an object type and an array name argument. The function fills the array with the database object names and returns the number of rows in the array. Allowable object names are Tables, Query, Forms, Reports, Macros, and Modules. '**********************************   '  Declarations section of the module '**********************************

Option Compare Database   ' Use database order for ' string comparisons. Option Explicit

Dim list As String Dim entries

'**************************************   'Function FillNameList '**************************************

Function FillNameList (fld As Control, id, row, col, code) ' Accepts a control, an identifier, a row, a column, and a code.

On Error GoTo ErrorHandler

Dim ReturnVal Dim x As String

If IsNull(Forms![frmFillListBox]![ListBox1]) Then x = "Tables" Else x = Forms![frmFillListBox]![ListBox1] End If

ReturnVal = Null Select Case code Case 0                 ' Initialize. entries = 0 entries = GetNames(x, list) ReturnVal = entries Case 1                 ' Open. ReturnVal = Timer ' Unique ID number for the control. Case 3                 ' Get the number of rows. ReturnVal = entries Case 4                 ' Get the number of columns. ReturnVal = 1 Case 5                 ' Get the column width. ReturnVal = -1    ' Use the default width. Case 6                 ' Get the data. ReturnVal = list(row) Case 9                 ' End. ReDim list(0) entries = 0 End Select FillNameList = ReturnVal

ErrorHandler: Resume Next

End Function

'*********************   'Function GetNames '*********************

Function GetNames (objtype As String, names As String) Dim Conta As Container, Db As Database, I, Arlen

Set Db = CurrentDb

' In Microsoft Access 7.0 and earlier, the above line should read: '   '          Set Db = DbEngine.Workspaces(0).Databases(0)

Arlen = 0

If objtype = "Macros" Then objtype = "Scripts"  ' Macros are called scripts, internally. End If

Select Case objtype Case "Tables" If Db.Tabledefs.Count <> 0 Then Arlen = Db.Tabledefs.Count ReDim list(0 To Arlen - 1) I = 0 For I = 0 To (Arlen) - 1 names(i) = Db.Tabledefs(i).Name Next I         End If            Case "Queries" If Db.Querydefs.Count <> 0 Then Arlen = Db.Querydefs.Count ReDim list(0 To Arlen - 1) I = 0 For I = 0 To (Arlen) - 1 names(i) = Db.Querydefs(i).Name Next i              End If            Case Else Set Conta = Db.Containers(objtype) If Conta.Documents.Count <> 0 Then Arlen = Conta.Documents.Count ReDim list(0 To Conta.Documents.Count - 1) I = 0 For I = 0 To (Arlen) - 1  ' Fill the Names array ' with object names. names(i) = Conta.Documents(i).Name Next I                 End If            End Select Getnames = Arlen        ' Return the length of the array to the ' FillNameList function. End Function </li> View frmFillListBox in Form view, and select an object type in ListBox1. ListBox2 displays the names of objects of the type selected in ListBox1.</li></ol>

Additional query words: list function

Keywords: kbhowto kbprogramming kbusage KB124344

-

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

© Microsoft Corporation. All rights reserved.