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 about gathering input as well as the type of information 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-17 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.17: A form that performs a simple lookup operation.

The SQL statement for the qprHistoryfromTextBox query follows . Notice that the syntax specifies a parameter query. The parameter value is provided by the text box, txtCustomerID , on the frmTextBoxLookup form. This form appears at the top of Figure 5-17. If the frmTextBoxLookup form is closed when a user attempts to open the query, Access automatically prompts for a parameter to denote the value that the text box normally supplies . The query joins three tables to compute the quantity of each product ordered by the customer whose customer ID is in the txtCustomerID text box.

 SELECTCustomers.CompanyName,[OrderDetails].ProductID, Sum([OrderDetails].Quantity)ASTotalQuantity FROM(CustomersINNERJOINOrders ONCustomers.CustomerID=Orders.CustomerID) INNERJOIN[OrderDetails] ONOrders.OrderID=[OrderDetails].OrderID WHERECustomers.CustomerID=Forms.frmTextBoxLookup.txtCustomerID GROUPBYCustomers.CompanyName,[OrderDetails].ProductID 

The final element is a short VBA event procedure that fires when the user clicks the form's command button. The procedure has a single line that opens the query qprHistoryfromTextBox :

 PrivateSubcmdLookup_Click() DoCmd.OpenQuery "qprHistoryfromTextBox" EndSub 

Using a Combo Box for User Input

The form, frmComboLookupSQL , shown in Figure 5-18 has a better design than the one in Figure 5-17, provided that your list of customers isn't too long. Instead of forcing the user to enter a correct CustomerID field value, this form lets the user select the customer's name from a combo box. A procedure for the combo box's AfterUpdate event opens the qprHistoryfromComboBox query that uses the customer selected by the user, so the command button is unnecessary.

Figure 5.18: A Combo Box 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 (see Figure 5-17). Second, you can create a tiered system in which users make choices that restrict the range of items a combo box will make available. Essentially, you can split the list for one combo box across two or more other combo boxes. See "Programming Conditional Combo Boxes" in Chapter 12 for a sample implementing this approach.

To populate the combo box with the values for the application, follow these simple steps:

1. Set the control's RowSourceType property to Table/Query (the default).

2. Set the control's RowSource 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 ColumnCount 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 click the Control Wizards button on the Toolbox, and then add the combo box to the form.

Your goal is to populate the combo box with two columns . The first column contains the customer IDs, and its width is 0. The second column contains customer names, and its width is appropriate for displaying these names. When the user opens the combo box, he or she sees the customer names . However, when the user makes a selection, the combo box assumes the value of the CustomerID corresponding to the selected customer. You need to understand this distinction because the query must use the CustomerID value and not the customer name as a criterion value. The following SQL statement for the qprHistoryFromComboBox query reinforces this point. Notice that the WHERE clause sets the cboLookup control on the frmComboBoxLookup form to CustomerID ”not CompanyName .

 SELECTCustomers.CompanyName,[OrderDetails].ProductID, Sum([OrderDetails].Quantity)ASTotalQuantity FROM(CustomersINNERJOINOrders ONCustomers.CustomerID=Orders.CustomerID) INNERJOIN[OrderDetails] ONOrders.OrderID=[OrderDetails].OrderID WHERECustomers.CustomerID=Forms.frmComboBoxLookup.cboLookup GROUPBYCustomers.CompanyName,[OrderDetails].ProductID 

Displaying Results in a Message Box

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

The sample lookup form at the top of Figure 5-19 remedies both of these deficiencies by using VBA and ADO. The input form has the same look and feel as the form in Figure 5-18. While the result sets of the queries opened by both forms are identical, they are displayed in different ways. The sample in Figure 5-19 displays its result 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 result set.) This protects the underlying data from inadvertent damage by a user.

Figure 5.19: 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 AfterUpdate event of the combo box in Figure 5-19. It executes a command based on a query to develop a result set. It then assigns the result set from the command to a recordset and displays the recordset using one or more message boxes.

 PrivateSubcboLookup_AfterUpdate()  Dimctl1AsControl Dimcmd1AsCommand Dimrst1AsRecordset Dimstr1AsString     'Setreferencetocomboboxcontrol Setctl1=Me.Controls("cboLookup") 'Createanddefinecommand. 'UsecomboboxvalueinSQLstringforcommand. Setcmd1=NewADODB.Command Withcmd1 .ActiveConnection=CurrentProject.Connection .CommandText= "SELECTCustomers.CompanyName, " &_  "Products.ProductName, " &_  "SUM([OrderDetails].Quantity)ASTotalQuantity " &_  "FROMProductsINNERJOIN((CustomersINNERJOINOrders " &_  "ONCustomers.CustomerID=Orders.CustomerID) " &_  "INNERJOIN[OrderDetails]ON " &_  "Orders.OrderID=[OrderDetails].OrderID) " &_  "ONProducts.ProductID=[OrderDetails].ProductID " &_  "WHERECustomers.CustomerID='" &ctl1.Value& "'" &_  "GROUPBYCustomers.CompanyName,Products.ProductName;" .CommandType=adCmdText .Execute EndWith     'CreaterecordsetbasedonresultsetfromSQLstring Setrst1=NewADODB.Recordset rst1.Opencmd1,,adOpenKeyset,adLockOptimistic 'Loopthroughresultsettodisplayinmessagebox(es) 'inblocksof925charactersorless DoUntilrst1.EOF str1=str1&rst1.Fields(0)& ", " &_ rst1.Fields(1)& ", " &rst1.Fields(2) str1=str1&vbCrLf IfLen(str1)>925Then str1=str1&vbCrLf& "ClickOKtoseemore " &_  "in another message box" MsgBoxstr1,vbInformation,_  "ProgrammingMicrosoftAccess2003" str1= "" EndIf rst1.MoveNext Loop MsgBoxstr1,vbInformation,_  "ProgrammingMicrosoftAccess2003"     EndSub 

I could not use the SQL code from a query window in Design view in this procedure because the SQL string for a Command object 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 result 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, the loop 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 box. 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; note that each of the top 19 lines in this test routine's output contains 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-20 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 the Show Customer In Form button 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.20: These forms let the user select and view a customer's record.

The following elegant and simple event procedure is the code behind the command button with the Show Customer In Form caption:

 PrivateSubcmdShowCustomer_Click()  OnErrorGoToShowCustomerTrap Dimstr1AsString Dimstr2AsString     str1=Me.cboCompanyName.Value DoCmd.OpenForm "frmCustomers",acNormal,,_  "CustomerID='" &str1& "'"     ShowCustomerTrapExit: ExitSub     ShowCustomerTrap: IfErr.Number=94Then MsgBox "Selectacustomerinthecombobox " &_  "beforeattemptingtoopentheCustomerform.",_ vbExclamation, "ProgrammingMicrosoft2003" Else str2= "Errornumber: " &Err.Number& "caused " &_  "failure.Itsdescriptionis:" &vbCrLf&_ Err.Description MsgBoxstr2,vbExclamation,_  "ProgrammingMicrosoftAccess2003" EndIf ResumeShowCustomerTrapExit     EndSub 

User Interactivity Based on Parameter Queries and ADO

The dynamic output generated to this point relies on SQL string expressions with variables . The variable values change the value returned by the SQL string. However, parameter queries are another common and powerful approach to obtaining dynamic output from Access. This section illustrates a couple of approaches to processing Access parameter queries with ADO.

Both approaches use the same parameter query, which is not a stored query in Access. By leaving the parameter query unstored, the sample demonstrates how to secure a query from anyone who can list or view the queries in a database file. A function procedure, LookupOrders , in a standard module evaluates the parameter query and returns an ADO recordset with rows based on the parameter value passed to the function procedure. The sample has the procedure in the sole standard module within the Chapter05.mdb file. By using a procedure in a standard module, multiple forms can re-use the same code.

The following listing shows the logic behind the function procedure that takes a parameter value and returns an ADO Recordset object. The procedure's parameter is a CustomerID value, such as one from the Customers table in the Northwind database. The result set from the parameter query is a set of rows listing order information for the customer specified by the parameter value. The columns in the rowset include CustomerID , OrderID , and OrderDate . The SQL string for the parameter is the CommandText property value for an ADO Command object. The procedure applies the CreateParameter method, initially discussed in Chapter 2, to instantiate a parameter for the Command object that will allow a specification of the parameter by the CommandText property.

 FunctionLookupOrders(str1AsString)_ AsADODB.Recordset     Dimcmd1AsNewADODB.Command Dimprm1AsADODB.Parameter     'Specifyaparameterquerycutandpastefromthe 'SQLviewwindow cmd1.ActiveConnection=CurrentProject.Connection cmd1.CommandText= "PARAMETERS[Whichcustomer?]Text(5); " &_  "SELECTCustomers.CustomerID, " &_  "Orders.OrderDate,Orders.OrderID " &_  "FROMCustomersINNERJOINOrders " &_  "ONCustomers.CustomerID=Orders.CustomerID " &_  "WHERE(((Customers.CustomerID)=[Whichcustomer?]))"     'AssignthevalueintheText0controltothe 'parameterforcmd1 Setprm1=cmd1.CreateParameter("[Whichcustomer?]",_ adVarWChar,adParamInput,5) cmd1.Parameters.Appendprm1 prm1.Value=str1     'Executecmd1andreturntheresultset SetLookupOrders=cmd1.Execute     EndFunction 

Displaying Returned Rows via a Message Box

The form that displays the result set from the parameter query in a message box has three controls ”text box, its matching label, and a command button (see Figure 5-21). A user inputs a CustomerID code into the text box and clicks the command button. The click starts the presentation of one or more message boxes that display the orders for the customer corresponding to the CustomerID in the message box.

Figure 5.21: A form to display customer orders in one or more message boxes.

The code behind the form consists of two event procedures. The Form_Load event procedure formats the form by adding a caption and removing the record selector, dividing lines, and navigation buttons . The text specifying the Caption property appears in the form's border when the form is open in Form view (Figure 5-21 shows the form in Design view). The Click event procedure for the command button, cmdRunQuery , starts by invoking the LookupOrders function procedure described previously. The return from the function procedure is a recordset that the procedure displays in one or more text boxes. Within a Do loop, the procedure displays orders in blocks of 10 each. The procedure presents any orders not displayed within the loop immediately after exiting the loop. This can occur when the last block of orders contains fewer than 10 orders.

 PrivateSubForm_Load() Me.Caption= "ShowordersforCustomerIDinamessagebox" Me.RecordSelectors=False Me.DividingLines=False Me.NavigationButtons=False EndSub     PrivateSubcmdRunQuery_Click()      Dimrst1AsADODB.Recordset Dimstr1AsString Dimint1AsInteger     'Invokeparameterquerythroughfunction 'procedure Setrst1=LookupOrders(Text0.Value)     'Loopthroughtheresultsetfromtheparameter 'queryandformastringfordisplay;display 'tenrowsatatime int1=0 DoUntilrst1.EOF     str1=str1&rst1("CustomerID")& ", " &_ rst1("OrderDate")& ", " &rst1("OrderID")&_ vbCr rst1.MoveNext int1=int1+1 Ifint1=10Then MsgBoxstr1,vbInformation,_  " ProgrammingMicrosoftAccess2003" int1=0 str1= "" EndIf     Loop     'Displayanyoddlotofrowsintheresultset Ifint1>1Then MsgBoxstr1,vbInformation,_  "ProgrammingMicrosoftAccess2003" EndIf     EndSub 

Displaying Returned Rows Via a List Box

The form to display orders in a list box appears in Figure 5-22. The Combo Box control has the name cboCustomers . This control displays a list of customer names and stores the CustomerID of any customer a user selects. After the user makes a selection, the List Box control, lstOrders , displays the orders for the customer selected in the combo box. The default vertical scrollbar for the list box permits users to navigate to an order not showing in the control's window.

Figure 5.22: A form to display customer orders in the List Box control.

Two event procedures behind the form in Figure 5-22 enable the functionality described for the form, as well as format the layout of the form. The Form_Load event procedure populates cboCustomers with CustomerID and CustomerName column values from the Customers table. The load event procedure also specifies a Value List as the RowSourceType property for lstOrders . This setting enables the code to populate the lstOrders control with the values from the recordset returned by the LookupOrders procedure, instead of relying on an Access object such as a stored query. The Form_Load procedure closes by formatting the form. For example, it assigns a Caption property to the form declaring that the procedure displays orders in a list box.

The cboCustomers_AfterUpdate event procedure populates lstOrders with the orders for the customer selected in cboCustomers . The event procedure performs three tasks . First, it invokes the LookupOrders procedure to return a recordset with the orders for the customer selected in cboCustomers . Next, the procedure clears any prior contents for the RowSource property of the lstOrders List Box control. This prepares lstOrders to show just orders for the last selected customer from cboCustomers . Finally, the procedure iterates through the rows of the rst1 recordset and adds them one at a time to lstOrders . The AddItem method for List Box controls facilitates this objective. In order for this process to work, it is also necessary for the control's RowSourceType property to equal Value List . Here's the listing for the code behind the form in Figure 5-22.

 PrivateSubForm_Load() 'InitializecboCustomerscontrol WithcboCustomers .RowSource= "SELECTCustomerID, " &_  "CompanyNameFROMCustomers" .BoundColumn=1 .ColumnCount=2 .ColumnWidths= "0in;1.75in" .Width=2880 EndWith     'AssignValueListasRowSourceTypeproperty 'forlstOrders lstOrders.RowSourceType= "ValueList"     'Formatform Me.Caption= "Showordersforcustomerinalistbox" Me.RecordSelectors=False Me.DividingLines=False Me.NavigationButtons=False     EndSub     PrivateSubcboCustomers_AfterUpdate()  Dimrst1AsADODB.Recordset     'Openrecordsetbasedonselectionfor 'cboCustomers Setrst1=LookupOrders(cboCustomers.Value)     'ClearpriorRowSourcepropertysetting 'forlstOrders lstOrders.RowSource= ""     'Loopthroughtheresultsetfromtheparameter 'queryandformastringfordisplayinlistbox 'control DoUntilrst1.EOF str1=rst1("CustomerID")& ";" &_ rst1("OrderDate")& ";" &rst1("OrderID") lstOrders.AddItemstr1 rst1.MoveNext Loop     EndSub 

Charting a Subset of Data

The Microsoft Graph 2000 Chart object makes it easy to create professional-looking charts . (The Chart object is a mature Access development feature available since the first version of Access.) The object, which sits in an OLE Unbound Object Frame 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's custom menu, click on the form outside the object to restore the normal Access form Design menu.)

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. In the New Form dialog box, select Chart Wizard and the table or query on which your chart will be based, and then click OK.

3. On the first page of the Chart Wizard, 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 the Modify The Design Of The Form Or The Chart option and click Finish.

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

Figure 5-23 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 AfterUpdate 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-23, 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.23: These forms let the user see monthly sales 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.)