Populating a Combo Box from a Recordset

Combo boxes, list boxes, and other bound controls maintain a connection to their underlying Jet tables. Combo boxes create their connection when you open the combo list. List boxes create a connection when you open the form. Neither connection closes until you close the form. Connections to tables create permanent user read locks; updating a table creates a temporary write lock during the update process.

A Jet database has a maximum of 255 concurrent user locks. You seldom, if ever, run out of locks in a single-user environment, but it's quite easy to hit the Jet lock limit with a widely deployed multiuser application that uses combo and list boxes. For example, 50 simultaneous users of the decision-support application described in Chapter 29, "Programming Combo and List Boxes," would generate 200 read locks, because two combo boxes and two list boxes are on the form. As the application gains more users, Jet might run out of read locks. In such a case, some users encounter a runtime error when attempting to open a combo box.

You can minimize the number of user read locks by populating combo boxes from a temporary Recordset object, which you close immediately after filling the combo list. Replacing the RowSource query of the combo box with a list of items, called a Value List, reduces the number of read locks by half. The following sections describe the changes required to the combo boxes and code of the Order Query with Criteria from Combo Boxes form (frmCombo1) to populate the two combo boxes (cboCountry and cboProduct) with a temporary Recordset.

Note

Client/server databases, such as SQL Server, can support a very large number of connections and locks, but connections and locks consume a significant amount of server memory. Minimizing connections and locks is an important element of client/server front-end design.


Creating frmCombo2 and Altering the Combo Box Design

The examples in this chapter enhance the example application you created in Chapter 29. To create a copy of frmCombo1 and modify the design of its two combo boxes, follow these steps:

1.
If Form1, used in the preceding examples, is still open, close it and don't save changes.

2.
Open the database in which you created frmCombo1, if it's not in your currently open database.

3.
In the Database window, click the Forms tab and select frmCombo1. Press Ctrl+C and then Ctrl+V, and type frmCombo2 in the Form Name text box of the Paste As dialog to create the new form.

4.
Open frmCombo2 in design mode, select the cboCountry combo box, and click the Properties button.

5.
Change the Row Source Type property value to Value List.

6.
Delete the entire SELECT query of the Row Source property text box (see Figure 30.9).

7.
Select cboProduct and repeat steps 5 and 6.

Figure 30.9. Changing the Row Source Type and Row Source property values of the cboCountry combo box.


Note

If you change to Form view and open the combo boxes, you see a single list item with remnants of the prior SELECT queries. Combo and list boxes persist (store) the last Row Source value until it's replaced by a new value.


Opening the Recordset and Populating the Combo Boxes

Value lists use the semicolon (;) as a record separator, similar to the format of comma-separated values in .csv files. You populate single-column value lists, such as cboCountry's, with single rows in a Row1Value;Row2Value;Row3Value;…RowiValue sequence, where i is the row count. Multicolumn value lists such as cboProduct's require a row-column sequence:

Row1Col1Value;…Row1ColjValue; Row2Col1Value;…Row2ColjValue;… RowiColjValue, where i is the row count and j is the column count. Thus, value list code requires that you create a semicolon-separated string in the appropriate format from the values of field(s) of the Recordset.

Note

Combo and list boxes that use value lists are limited to a maximum of 2,047 characters, 255 Long integers, or 511 Integers in the list. Thus, value lists should be used only for relatively small combo or list boxes.


To gain optimum performance, you use Snapshot-type (dbOpenSnapshot) Recordsets with the Forward-Only (dbOpenForwardOnly) option for populating combo and list boxes. Using Forward-Only Snapshot Recordsets is especially advantageous when connecting to client/server databases. The SQL UNION query statements for creating the two Recordsets are identical to those used by frmCombo1 to supply the RowSource property of the combo boxes.

The following steps add the code to open a temporary Recordset and populate the two combo boxes:

1.
Click the Code button to open the code-editing window for the frmCombo2 Class Module.

2.
Add these two lines to the Declarations section:

									Private dbCurrent As Database
Private rsfTemp As Recordset

3.
Move to the Form_Open event handler and delete the following four lines of code that assign SELECT queries to the RowSource property of the cboCountry and cboProduct combo boxes:

									Me!cboCountry.RowSource = strSQL5
Me!cboCountry.Requery
Me!cboProduct.RowSource = strSQL6
Me!cboProduct.Requery

4.
Add the following code to Form_Open to open the rsfTemp Forward-Only Snapshot Recordset to populate cboCountry:

									Dim strList As String
									Set dbCurrent = CurrentDb
Set rsfTemp = dbCurrent.OpenRecordset(strSQL5, _
   dbOpenSnapshot, dbOpenForwardOnly)
rsfTemp.MoveFirst
strList = ""
Do Until rsfTemp.EOF
   strList = strList & rsfTemp.Fields(0) & ";"
   rsfTemp.MoveNext
Loop
									Me!cboCountry.RowSource = strList

5.
Add this code to populate the two columns of cboProduct:

									Set rsfTemp = dbCurrent.OpenRecordset(strSQL6, _
   dbOpenSnapshot, dbOpenForwardOnly)
rsfTemp.MoveFirst
strList = ""
Do Until rsfTemp.EOF
   strList = strList & rsfTemp.Fields(0) & ";" & _
      rsfTemp.Fields(1) & ";"
   rsfTemp.MoveNext
Loop
rsfTemp.Close
Me!cboProduct.RowSource = strList

6.
From the Debug menu choose Compile and Save All Modules to check your typing and save your changes.

7.
Click frmCombo2, and click the Form view button to open the form.

8.
Verify that your value lists are correct by opening the two combo boxes and checking that their lists are identical to those in frmCombo1. Run a few queries to verify that the combo boxes return the proper values to the SQL statement that populates the Orders list box (lstOrders).

Altering the Sequence of Combo Box Lists

The UNION query that creates the two Recordsets adds the (All) item at the beginning of the list. Jet UNION queries are slower than conventional SELECT queries, so adding the (All) item by modifying the value list improves performance. Also, you can discourage users from running (All) queries by putting (All) at the end of the list. If USA is the most common country selection, you can move USA to the top of the list. To modify your value list code to make these changes, do the following:

1.
Return to Design view and open the code-editing window, if necessary.

2.
Add these two lines, which remove the UNION element of the query, to the Declarations section of the class module:

									Const strSQL10 = "SELECT DISTINCT Country FROM Customers " & _
   "ORDER BY Country;"
Const strSQL11 = "SELECT ProductID, ProductName FROM Products "

The DISTINCT reserved word is required in the country SQL statement to remove duplicate rows; UNION queries automatically remove duplicate rows.

3.
Change strSQL5 to strSQL10 and strSQL6 to strSQL11 in the two OpenRecordset statements of Form_Load.

4.
Change the first strList = "" statement to

strList = "USA;"

5.
Add this line after the first Loop statement:

strList = strList & "(All);"

6.
Add this line after the rsfTemp.Close statement:

strList = strList & "0;(All);"

7.
To prevent USA from appearing twice in the cboCountry list, wrap the strList = strList &rsfTemp.Fields(0) & ";" line with an If…Then statement:

									If rsfTemp.Fields(0) <> "USA" Then
   strList = strList & rsfTemp.Fields(0) & ";"
End If

Listing 30.2 includes all the code of the Form_Load event handler.

8.
Choose Compile and Save All Modules from the Debug menu to check your work and save your changes.

9.
Select the form and click the View button to test your modified combo boxes (see Figure 30.10).

Code Listing 30.2. Code to Populate the cboCountry and cboProduct Combo Boxes from a Temporary Recordset
							Private Sub Form_Load()
   Dim strList As String
							Set dbCurrent = CurrentDb

   'Populate the country combo box
   Set rsfTemp = dbCurrent.OpenRecordset(strSQL10, _
      dbOpenSnapshot, dbOpenForwardOnly)
   rsfTemp.MoveFirst
   strList = "USA;"
   Do Until rsfTemp.EOF
      If rsfTemp.Fields(0) <> "USA" Then
         strList = strList & rsfTemp.Fields(0) & ";"
      End If
      rsfTemp.MoveNext
   Loop
   strList = strList & "(All);"
   Me!cboCountry.RowSource = strList

   'Populate the product combo box
   Set rsfTemp = dbCurrent.OpenRecordset(strSQL11, _
      dbOpenSnapshot, dbOpenForwardOnly)
   rsfTemp.MoveFirst
   strList = ""
   Do Until rsfTemp.EOF
      strList = strList & rsfTemp.Fields(0) & ";" & _
         rsfTemp.Fields(1) & ";"
      rsfTemp.MoveNext
   Loop
   rsfTemp.Close
   strList = strList & "0;(All);"
   Me!cboProduct.RowSource = strList
End Sub

Figure 30.10. Revisions to the list sequences of the cboCountry and cboProduct combo boxes made possible by using a value list (double-exposure screen capture).


Filling List Boxes from Recordset Objects

You can use techniques similar to those for combo boxes to populate list boxes. List boxes that include many fields, have column headings, or both require additional code. You must test for queries that return no records and value lists with lengths greater than 2,047 characters. If your list box includes column headings, you must add the headings to the value list before appending rows from the Recordset. One benefit of using a Recordset is that you can detect RecordCount = 0 and display a message box that the query returned no records.

Fortunately, Access 97 lets you change the Row Source Type property of list and combo boxes with VBA code. If the length test indicates an overly long value list, you change the lstName.RowSourceType property value from "Value List" to "Query/Table" and supply the SQL statement used to create the Recordset as the lstName.RowSource property value.

You use the rsName.Fields (n).Name property value, where n equals 0 to one less than the rsName.Fields. Count value, to supply the value list elements for the column names. Although you could manually assign the list of field names to the beginning of the value list string, using the Name property of the Field object is a good programming practice because it makes your code more generic.

Note

SQL queries (QueryDef objects) that supply Row Source property values have an Access-specific property of the Field object, Caption, that provides the value of each column header. The Field object of a Recordset doesn't support Access-specific properties, such as Caption. If your table field names are cryptic and you specify a Caption property for each field, you can alias the field names to the Caption value with the Access SQL AS keyword in your Recordset query.


The following steps convert the lstOrders list box to the value list type for queries that return less than 2,048 characters:

1.
Add this line to the Declarations section of frmCombo2:

									Const strMsg8 = "Orders by Country and Product"

2.
Scroll to lstOrders_GotFocus event handler and add the following as the first line of code:

									Dim strList As String

3.
Delete all the code between the first and last End If statements of lstOrders_GotFocus.

4.
Replace the deleted code with the following:

									Set rsfTemp = dbCurrent.OpenRecordset(strSQL, _
   dbOpenSnapshot, dbOpenForwardOnly)
If rsfTemp.RecordCount Then
   rsfTemp.MoveFirst
   For intCtr = 0 To rsfTemp. Fields.Count - 1
      strList = strList & rsfTemp.Fields(intCtr).Name & ";"
   Next intCtr
   Do Until rsfTemp.EOF
      For intCtr = 0 To rsfTemp.Fields.Count - 1
         strList = strList & rsfTemp.Fields(intCtr) & ";"
      Next intCtr
      rsfTemp.MoveNext
   Loop
   rsfTemp.Close
   If Len(strList) < 2047 Then
									Me!lstOrders.RowSourceType = "Table/Query"
      Me!lstOrders.RowSource = strSQL
      Me!lstOrders.Requery
   Else
									Me!lstOrders.RowSourceType = "Value List"
      Me!lstOrders.RowSource = strList
   End If
									Me!lblList.Caption = "Orders from " & _
      Me!cboCountry.Value & " for " & _
      Me!cboProduct.Column(1)
   Me!lblLineItems.Caption = strMsg4
Else
									MsgBox "No orders from " & _
      Me!cboCountry.Value & " for " & _
      Me!cboProduct.Column(1)
   Me!lblLineItems.Caption = strMsg8
End If

5.
Save your changes and test frmCombo2 to verify that the column headers are added and that lstOrders displays the correct information. Listing 30.3 shows all the code of the lstOrders_GotFocus subprocedure.

6.
If you want to make your column headers more readable, change the values of strSQL1 and strSQL7 to

									Const strSQL1 = "SELECT CompanyName AS [Company Name], " & _
   "OrderID AS [Order #], ShippedDate AS [Date Shipped] " & _
   "FROM qryCombo1 WHERE Country = '"
Const strSQL7 = "SELECT CompanyName AS [Company Name], " & _
   OrderID AS [Order #], ShippedDate  AS [Date Shipped] " & _
   "FROM qryCombo1 "

As an aid to troubleshooting your modifications to lstOrders_GotFocus, Listing 30.3 includes all the event-handling code.

Code Listing 30.3. Code to Fill the lstOrders List Box from a Temporary Recordset
							
Private Sub lstOrders_GotFocus()
   Dim strList As String
							If Me!cboCountry.Value <> "" Then
							If Me!cboProduct.Value = 0 Then
         strSQL = strSQL7 & strSQL8 & Me!cboCountry.Value & _
            "'" & strSQL3
      ElseIf Me!cboCountry.Value = "(All)" Then
         strSQL = strSQL7 & strSQL9 & Me!cboProduct. Value & _
            strSQL3
      Else
         strSQL = strSQL1 & Me!cboCountry.Value & _
            strSQL2 & Me!cboProduct.Value & strSQL3
      End If
							Set rsfTemp = dbCurrent.OpenRecordset(strSQL,  _
         dbOpenSnapshot, dbOpenForwardOnly)
      If rsfTemp.RecordCount Then
         rsfTemp.MoveFirst
         For intCtr = 0 To rsfTemp.Fields.Count - 1
            strList = strList & rsfTemp.Fields(intCtr).Name & ";"
         Next intCtr
         Do Until rsfTemp.EOF
            For intCtr = 0 To rsfTemp.Fields.Count - 1
               strList = strList & rsfTemp.Fields(intCtr) & ";"
            Next intCtr
            rsfTemp.MoveNext
         Loop
         rsfTemp.Close
         If Len(strList) > 2047 Then
							Me!lstOrders.RowSourceType = "Table/Query"
            Me!lstOrders.RowSource = strSQL
            Me!lstOrders.Requery
         Else
							Me!lstOrders.RowSourceType = "Value List"
            Me!lstOrders.RowSource = strList
         End If
							Me!lblList.Caption = "Orders from " & _
            Me!cboCountry.Value & " for " & _
            Me!cboProduct.Column(1)
         Me!lblLineItems.Caption = strMsg4
      Else
							MsgBox "No orders from " & _
            Me!cboCountry.Value & " for " & _
            Me!cboProduct.Column(1)
         Me!lblLineItems.Caption = strMsg8
      End If
							End If
							End Sub

Formatting Value List Combo Box Columns

To eliminate all long-term read locks from execution of frmCombo2, you also must convert the lstLineItems list box to use a Recordset-based value list. When you create a value list from a Recordset with currency and percentage formatting, the format attributes are lost. You use the Type property of the Field object to detect fields of the Currency data type. There's no Percentage field data type, so you must explicitly identify field(s) that require percentage formatting.

Follow these steps to make the modifications to the lstLineItems list box:

1.
Add the following line at the beginning of the lstLineItems_DblClick event handler:

									Dim strList As String

2.
Delete these two lines:

									Me!lstLineItems.RowSource = strSQL
Me!lstLineItems.Requery

3.
Replace the deleted lines with the following code, which is quite similar to that added to the lstOrders_GotFocus event handler:

									Set rsfTemp = dbCurrent.OpenRecordset(strSQL, _
   dbOpenSnapshot, dbOpenForwardOnly)
If rsfTemp.RecordCount Then
   rsfTemp.MoveFirst
   For intCtr = 0 To rsfTemp. Fields.Count - 1
      strList = strList & rsfTemp.Fields(intCtr).Name & ";"
   Next intCtr
   Do Until rsfTemp.EOF
      For intCtr = 0 To rsfTemp.Fields.Count - 1
         strList = strList & rsfTemp.Fields(intCtr) & ";"
      Next intCtr
      rsfTemp.MoveNext
									Loop
   rsfTemp.Close
   If Len(strList) > 2047 Then
									Me!lstLineItems.RowSourceType = "Table/Query"
      Me!lstLineItems.RowSource = strSQL
      Me!lstLineItems.Requery
   Else
									Me!lstLineItems.RowSourceType = "Value List"
      Me!lstLineItems.RowSource = strList
   End If

4.
Indent the remaining existing code, and then add an End If statement before the End Sub line.

5.
Save your changes and open frmCombo2. Make a combo box selection and double-click an item in lstOrders to execute your added code. The items in lstLineItems have lost their formatting (see Figure 30.11).

Figure 30.11. Loss of formatting of items in the lstLineItems list box as a result of substituting a value list for a query.


6.
To format the fields in the lstLineItems list box, replace the code within the For intCtr = 0 To rsfTemp.Fields.Count - 1…NextintCtr loop with

									If IsNull(strItem) Then
   strItem = ""
Else
   strItem = rsfTemp.Fields(intCtr)
   If rsfTemp.Fields(intCtr).Type = dbCurrency Then
      strItem = Format$(strItem, "$#,###.00")
   ElseIf intCtr = 3 Then
      strItem = Format$(strItem, "00.0%")
   End If
									End If
strList = strList & strItem & ";"

7.
Add this line below the Dim strList As String line:

									Dim strItem As String

8.
To alias the column headings, change the value of strSQL4 to

									Const strSQL4 = "SELECT ProductName AS [Product Name], " & _
   "UnitPrice AS [Unit Price], Quantity, Discount, " & _
   "Extended FROM qryDrillDown WHERE OrderID = "

9.
Save your changes and open frmCombo2. Make a combo box selection and double-click an item in lstOrders to execute your new code. The items in lstLineItems have regained their formatting (see Figure 30.12).

Figure 30.12. Formatting applied with the Format$ function to the items in the lstLineItems list box.


If you experience a runtime error in step 8, Listing 30.4 shows all the code of the modified lstOrders_DblClick event handler to aid troubleshooting.

Code Listing 30.4. Code to Populate the lstLineItems List Box from a Temporary Recordset and Format the Elements of the Value List
							Private Sub lstOrders_DblClick(Cancel As Integer)
   Dim strList As String
							Dim strItem As String
							If Me!lstOrders.Value <> "" Then
      strSQL = strSQL4 & Me!lstOrders.Value & ";"
      Set rsfTemp = dbCurrent.OpenRecordset(strSQL, _
         dbOpenSnapshot, dbOpenForwardOnly)
      If rsfTemp.RecordCount Then
         rsfTemp.MoveFirst
         For intCtr = 0 To rsfTemp.Fields.Count - 1
            strList = strList & rsfTemp.Fields(intCtr).Name & ";"
         Next intCtr
         Do Until rsfTemp.EOF
            For intCtr = 0 To rsfTemp.Fields.Count - 1
               If IsNull(strItem) Then
                  strItem = ""
               Else
                  strItem = rsfTemp.Fields(intCtr)
                  If rsfTemp.Fields(intCtr).Type = dbCurrency Then
                     strItem = Format$(strItem, "$#,###.00")
                  ElseIf intCtr = 3 Then
                     strItem = Format$(strItem, "00.0%")
                  End If
							End If
               strList = strList & strItem & ";"
            Next intCtr
            rsfTemp.MoveNext
							Loop
         rsfTemp.Close
         If Len(strList) > 2047 Then
							Me!lstLineItems.RowSourceType = "Table/Query"
            Me!lstLineItems.RowSource = strSQL
            Me!lstLineItems.Requery
         Else
							Me!lstLineItems.RowSourceType = "Value List"
            Me!lstLineItems.RowSource = strList
         End If
							Me!lblLineItems.Caption = strMsg5 & Me!lstOrders.Value
         'Highlight the product row
         For intCtr = 0 To Me! lstLineItems.ListCount - 1
            If Me!lstLineItems.Column(0, intCtr) = _
                  Me!cboProduct.Column(1) Then
							Me!lstLineItems.Selected(intCtr) = True
							Exit For
							End If
							Next intCtr
      End If
							End If
							End Sub

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.23.130.191