Displaying Database Objects in a Combo Box

Displaying Database Objects in a Combo Box

If you want to display objects, such as tables, in a Combo Box, then there are three different ways of doing this.

Firstly, you can use a System table, MSysObjects, and use this as the RowSource for the combo box. To view the System Tables in your database, set View|Options|System Objects to 'Yes'. Half a dozen tables now appear in the tables tab, but do not amend them, or your database will very likely become corrupt. Add a new combo box to the form, and click on the ellipsis next to the RowSource property to open up a query grid. Add the MSysObjects table to the query grid, and then Double Click on the fields 'Type' and 'Name'. Enter a criteria for 'Type' of 1, and a criteria for 'Name' of Left([Name],4)<>"MSys" to ignore the system tables. Remove the tick from the 'Show' box for 'Type' so that the user doesn't see this column, and set it to sort Ascending on the 'Name'. Close the query grid and accept the changes. The SQL should look like:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((Left([name],4)<>"MSys") AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

This will only include native Access tables - to also include attached Access tables, you will need to have a criteria to include their type (which is 6).

The second way is to use a value list that is calculated, by looping the relevant collection, when the combo box gets the focus. The main problem with using this method is that the RowSource is limited to 2048 characters, which obviously won't be enough if you have many Tables in the database. Add a combo box to the form, and change it's RowSourceType to 'Value List'. In the Combo Box's GotFocus event, have the following code:

Private Sub cboValueList_GotFocus()
    Dim strValueList As String
    Dim db As Database
    Dim tdf As TableDef
    Set db = DBEngine(0)(0)
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then strValueList = strValueList & ";" & tdf.Name
    Next tdf
    If Left(strValueList, 1) = ";" Then strValueList = Mid(strValueList, 2)
    Me!cboValueList.RowSource = strValueList
    Set db = Nothing
End Sub

The third way of displaying the required data is to use a user-defined function as the RowSourceType, leaving the RowSource blank. Below is an example of such a function:

Function fListTableDefs(fld As Control, id As Long, row As Long, col As Long, code As Integer)
    Dim db As Database
    Dim tdf As TableDef
    Static tdfs(512) As String
    Static Entries As Integer
    Dim i As Integer
    Dim varReturn As Variant
    varReturn = Null
    Select Case code
        Case acLBInitialize
            Set db = DBEngine(0)(0)
            Entries = 0
            For i = 0 To db.TableDefs.Count - 1
                If Left(db.TableDefs(i).Name, 4) <> "MSys" Then
                    tdfs(Entries) = db.TableDefs(i).Name
                    Entries = Entries + 1
                End If
            Next i
            varReturn = Entries
        Case acLBOpen
            varReturn = Timer
        Case acLBGetRowCount
            varReturn = Entries
        Case acLBGetColumnCount
            varReturn = 1
        Case acLBGetValue
            varReturn = tdfs(row)
        Case acLBEnd
            For Entries = 0 To 512
                tdfs(Entries) = ""
            Next
    End Select
    fListTableDefs = varReturn
End Function

Obviously, you can alter these to display the appropriate objects in the Combo/List Box by using the correct collection. Or alternatively, you could use an extra list box to allow the user to select the type of object, and then use that to select the correct object.

You can download an Access 97 database that shows these 3 different methods here.

Top

Last modified at 06/06/2006 14:54:06