Listing Procedure to Create a Combo Box of Table and Query Names

Private Sub Form_Load()

'Declare an empty string to store a value l

ist.

Dim TblNames As String

TblNames = ""

'To keep lines below short, we'll store the

quotation mark

'as a variable named QM, and just refer to

it by

'name (QM) in code that follows.

Dim QM As String

QM = Chr(34)

'Loop through AllTables connection, add eac

h table's

'name to TblNames variable, each enclosed i

n quotation

'marks and followed by a semicolon.

Dim tbl As AccessObject

For Each tbl In CurrentData.AllTables

'Exclude MSys table names from list.

If Not Left(tbl.Name, 4) = "MSys" Then

TblNames = TblNames + QM + "Table: "

+ tbl.Name + QM + ";"

End If

Next tbl

'Next we loop through the AllQueries collection and

add their names.

Dim qry As AccessObject

For Each qry In CurrentData.AllQueries

TblNames = TblNames + QM + "Query: " + qry.Name

+ QM + ";"

Next qry

'TblNames string now has all table and query names.

'Make it the Row Source for the TblQryCombo control

Me!TblQryCombo.RowSourceType = "Value List"

Me!TblQryCombo.RowSource = TblNames

'Show first item as selected item in control.

Me!TblQryCombo.Value = Me!TblQryCombo.ItemData(0)

'Make sure user can only select a valid name.

Me!TblQryCombo.LimitToList = True

End Sub

Referring to the empty TblQryCombo control shown at the start of this section — and assuming that the code above is tied to that form's On Load event — by the time the form is visible to the user, the control will contain the names of all tables and queries in the current database, as in the example shown in Figure 10-6.

Figure 10-6:

TblQry Combo control after Form_ Load() procedure executes.

31 ThlQryComljoForm : Torm

sj

Choose a Table or Query

i able: Address BOok^^^^^^^^B

Table: Address.Book-

R

Table: Comirybobkup

a

Table: Email Messages Table

Table: MyBuslre'ss

Table: Order Details

Table:. Orders

Table: Products

Table: Sales Tax-Rates

Table: StateL'ookup

Query: Addre'ss-Bjök'AiphäQry t

■ Query; Contact/Lookup Qry

I Query: Customer LcMup Qry

0

ll

Make a list of form or report names

You can use a similar technique to Listing 10-2 to make a drop-down list display the names of all forms or all reports in the current database. For example, Figure 10-7 shows an empty ComboBox control named ObjCombo (for lack of a better name).

Figure 10-7:

Sample combo box named ObjCombo.

Figure 10-7:

Sample combo box named ObjCombo.

Listing Procedure

To fill the ObjCombo with a list of all form names in the current database, tie the form's On Load event to a procedure that creates a value list of form names, as follows in Listing 10-4.

0 -1

Post a comment