Article ID: 126946
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 Q126946
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to create a sample user-defined Visual Basic for Applications function that you can use to fill a combo box or list box with the names of all the tables in the current database.
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 create and use the sample ListAllTables() function:
- Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0).
Create a new module and enter the following code in the module.
NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.Function ListAllTables (fld As Control, id As Long, row As _ Long, col As Long, code As Integer) Dim db As Database Dim tbdf As TableDef Static tbls(256) As String Static Entries As Integer Dim i As Integer Dim ReturnVal ReturnVal = Null Select Case code Case LB_INITIALIZE ' Initialize database. Set db = DBEngine.Workspaces(0).databases(0) Entries = 0 For i = 0 To db.tabledefs.count - 1 tbls(Entries) = db.tabledefs(i).name Entries = Entries + 1 Next i ReturnVal = Entries Case LB_OPEN ' Open. ReturnVal = Timer ' Unique ID number for control. Case LB_GETROWCOUNT ' Number of rows. ReturnVal = Entries Case LB_GETCOLUMNCOUNT ' Number of columns. ReturnVal = 1 Case LB_GETCOLUMNWIDTH ' Column width. ReturnVal = -1 ' Use the default width. Case LB_GETVALUE ' Get the data. ReturnVal = tbls(row) Case LB_END ' End. For Entries = 0 To 256 tbls(Entries) = "" Next End Select ListAllTables = ReturnVal End Function
- Create a blank new form that is not based on any table or query.
- Add a combo box control to the form.
- Set the RowSourceType property of the combo box to ListAllTables.
- View the form in Form view. Note that the combo box lists all the tables in the current database.
Additional query words: tablenames
Keywords: kbhowto kbprogramming KB126946