This grid is really a reference for the product manager. You can see how our program is grouping countries. So this can easily be scrolled through for quick reference by the user.
1. Please add another sub to the frmID3 form named gridID3. We will encapsulate updating the geographic groupings in this single sub routine. We noticed above that this is called as soon as the setupID3 routine finishes grouping the countries by region and language. We now display the results here.
Public Sub gridID3()
Dim adoID3 As ADODB.Recordset
Dim sSql As String
Dim iRows As Integer
Dim iCols As Integer
Dim iRowLoop As Integer
Dim iColLoop As Integer
sSql = "SELECT CountryRegion, CountryLanguage, Country"
sSql = sSql & " FROM ID3 GROUP BY "
sSql = sSql & "CountryRegion, CountryLanguage, Country"
Set adoID3 = New ADODB.Recordset
adoID3.CursorLocation = adUseClient
adoID3.Open sSql, adoConnection, , , adCmdText
adoID3.MoveFirst
iRows = adoID3.RecordCount
iCols = adoID3.Fields.Count
miningGrid.Rows = iRows
miningGrid.Cols = iCols
'----------------------------
'-- Set up the grid here --
'----------------------------
miningGrid.Row = 0
miningGrid.ColAlignment(0) = 7
For iColLoop = 0 To miningGrid.Cols - 1
miningGrid.Col = iColLoop
miningGrid.MergeCol(iColLoop) = True
miningGrid.ColWidth(iColLoop) = 1500 'Set column's width
Next
miningGrid.MergeCells = flexMergeRestrictColumns
For iRowLoop = 0 To iRows - 1
For iColLoop = 0 To iCols - 1
miningGrid.Row = iRowLoop
miningGrid.Col = iColLoop
miningGrid.Text = adoID3.Fields(iColLoop)
Next
adoID3.MoveNext
Next
adoID3.Close
Set adoID3 = Nothing
End Sub
We wish to retrieve the geographic information that was just placed in our ID3 table. However, we want to retrieve the ADO recordset in a specific order. So we select the three fields we want and then group them together. This will ensure they show up correctly on our flex grid when cells are merged together.
sSql = "SELECT CountryRegion, CountryLanguage, Country"
sSql = sSql & " FROM ID3 GROUP BY "
sSql = sSql & "CountryRegion, CountryLanguage, Country"
Again, rather than hard coding anything we can dynamically size our grid. We use the current recordset's RecordCount property to tell us how many rows and the .Fields.Count property to determine how many columns. This is a good technique for subs like this. We can create generalized routines that dynamically resize themselves. You could easily make this a routine where you pass in a recordset and the code automatically formats everything.
iRows = adoID3.RecordCount
iCols = adoID3.Fields.Count
Next we want to loop though the fields in our miningGrid grid to initialize them for size and merging. This next section simply loops through the columns in our recordset and updates the top row (row 0) of our grid. This places the heading on our grid. Each time through the loop – once for each field in the recordset – we set the Col property to the current column we want to format. We then tell it to merge and set its width. When we are finished, we set the MergeCells property to permit columns to merge if they have the same value. This is one of the powerful features of the grid – it will determine when and how to merge for us. That is why we retrieved the recordset using the GROUP BY clause in the SQL statement. We want like regions to be grouped together, and within those, the language. This way, countries in a region that have the same language will be grouped together. Of course, each country has a unique name, so there will be no grouping here. But we tried to look at the data to see which values could have identical values and placed them together.
miningGrid.Row = 0
miningGrid.ColAlignment(0) = 7
For iColLoop = 0 To miningGrid.Cols - 1
miningGrid.Col = iColLoop
miningGrid.MergeCol(iColLoop) = True
miningGrid.ColWidth(iColLoop) = 1500 'Set column's width
Next
miningGrid.MergeCells = flexMergeRestrictColumns
Next, we want to add some data to our grid. To lay out the grid so it looks nice and neat, we just set the Text of the current field to be the contents of the ordinal position of the field in the recordset. The first time through the loop, the iRowLoop is set to 0. Next the program control moves to the inner, iColLoop and loops from 0 to how many columns there are in the recordset - 1. Since we are starting at 0, we want to go one less. If there are, say, five fields, and we loop from 0 to 4 that gives us the five fields. So the first time through the iColLoop, the value is 0. We tell the grid which cell we are interested in by setting the Row and Col properties. Next we take the field in ordinal position 0 from adoID3 and place it in the grid. We then loop through the rest of the fields in that record. When finished, we move on to the next record and repeat the process.
For iRowLoop = 0 To iRows - 1
For iColLoop = 0 To iCols - 1
miningGrid.Row = iRowLoop
miningGrid.Col = iColLoop
miningGrid.Text = adoID3.Fields(iColLoop)
Next
adoID3.MoveNext
Next
When complete, we close the recordset. Remember that this sub was called from setupID3. In turn, that was called from the Form_Load event procedure. There is one more initialization routine that we call from the Load event. This one, updateListBoxes, updates the list boxes with Categories and Products within the categories. Now you can see why we forced a refresh at the beginning of the Load event.
1. Please add a new sub to the frmID3 from called updateListBoxes. Here we are just updating the two list boxes to display the legitimate categories and products. This is helpful because we only show legitimate choices. Again, keeping the man/machine interface design in mind, we display a lot of information to the user. However, there is no data-entry required! The user simply clicks choices from the list boxes. Cool!
Public Sub updateListBoxes()
Dim adoTempRecordset As ADODB.Recordset
Dim sSql As String
'-- Let the user know what we are doing here ---
sbStatus.Panels.Item(1).Text = "Updating list boxes."
'--------------------------------------
'-- Set up the Categories list box --
'--------------------------------------
Set adoTempRecordset = New ADODB.Recordset
adoTempRecordset.CursorLocation = adUseClient
adoTempRecordset.Open _
"SELECT * FROM Categories ORDER BY CategoryName", _
adoConnection
ProgressBar1.Max = adoTempRecordset.RecordCount
lstCategory.Clear
With adoTempRecordset
If .RecordCount > 0 Then .MoveFirst
While Not .EOF
ProgressBar1.Value = .AbsolutePosition
lstCategory.AddItem !CategoryName
lstCategory.ItemData(lstCategory.NewIndex) = !CategoryID
.MoveNext
Wend
End With
lstCategory.ListIndex = 0
ProgressBar1.Value = 0
adoTempRecordset.Close
'--------------------------
'—Set up the Products –
'--------------------------
sSql = "SELECT ProductName, ProductID FROM Products"
sSql = sSql & " WHERE CategoryID = " & _ lstCategory.ItemData(lstCategory.ListIndex)
adoTempRecordset.Open sSql, adoConnection
ProgressBar1.Max = adoTempRecordset.RecordCount
lstProduct.Clear
With adoTempRecordset
If .RecordCount > 0 Then .MoveFirst
While Not .EOF
ProgressBar1.Value = .AbsolutePosition
lstProduct.AddItem !ProductName
lstProduct.ItemData(lstProduct.NewIndex) = !ProductID
.MoveNext
Wend
End With
lstProduct.ListIndex = 0
ProgressBar1.Value = 0
sbStatus.Panels.Item(1).Text = ""
End Sub2. Press F5 and run the program. All we have done so far is to add the region, language and country to our grid, and updated our list boxes. At this point, though, your project should look like this:
We want to populate the Categories list box first. We know there is a one-to-many relationship between category and product. One category has many products associated with it. We want to have the recordset return the categories in alphabetical order, so the ORDER BY does that job for us. Again, please keep little things like this in mind. In the absence of any other logical grouping for returned records, it's better to group them in alpha order. It displays order and security to the user, rather than having the categories returned in a hap hazard way.
adoTempRecordset.Open _
"SELECT * FROM Categories ORDER BY CategoryName", _
adoConnection
We are then planning on looping through the adoTempRecordset and stuffing the category name into the lstCategory list box. But first, notice that we are setting the Max property of the progress bar to the RecordCount property of the recordset. This will permit us to graphically show the user the progress. By now you are noticing that showing the user progress is our standard Modus Operandi – our MO. This is a very good habit to get into. The graphical tools really lend themselves to this, so we should use them wherever possible.
We then clear out any previous contents of the lstCategory list box by calling its Clear method. We when loop through the recordset. As a sanity check, we ensure that the current record pointer is pointing to the first record.
We when loop through and update the progress bar. If there were eight records in the recordset, then the Max property would have been set to 8 and when we set the value, the progress bar would now be 1/8th filled in. Next, we add the CategoryName to our list box. Finally, we use the ItemData property of the list box. This permits us to add a hidden value along with the visible value the user sees in the list box. Then, we move to the next record in the adoTempRecordset and do the same thing until we reach the end.
ProgressBar1.Max = adoTempRecordset.RecordCount
lstCategory.Clear
With adoTempRecordset
If .RecordCount > 0 Then .MoveFirst
While Not .EOF
ProgressBar1.Value = .AbsolutePosition
lstCategory.AddItem !CategoryName
lstCategory.ItemData(lstCategory.NewIndex) = !CategoryID
.MoveNext
Wend
End With
lstCategory.ListIndex = 0
ProgressBar1.Value = 0
adoTempRecordset.Close
Let's spend a minute discussing the ItemData property of the list box. The ItemData property is an array of long integer values. It has the same number of items in it as the control's List property. So we can use the list box's index number to identify the items. This is hidden from the user. For the visible entry we place in the list box using the AddItem method, we can place a hidden value the ItemData property. So when the user clicks on an entry in the list box, say the 5th element, we can retrieve the value in the ItemData property. In our case, when the user clicks on a category, we are going to retrieve the hidden CategoryID value so we can refresh the Products list box with all of the products for this category.
Once the list box is populated, the ListIndex is set to 0. This highlights the first item in the list box (remember that collections start at 0?). We also zero out the progress bar.
I use the ItemData property when displaying items such as employee names. Each name is added to the list box with the AddItem method as expected. But I then add the employee ID to the ItemData property of that list box. Since an employee database would be keyed on an employee number (which might be a social security number), that is used as the unique primary key. For example, we might have five John Smiths in the database and hence the list box, but in the ItemData property is the unique identifier for each of the Smiths. So when the user clicks on a name, the unique identifier for the name in the ItemData property is retrieved. This is what is used to hit the database and retrieve the employee record. The employee number means nothing to the user, but the name does. However, we can't retrieve a guaranteed unique record only by the name, so the unique employee ID, hidden from the user, does the trick.
We then go on to update the Products list box in exactly the same way. In the ItemData property, we stuff the ProductID value for each product. We will be using these values in a bit to embed in SQL queries to retrieve records from the database.
We are not quite finished with the list boxes. When a user clicks on a selection in the Categories list box, we refresh the Products list box with all products for only that category. Let's look at how this is done.
1. In the Click event of the lstCategory list box, add the following code. When the user clicks on the Categories list box, we simply build an SQL query that will retrieve all of the products where the CategoryID is equal to the CategoryID for the currently selected category. In other words, when the user clicks on an item in the Categories list box, we grab the CategoryID that we placed in the ItemData property and round up all of the products that have this same ID. These are then displayed in the Products list box.
Private Sub lstCategory_Click()
Dim adoTempProducts As ADODB.Recordset
Dim adoTempPicture As ADODB.Recordset
Dim sSql As String
sSql = "SELECT ProductName, ProductID FROM Products"
sSql = sSql & " WHERE CategoryID = "
sSql = sSql & lstCategory.ItemData(lstCategory.ListIndex)
Set adoTempProducts = New ADODB.Recordset
adoTempProducts.CursorLocation = adUseClient
adoTempProducts.Open sSql, adoConnection
If adoTempProducts.RecordCount > 0 Then _
adoTempProducts.MoveLast
ProgressBar1.Max = adoTempProducts.RecordCount
adoTempProducts.MoveFirst
lstProduct.Clear
With adoTempProducts
If .RecordCount > 0 Then .MoveFirst
While Not .EOF
ProgressBar1.Value = .AbsolutePosition
lstProduct.AddItem !ProductName
lstProduct.ItemData(lstProduct.NewIndex) = !ProductID
.MoveNext
Wend
End With
lstProduct.ListIndex = 0
ProgressBar1.Value = 0
adoTempProducts.Close
sbStatus.Panels.Item(1).Text = ""
Call showAnalysis
End Sub
The following SQL statement is the brains of the operation. When the user clicks on a selection in the Categories list box, we dynamically generate a SQL statement. We want to retrieve the products associated with the selected category; so we can grab the ProductName and the ProductID from the Products table where the CategoryID is the value we stuffed in the ItemData property of the list box. This way, we only retrieve products that have the required CategoryID.
sSql = "SELECT ProductName, ProductID FROM Products"
sSql = sSql & " WHERE CategoryID = "
sSql = sSql & lstCategory.ItemData(lstCategory.ListIndex)We retrieve that recordset based on the above SQL, clear the list box of old entries, and blast in the new products and IDs. Since the code is the same as above, we don't need to go over it here. But the great thing is that when the user clicks on a category, only relevant products will be displayed.
If you had your thinking hats on, you might have remembered that just a while ago in the updateListBoxes routine, we set the ListIndex property to 0. This has the effect of clicking and highlighting the first item – item 0. When this happens as the program is initialized, the Click code is executed and only the products for the first category will be displayed. So by setting the ListIndex property using code, we simply simulate someone clicking the first item.
Call showAnalysis
You might have noticed the call to showAnalysis at the end of the routine. Quite simply, this places some English text in the txtAnalysis box so the user can see the criteria that will be used when the Analyze button is clicked. This is a simple visual conformation of what items will be analyzed if the Analyze button gets clicked.
1. Please add a new sub to the frmID3 form. Name the sub showAnalysis and enter the following code.
Public Sub showAnalysis()
Dim sAnalysis As String
sAnalysis = "Search / Analysis Criteria: " & vbCrLf
sAnalysis = sAnalysis & " Category: " & lstCategory & vbCrLf
sAnalysis = sAnalysis & " Product: " & lstProduct & vbCrLf
txtAnalysis = sAnalysis
End Sub
2. Now place this single line of code in the lstProduct_Click event. This will just update the txtAnalysis text box.
Private Sub lstProduct_Click()
Call showAnalysis
End Sub
3. Press F5 to test our list boxes and the showAnalysis sub. The Categories and Products list boxes should be populated. The first item in each should be highlighted because we set the ListIndex property to 0 for each of them.
Since we set the Products list box's ListIndex to 0 when the program is initialized, a call to showAnalysis is made – just as if the user clicked an item. Our routine just takes the current item in the Categories and Products list boxes and displays them in the txtAnalysis text box. Since we set the MultiLine property to True, the built-in VB constant vbCrLf (for carriage return/line feed) places each string of text on its own line in the text box. We will be using this text box to show the results, but it was too much screen real estate to waste while the user made a selection. So here we just provide a bit of reinforcement.
Now we have the infrastructure of our program down. The user interface looks good. So now let's write the code that actually does the grunt work and performs the analysis.