How It Works
Using the Selector form is easy from the user's point of view. For many applications, you can use it as it is written with little or no modification.
The form is also easy to add to your application. That lets you show it to customers in an early prototype so you can get feedback right away.
To make query by example easy for the user and developer, the Selector form has to do a lot of work. Its four main tasks are gathering information about what it needs to do, initializing itself, performing queries, and letting the user select a record.
Listing 3 shows the code the Selector form uses to gather information about the fields it will display. It uses the private collections m_FieldDBName, m_FieldDisplayName, m_FieldAlignment, and m_FieldDBFormat to hold information about the fields it will manipulate. These collections are described in Table 1.
Table 1 Field Information Collections
The field name in the database
The field name displayed to the user
The field's alignment in the grid
Indicates whether the field is a string, number, or date
The form's AddField method saves information about a field. It simply adds the information describing the new field to the field information collections.
Listing 3 The Selector Form Uses this Code to Store Field Information.
' Configuration parameters. Private m_Connection As ADODB.Connection Private m_BaseQuery As String Private m_FromClause As String Private m_JoinStatements As String Private m_OrderByClause As String Private m_NumVisibleFields As Integer ' Field description collections. Private m_FieldDBName As Collection Private m_FieldDisplayName As Collection Private m_FieldAlignment As Collection Private m_FieldDBFormat As Collection ' Add entries for a field. Public Sub AddField(ByVal field_db_name As String, _ ByVal field_display_name As String, _ Optional ByVal field_db_format _ As SelectFormat = format_String, _ Optional ByVal field_alignment _ As MSFlexGridLib.AlignmentSettings = flexAlignLeftTop) ' Allocate the field collections if necessary. If m_FieldDBName Is Nothing Then Set m_FieldDBName = New Collection Set m_FieldDisplayName = New Collection Set m_FieldAlignment = New Collection Set m_FieldDBFormat = New Collection End If ' Save the new values. m_FieldDBName.Add field_db_name m_FieldDisplayName.Add field_display_name m_FieldAlignment.Add field_alignment m_FieldDBFormat.Add field_db_format End Sub
Listing 4 shows the Select form code that prepares the form for work. Most of the work is done in the Initialize subroutine. Initialize is long but straightforward.
Initialize first does a little error-checking to make sure its parameters make sense. For example, it makes sure that it has been passed a database connection and that at least one field has been defined.
The routine then saves the database connection, FROM clause, join statements, and ORDER BY clause. It checks the clauses to ensure they begin with FROM, WHERE, and ORDER BY, respectively.
Next, Initialize concatenates the field names into a basic SELECT statement of the following form:
SELECT field1, field2, ...
Initialize loops through the field display names to see how many are non-blank. Later, that number determines the number of columns in the result grid.
The subroutine then loads the controls it needs to display in the form's Query Values area. As it loads the controls, it sets their positions and TabIndex properties. After it has loaded the new controls, Initialize sizes the Query Values frame to fit the controls it contains.
The routine gives each field name combo box a blank item so the user can select nothing for that field. It then loops through the field names shown to the user and adds them to the field name combo boxes. As it does this, the routine keeps track of the field name that takes up the most room.
When it has finished loading the combo boxes, the Initialize subroutine makes them all wide enough to display the biggest field name. It also positions the operator combo boxes and the value text boxes.
Finally, subroutine Initialize calls DisplayColumnHeaders to set the grid control's column headers. DisplayColumnHeaders loops through the collection of field display names and places each in the first row of the corresponding column. It sets the column's width so it is wide enough to display the header value and sets the column's alignment using the value specified in the call to AddField.
Listing 4 The Selector Form Uses this Code to Initialize Itself.
' Configuration parameters. Private m_Connection As ADODB.Connection Private m_BaseQuery As String Private m_FromClause As String Private m_JoinStatements As String Private m_OrderByClause As String Private m_NumVisibleFields As Integer ' The data selected. Private m_DataSelected As Variant ' Save values used to configure the form. Public Sub Initialize(ByVal db_connection As ADODB.Connection, _ ByVal from_clause As String, _ Optional ByVal join_statements As String = "", _ Optional ByVal order_by_clause As String = "", _ Optional ByVal num_conditions As Integer = 4) Const GAP = 30 Dim i As Integer Dim j As Integer Dim frame_height As Single Dim ctl As Control Dim field_name As String Dim name_width As Single Dim max_name_width As Single Dim operator_left As Single Dim value_left As Single Dim tab_index As Integer ' *** Validate the parameters *** ' Make sure we have not already done this. If Not (m_Connection Is Nothing) Then Err.Raise err_AlreadyInitialized, _ "frmSelector.Initialize", _ "Selector already initialized" End If ' Make sure the parameters make some sense. If db_connection Is Nothing Then Err.Raise err_NoConnection, _ "frmSelector.Initialize", _ "Connection is Nothing" End If ' Make sure we have fields defined. If m_FieldDBName.Count < 1 Then Err.Raise err_NoFields, _ "frmSelector.Load", _ "No fields defined" End If ' *** Save the connection and query clauses *** Set m_Connection = db_connection m_FromClause = Trim$(from_clause) If UCase$(Left$(m_FromClause, 5)) <> "FROM " Then m_FromClause = "FROM " & m_FromClause End If m_JoinStatements = Trim$(join_statements) If UCase$(Left$(m_JoinStatements, 6)) <> "WHERE " Then m_JoinStatements = "WHERE " & m_JoinStatements End If m_OrderByClause = Trim$(order_by_clause) If Len(m_OrderByClause) > 0 Then If UCase$(Left$(m_OrderByClause, 9)) <> "ORDER BY " Then m_OrderByClause = "ORDER BY " & m_OrderByClause End If End If ' Build the SELECT clause. For i = 0 To m_FieldDBName.Count - 1 m_BaseQuery = m_BaseQuery & ", " & m_FieldDBName(i + 1) Next i m_BaseQuery = "SELECT " & Mid$(m_BaseQuery, 3) ' See how many columns are visible. m_NumVisibleFields = 0 For i = 0 To m_FieldDisplayName.Count - 1 If Len(m_FieldDisplayName(i + 1)) > 0 Then _ m_NumVisibleFields = m_NumVisibleFields + 1 Next i If m_NumVisibleFields < 1 Then Err.Raise err_NoVisibleFields, _ "frmSelector.Load", _ "No visible fields" End If ' Set the tab index for the first three controls. cboFieldName(0).TabIndex = 0 cboOperator(0).TabIndex = 1 txtFieldValue(0).TabIndex = 2 tab_index = 3 ' *** Load the selection controls *** Me.txtFieldValue(0).Height = cboFieldName(0).Height For i = 1 To num_conditions - 1 Load cboFieldName(i) With cboFieldName(i) Set .Container = fraQueryValues .Top = cboFieldName(i - 1).Top + _ cboFieldName(i - 1).Height + 2 * GAP .Visible = True .TabIndex = tab_index tab_index = tab_index + 1 End With Load cboOperator(i) With cboOperator(i) Set .Container = fraQueryValues .Top = cboFieldName(i).Top .Visible = True For j = 0 To cboOperator(0).ListCount - 1 .AddItem cboOperator(0).List(j) Next j .TabIndex = tab_index tab_index = tab_index + 1 End With Load txtFieldValue(i) With txtFieldValue(i) Set .Container = fraQueryValues .Top = cboFieldName(i).Top .Visible = True .TabIndex = tab_index tab_index = tab_index + 1 End With Next i ' Set the remaining tab indexes. cmdList.Default = True cmdList.TabIndex = tab_index tab_index = tab_index + 1 flxResults.TabIndex = tab_index tab_index = tab_index + 1 ' Make the value area as big as we can. frame_height = _ txtFieldValue(txtFieldValue.UBound).Top + _ txtFieldValue(0).Height + 120 fraQueryValues.Move 0, 0, ScaleWidth, frame_height ' *** Arrange the selection controls *** ' Start the field name ComboBoxes with a blank entry. For Each ctl In cboFieldName ctl.AddItem "" Next ctl ' Add the field names to the field name ComboBoxes. For i = 0 To m_FieldDisplayName.Count - 1 ' See if the display name is blank. field_name = m_FieldDisplayName(i + 1) If Len(field_name) > 0 Then For Each ctl In cboFieldName ctl.AddItem field_name Next ctl ' See how wide the field name is. name_width = TextWidth(field_name) ' Save the widest field name's width. If max_name_width < name_width Then max_name_width = name_width End If End If Next i ' Calculate the necessary positions of the controls. name_width = max_name_width + 480 operator_left = cboFieldName(0).Left + name_width + GAP value_left = operator_left + cboOperator(0).Width + GAP ' Size the controls so there is room for the ' column names. We don't bother to size the ' value fields because we need to do that when ' the form resizes anyway. For Each ctl In cboFieldName ctl.Width = name_width Next ctl For Each ctl In cboOperator ctl.Left = operator_left Next ctl For Each ctl In txtFieldValue ctl.Left = value_left Next ctl ' Display the column headers. DisplayColumnHeaders flxResults.Visible = True End Sub ' Display the column headers in the MSFlexGrid. Private Sub DisplayColumnHeaders() Dim i As Integer Dim c As Integer Dim field_name As String ' Copy the results into flxResults. flxResults.Rows = 2 flxResults.FixedRows = 1 flxResults.Rows = 1 flxResults.Cols = m_NumVisibleFields flxResults.FixedCols = 0 ' Display the column headings. For i = 0 To m_FieldDisplayName.Count - 1 field_name = m_FieldDisplayName(i + 1) If Len(field_name) > 0 Then flxResults.TextMatrix(0, c) = field_name flxResults.ColWidth(c) = TextWidth(field_name) + 120 flxResults.ColAlignment(c) = m_FieldAlignment(i + 1) c = c + 1 End If Next i End Sub
The other initialization-related task that the form performs is positioning its controls. The form's Resize event handler, shown in Listing 5, begins by making the Query Values area as wide as the form. It makes the value text boxes as wide as possible while still fitting within that area.
The Resize event handler then centers the List button beneath the Query Values area, and sizes the result grid to fill the rest of the form.
Listing 5 When the Form Resizes, the Resize Event Handler Rearranges Its Controls.
' Arrange the form's controls. Private Sub Form_Resize() Const GAP = 60 Dim i As Integer Dim value_width As Single Dim grid_top As Single Dim grid_height As Single ' Make fraQueryValues fill the width of the form. fraQueryValues.Width = ScaleWidth ' See how wide we can make the value fields. value_width = fraQueryValues.Width - txtFieldValue(0).Left - 120 If value_width < 120 Then value_width = 120 ' Size the values fields. For i = cboFieldName.LBound To cboFieldName.UBound txtFieldValue(i).Width = value_width Next i ' Put the List button under fraQueryValues. cmdList.Move (ScaleWidth - cmdList.Width) / 2, _ fraQueryValues.Top + fraQueryValues.Height + GAP ' Fill the rest of the form with the results grid. grid_top = cmdList.Top + cmdList.Height + GAP grid_height = ScaleHeight - grid_top If grid_height < 120 Then grid_height = 120 flxResults.Move 0, grid_top, ScaleWidth, grid_height End Sub
The most interesting part of the Selector form's code, shown in Listing 6, deals with composing and executing queries, and displaying the results. When the user clicks the List button, its event handler uses the ComposeQuery routine to create the query it must execute. It then calls DisplayResults to execute the query and display the results.
ComposeQuery starts with the form's basic join statement. If the program specified a join statement in its call to the form's Initialize method, this will have the form:
WHERE table1.field1 = table2.field2
If the call to initialize did not specify a join condition, the join clause is initially the word WHERE.
Next, ComposeQuery loops through the field name combo boxes. When it finds a non-blank selection, the routine calls AddToWhereClause to add that field's information to the WHERE clause.
When it has added all of the fields to the WHERE clause, the program determines whether the clause contains more than the word WHERE. If the clause contains nothing but WHERE, the routine empties it entirely. That lets ComposeQuery add the blank clause to the final query safely.
Finally, ComposeQuery concatenates the basic SELECT, FROM, and ORDER BY clauses with the newly constructed WHERE clause to build the final query.
Subroutine AddToWhereClause adds a field name, operator, and value to the query's WHERE clause. First, the routine determines whether the field name or operator is blank. If either of these is blank, there is not enough information to add to the WHERE clause so the routine exits.
If the field's value is blank, the operator must be one that does not require a value. If the operator is not IS NULL or IS NOT NULL, the routine exits.
If the WHERE clause already contains anything, the routine adds AND to the clause. It then tacks on the new field's name and the operator.
If the operator is IS NULL or IS NOT NULL, nothing else is needed, so the routine exits. If the operator is something else, the routine must also add the field's value. AddToWhereClause uses the field's format to determine whether the value should be formatted as a string, number, or date. It adds the value accordingly and returns the result.
After the List button's event handler builds the query with the ComposeQuery subroutine, it calls DisplayResults to execute the query and show the records selected. DisplayResults uses the database connection's Execute method to execute the query.
If Execute returns any rows, the routine uses the returned Recordset's GetRows method to copy the data into the Variant array m_DataSelected. DisplayResults then loops through the array, copying the results into the form's MSFlexGrid control.
As it displays values, the routine checks the values' data type to see which are stored as Currency data in the database. It displays Currency values with two digits of precision. If it did not take this extra step, the values 12.00 and 34.50 would be displayed as 12 and 34.5, not looking much like currency values.
After it has copied all the data into the grid control, DisplayResults sizes the columns. The routine makes each column wide enough to display the widest value it contains.
Listing 6 The Selector Form Uses this Code to Perform Queries and Display Results.
' The data selected. Private m_DataSelected As Variant ' Compose and execute the query. Private Sub cmdList_Click() Dim query As String ' Compose the query. query = ComposeQuery() ' Execute the query and display the results. DisplayResults query End Sub ' Build the SELECT statement. Private Function ComposeQuery() As String Dim i As Integer Dim where_clause As String Dim query As String ' Start with the join conditions. where_clause = m_JoinStatements ' Add the conditions specified by the user. For i = cboFieldName.LBound To cboFieldName.UBound ' Make sure the field name is not blank. If cboFieldName(i).ListIndex >= 0 Then AddToWhereClause where_clause, _ m_FieldDBName(cboFieldName(i).ListIndex + 1), _ cboOperator(i).Text, _ Replace(txtFieldValue(i).Text, "'", "''"), _ m_FieldDBFormat(cboFieldName(i).ListIndex + 1) End If Next i ' See if the WHERE clause is more than "WHERE " If Len(where_clause) <= 6 Then where_clause = "" ' Build the query. ComposeQuery = _ m_BaseQuery & " " & _ m_FromClause & " " & _ where_clause & " " & _ m_OrderByClause End Function ' Add this entry to the WHERE clause if appropriate. Private Sub AddToWhereClause(ByRef where_clause As String, _ ByVal field_name As String, ByVal operator As String, _ ByVal field_value As String, _ ByVal field_db_format As SelectFormat) Const DATE_FORMAT = "MM/DD/YYYY" ' If the field name or operator is blank, do nothing. If Len(field_name) = 0 Or Len(operator) = 0 Then Exit Sub ' If the value is blank, make sure the operator is ' IS NULL or IS NOT NULL. If Len(field_value) = 0 Then If (operator <> "IS NULL") And _ (operator <> "IS NOT NULL") _ Then Exit Sub End If ' If the clause is more than "WHERE ", add an AND. If Len(where_clause) > 6 _ Then where_clause = where_clause & " AND" ' Compose the condition. where_clause = where_clause & " " & _ field_name & " " & _ operator & " " ' If the operator is IS NULL or IS NOT NULL, we are done. If (operator = "IS NULL") Or _ (operator = "IS NOT NULL") _ Then Exit Sub ' Add quotes if necessary. Select Case field_db_format Case format_String ' Add quotes. field_value = "'" & field_value & "'" Case format_Numeric ' Add nothing. field_value = field_value Case format_Date ' Format for a date. field_value = "TO_DATE('" & _ Format$(field_value, DATE_FORMAT) & _ "', '" & DATE_FORMAT & "')" End Select ' Add the value. where_clause = where_clause & field_value End Sub ' Execute the query and display the results in ' the MSFlexGrid. Private Sub DisplayResults(ByVal query As String) Dim rs As ADODB.Recordset Dim max_col As Integer Dim r As Integer Dim c As Integer Dim i As Integer Dim fld As ADODB.Field Dim text_width As Single Dim max_width As Single ' Execute the query. Set rs = m_Connection.Execute(query, , adCmdText) ' Hide flxResults while we rebuild it. flxResults.Visible = False flxResults.Refresh ' Copy the results into flxResults. flxResults.Rows = 1 max_col = rs.Fields.Count - 1 ' Save the results in an array. If rs.EOF Then m_DataSelected = Null Else m_DataSelected = rs.GetRows() ' Display the rows. rs.MoveFirst r = 1 Do While Not rs.EOF ' Display this row. flxResults.Rows = r + 1 c = 0 For i = 0 To max_col If Len(m_FieldDisplayName(i + 1)) > 0 Then Set fld = rs.Fields(i) If fld.Type = adCurrency Then flxResults.TextMatrix(r, c) = _ Format$("" & fld.Value, "0.00") Else flxResults.TextMatrix(r, c) = "" & fld.Value End If c = c + 1 End If Next i ' Save this record's index. flxResults.RowData(r) = r - 1 ' Get the next record. r = r + 1 rs.MoveNext Loop End If rs.Close ' Size the columns. For c = 0 To flxResults.Cols - 1 max_width = 0 For r = 0 To flxResults.Rows - 1 text_width = TextWidth(flxResults.TextMatrix(r, c)) If max_width < text_width Then max_width = text_width Next r flxResults.ColWidth(c) = max_width + 120 Next c ' Display the FlexGrid. flxResults.Visible = True End Sub
The last task the Selector form performs is notifying the main program when the user selects a record. When the user double-clicks on a row in the result grid, the grid's DblClick event handler shown in Listing 7 event fires. The event handler copies the data for the selected row from the m_DataSelected array into a new one-dimensional Variant array. It then raises the RecordSelected event, passing it the new array. The main program can use the values in this array to see which record was selected.
Note that the program copies all the data for this record into the new array, not just the fields that should be visible to the user. This lets the program see fields that are selected by the query but hidden from the user.
Listing 7 When the User Double-clicks a Record, this Program Raises the Recordselected Event.
' We raise this event when the user selects a record. Public Event RecordSelected(ByVal values As Variant) ' Raise the RecordSelected event. Private Sub flxResults_DblClick() Dim i As Integer Dim r As Integer Dim values As Variant ' Make sure we selected data. If IsNull(m_DataSelected) Then Exit Sub ' Copy the selected data into the values variable. ReDim values(LBound(m_DataSelected, 1) To _ UBound(m_DataSelected, 1)) r = flxResults.RowData(flxResults.Row) For i = LBound(values) To UBound(values) values(i) = m_DataSelected(i, r) Next i ' Raise the event. RaiseEvent RecordSelected(values) End Sub