Looking Up and Displaying Data

A lookup form is similar to a parameter query with a custom front end; the form simply collects input that drives a query. When you use forms and VBA, you can be flexible in how you gather input as well as in the type of information that you can return to users.

Creating a Lookup Form

The easiest way to implement a form that looks up information is to have the user type the lookup information in a text box and click a button to start the search. The text box should be unbound because it doesn't enter information into the database; it simply gathers search information from the user. A query uses the value specified in the text box to find the matching information.

Figure 5-9 shows a form that opens the qprHistoryfromTextBox query when the user types a customer ID in the text box and clicks Look It Up. The query finds the total quantity ordered of each product bought by that customer.

Figure 5-9. A form that performs a simple lookup operation.

Figure 5-10 shows the qprHistoryfromTextBox query in Design view. Notice that it is a simple query that sums the Quantity field of the Order Details table for each product the customer ordered. (The WHERE clause in the last column limits the query to rows in which the CustomerID field matches the value in the form's text box.)

Figure 5-10. The qprHistoryfromTextBox query in Design view.

The final element is a short VBA event procedure that fires when the user clicks Look It Up. The procedure has a single line that opens the query qprHistoryfromTextBox:

Private Sub cmdLookup_Click()     DoCmd.OpenQuery "qprHistoryfromTextBox" End Sub 

Using a combo box for user input

The form shown in Figure 5-11 has a better design than the one in Figure 5-9. Instead of forcing the user to guess the customer ID, it lets the user select the customer from a combo box. A procedure for the combo box's After Update event opens the qprHistoryfromComboBox query that uses the customer selected by the user, so the command button is unnecessary.

Figure 5-11. A better design for a lookup form.

NOTE

---

Combo box lookup forms can become prohibitively slow as the number of lookup items becomes large. In this situation, you have at least two choices. First, you can revert to the text box lookup form described above. Second, you can create a tiered system in which users make choices that restrict the range of items that a combo box makes available.

To give any combo box control over this functionality, follow these steps:

1. Set the control's Row Source Type property to Table/Query (the default).
2. Set the control's Row Source property to a SQL string that returns the fields you want. (The SQL string for the sample is SELECT CUSTOMERID, COMPANYNAME FROM CUSTOMERS.)
3. Set the control's Column Count property to 2.
4. Specify the column widths, separated by a semicolon. (The first value should always be 0.)

If you prefer, the Combo Box wizard can create the control for you. Simply ensure that the Control Wizards button on the Toolbox is depressed, and then add the combo box to the form.

Displaying results in a message box

The preceding samples suffer from two weaknesses. First, they pass values to and expose values from queries. This means that users can inadvertently damage the query's design. Second, a user can also modify the data underlying a query.

The sample lookup form at the top of Figure 5-12 remedies both of these deficiencies by using VBA and ActiveX Data Objects (ADO). The input form has the same look and feel as the form in Figure 5-11. While the return sets of the queries opened by both forms are identical, they are displayed in different ways. The sample in Figure 5-12 displays its return set in message boxes rather than in a query window in Datasheet view. (The sample uses as many message boxes as necessary to display its return set.) This protects the underlying data from inadvertent damage by a user.

Figure 5-12. The form at the top displays its result set in message boxes so that users can view the result set but not alter its underlying data.

The following procedure fires on the After Update event of the combo box in Figure 5-12. It executes a command based on a query to develop a return set. It then assigns the return set from the command to a recordset and displays the recordset using one or more message boxes.

Private Sub cboLookup_AfterUpdate() Dim ctl1 As Control Dim cmd1 As Command Dim rst1 As Recordset, str1 As String 'Set reference to ComboBox control. Set ctl1 = Me.Controls("cboLookup") 'Create and define command. 'Use ComboBox value in SQL string for command.     Set cmd1 = New ADODB.Command     With cmd1         .ActiveConnection = CurrentProject.Connection         .CommandText = "Select Customers.CompanyName, " & _             "Products.ProductName, " & _             "Sum([Order Details].Quantity) As TotalQuantity " & _             "From Products Inner Join ((Customers Inner Join Orders " & _             "ON Customers.CustomerID = Orders.CustomerID) " & _             "Inner Join [Order Details] ON " & _             "Orders.OrderID = [Order Details].OrderID) " & _             "ON Products.ProductID = [Order Details].ProductID " & _             "Where Customers.CustomerID = '" & ctl1.Value & "'" & _             "GROUP BY Customers.CompanyName, Products.ProductName;"         .CommandType = adCmdText         .Execute     End With 'Create recordset based on return set from SQL string.     Set rst1 = New ADODB.Recordset     rst1.Open cmd1, , adOpenKeyset, adLockOptimistic      'Loop through return set to display in message box(es) 'in blocks of 925 characters or less.     Do Until rst1.EOF         str1 = str1 & rst1.Fields(0) & ", " & _             rst1.Fields(1) & ", " & rst1.Fields(2)         str1 = str1 & vbCrLf         If Len(str1) > 925 Then             str1 = str1 & vbCrLf & "Click OK to see more " & _                 "in another message box"             MsgBox str1, vbInformation, _                 "Programming Microsoft Access 2000"             str1 = ""         End If         rst1.MoveNext     Loop     MsgBox str1, vbInformation, _         "Programming Microsoft Access 2000" End Sub 

I could not use the SQL code from a query window in Design view in this procedure because the SQL string for Command objects does not support lookup fields. Therefore, I added the Products table to the query design so that I could report each product's name in the return set instead of just a product ID from the Order Details table. Adding this extra table further complicated the join logic for the query. (See Chapter 4 for an introduction to the SQL statement syntax.)

A Do loop steps through the recordset sequentially and writes its contents to a string. At the end of each record, it inserts a carriage return and a linefeed. If the string length exceeds 925 characters, the procedure inserts a blank line and an instruction to view the continuation of the sales history for the customer in the next message block. A message box can hold just over 1000 characters. (The testmsgbox procedure in this chapter's sample database helps you determine the maximum number of characters that a message box can hold; each of the top 19 lines has two nonprinting characters.) Limiting additions to the current message box to 925 characters allows the message box to be filled without truncating any characters.

Dynamically Displaying Information

You can display data, such as a record, in a form, and you can even design a form so that users can view the record but not edit it. Figure 5-13 below shows a pair of forms that work together to let the user view a customer's record. The user selects a customer in the frmCustomerLookup form and clicks Show Customer In Form to open the Customers form, which displays the customer's record. (The Allow Edits, Allow Deletions, and Allow Additions properties of the Customers form are set to No, which prevents the user from changing the data.) The user can then click the Return To Customer Lookup Form button to transfer control back to the initial lookup form. The user can also launch another lookup or exit the application from this form.

Figure 5-13. These forms let the user select and view a customer's record.

The following elegant and simple event procedure is the code behind the Show Customer In Form button.

Private Sub cmdShowCustomer_Click() On Error GoTo ShowCustomerTrap Dim strValue As String, strMsg As String     strValue = Me.Combo2.Value     DoCmd.OpenForm "frmCustomers", acNormal, , _         "CustomerID = '" & strValue & "'"      ShowCustomerTrapExit:     Exit Sub      ShowCustomerTrap:     If Err.Number = 94 Then         MsgBox "Select a customer in the combo box " & _             "before attempting to open the Customer form.", _             vbExclamation, "Programming Microsoft Access 2000"     Else         strMsg = "Error number: " & Err.Number & "caused " & _             "failure. Its description is:" & vbCrLf & _             Err.Description         MsgBox strMsg, vbExclamation, _             "Programming Microsoft Access 2000"     End If     Resume ShowCustomerTrapExit      End Sub 

Charting a Subset of Data

The Microsoft Graph 2000 Chart object makes it easy to create professional looking charts. The object, which sits in an unbound object control on a form, can be bound to Access tables and queries, and you can choose from a wide selection of graph types and formatting options. (Double-click the object on a form in Design view to expose the custom menu for the object. When you finish using the Chart object menu, click on the form outside the object to restore the normal Access form Design menu.)

Creating a chart using the Chart wizard

You can add a Chart object manually, but using the Chart wizard is easier. Simply follow these steps:

1. Click the Forms object in the Database window and then click New.
2. Select Chart Wizard and the table or query on which your chart will be based, and then click OK.
3. Select the fields that will be on your chart datasheet and then click Next.
4. Select the chart type and click Next.
5. Drag and drop the desired field buttons to the chart and click Next.
6. Select Modify The Design Of The Form Or The Chart and click Finish.

You can add aggregation and formatting functions by modifying the SQL string in the Row Source property for the unbound object control containing the Chart object. (The wizard creates this statement for you.)

Using code to display the chart

Figure 5-14 below shows two forms that let the user chart sales for a selected month. The top form lets the user select any year in the Orders table. The combo box's After Update event opens the bottom form, which uses the Microsoft Graph 2000 Chart object to display total sales quantity by month for the specified year.

In Figure 5-14, the chart is based on a query that retrieves all orders from the specified year. The query translates each order date to the first of its month. (The underlying data remains unchanged.) This makes it simple to aggregate sales quantity by month, which in turn makes it easy to chart sales quantity by month. (The Chart wizard automatically sums sales quantity by month for a record source such as this.)

Figure 5-14. These forms let the user see monthly order data for a selected year.

The following three event procedures control the interaction between the two forms. The cboPickAYear_AfterUpdate procedure loads the charting form and minimizes the form in which the user selects a year. (You must minimize instead of close this form because the query for the chart determines what year the user selected using the combo box on the first form.)

Private Sub cboPickAYear_AfterUpdate()     DoCmd.Minimize     DoCmd.OpenForm "frmChart" End Sub Private Sub cmdClose_Click()     DoCmd.Close acForm, "frmPickAYear"     DoCmd.Close End Sub Private Sub cmdPickAYear_Click()     DoCmd.OpenForm "frmPickAYear"     DoCmd.Close acForm, "frmChart" End Sub