We'll begin by creating the code that will help set up the functionality of our user interface.
1. In the General area of the frmID3 form, declare a form-level variable that we will use for our connection to the database. Once we set it in the Form_Load event, we can then use it throughout the program:
Dim adoConnection As ADODB.Connection
When the form is loaded, we want to first show the user what is going on. So sprinkled throughout the program we will update the sbStatus panel. Since some of the initialization code can take a few seconds, we force a show of the form, with Me.Show. To ensure that the Show method is forced, we issue a DoEvents. Of course, this will process all pending CPU tasks - of which Me.Show is one. So while our form is loading, the user has something to look at. Otherwise, we can get half-drawn forms on the screen, which does not look very professional.
If you recall, earlier in the book we discussed putting all visual components in the Form's Activate event. While this is generally true, in this program we are opening a database, which might take a few seconds. So we will make a design decision and violate this rule here. Why? Because we want the user to actually see something while we are initializing our program. In this case, it does make sense to force the form to be visible by using Me.Show. If the user has the database on a local area network somewhere, you might have to add another few seconds to open the table and display the list boxes. So by forcing the form to show itself here, we provide a quick, crisp interface to the user immediately upon loading our program.
2. Add the following code to the form's Form_Load event:
Private Sub Form_Load()
'-- Show what we are doing here --
sbStatus.Panels.Item(2).Text = "Loading..."
Me.Show
DoEvents
'-------------------------------------
'-- Open the database with ADO --
'-------------------------------------
sbStatus.Panels.Item(1).Text = "Opening the database..."
If (Not openTheDatabase()) Then
sbStatus.Panels.Item(1).Text = "Database failed..."
sbStatus.Panels.Item(2).Text = "Error."
Exit Sub
End If
Call setupID3
sbStatus.Panels.Item(1).Text = "Updating list boxes..."
Call updateListBoxes
sbStatus.Panels.Item(1).Text = ""
sbStatus.Panels.Item(2).Text = "Ready."
End Sub
We call our function openTheDatabase to establish the connection to the database. If we are successful, we call setupID3, which is responsible for updating the Region - Language - Country flex grid. Finally, we make a call to updateListBoxes that fill the two list boxes with categories and products. We will look at each of these subs in detail. But you can see that the Load event is clean and crisp. We just call a few subs and update the status bar.