- User Features
- Developer Features
- Multitable Selection
- How It Works
- Enhancements
- Wrap Up
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Developer Features
Adding the Selector form to an application is relatively easy. The form provides methods for defining the fields the user can select and the ways they are displayed. Its Initialize method lets you specify the ADO database connection to use, the tables that contain the fields, the join and ORDER BY clauses, and the number of rows the form should display in its Query Values area.
When the user double-clicks on one of the records listed, the form raises a RecordSelected event so the main program can take action. To catch this event, the program declares a reference to the Selector form using the WithEvents keyword.
Private WithEvents customers_selector As frmSelector
This gives the customer_selector variable a RecordSelected event handler. When the user double-clicks a record, the event handler can take action. The event handler receives as a parameter a Variant containing the selected record's field values so the program can see which record was selected.
In the applications where I have used this selection technique, the program displays the record's detail form so the user can view and edit the data. The example program described here displays a message giving the customer name and ID, so you can see that it could find the record if necessary.
Private Sub customers_selector_RecordSelected(ByVal values As Variant)
MsgBox "Display Account information for " & _
values(1) & " (ID " & values(0) & ")"
End Sub
To display the Selector form, the program first creates a new instance of the form and saves it in the variable declared WithEvents. It then calls the form's AddField method to define each of the selection fields. The AddField method has this syntax:
AddField(field_db_name, field_display_name, field_db_format, _ field_alignment)
The parameters have these meanings:
field_db_name—The name of the field as it is defined in the database.
field_display_name—The name of the field as you want it displayed to the user.
field_db_format—One of format_String, format_Numeric, or format_Date. This tells the form how to format the field for the query. For example, strings need to be enclosed in quotes but numbers do not.
field_alignment—How you want the field aligned in the result grid.
If the field_display_name parameter is blank, the Selector form does not show the field to the user either in its field combo boxes or in the result grid. These fields are hidden from the user, but they are returned in the RecordSelected event handler so the program can use them.
The most obvious reason to do this is to obtain a record ID. Record IDs are meaningless to the user, but they let the program find the selected record quickly and easily. The example program uses this technique, selecting the CustomerId field from the database, but hiding it from the user.
After the program has defined the database fields, it calls the selector form's Initialize method.
Initialize(db_connection, from_clause, [join_statements], _ [order_by_clause], [num_conditions])
The parameters have these meanings:
db_connection—An open ADO Connection to the database.
from_clause——The list of database tables containing the fields.
join_statements—Conditions added to the WHERE clause to join the tables together.
order_by_clause—The query's ORDER BY clause.
num_conditions—The number of rows the form should display in its Query Values area.
After the program defines the Selector form's fields and calls its Initialize method, the form has all the information it needs so the program can display it. Listing 1 shows the code used by the example program to display the customer list form shown in Figure 1.
){kind=link}
Listing 1 The Example Program Uses this Code to Display a Customer Selection Form.
' List the customer records.
Private Sub cmdListCustomers_Click()
' Create the Selector form.
Set customers_selector = New frmSelector
' Define the selection fields.
customers_selector.AddField "CustomerId", ""
customers_selector.AddField "Company", "Company"
customers_selector.AddField "LastName", "Last Name"
customers_selector.AddField "FirstName", "First Name"
customers_selector.AddField "AccountBalance", "Account Balance", _
format_Numeric, flexAlignRightTop
' Initialize the Selector form.
customers_selector.Initialize m_Connection, _
"Customers", "", "Company, LastName, FirstName"
' Allow the user to resize the FlexGrid's entries.
customers_selector.flxResults.AllowUserResizing = flexResizeBoth
' Display the Selector form.
customers_selector.Show vbModal
Set customers_selector = Nothing
End Sub