VB/VBA Tutorial 10
 
Visual Basic Tutorial 10

In this our tenth VB tutorial, we shall take a look back and then go forward. The look back will underline the most important points to remember about the VB interface while doing another database component exercise. The reason we are "repeating" database and components is because this is what programming in the new millennia is really like. You, as programmer will be expected to quickly interconnect databases, VB and 3rd party components into a working package. In fact, this is so important, our next 3-4 tutorials will uncover how to do this with VBA-Visual Basic Applications available in every copy of Word 97 (or later), Word Perfect 2000 (and on), Visio 5 (or later) and dozens of other popular Windows programs. For better or for worse (with the good there are some distinct shortcomings) VBA is rapidly becoming the macro language for Windows.

But first, let us review the VB GUI interface and some guidelines for usage as shown in Figure 1. As you become more proficient in VB you will spend more time in the Source Code editor and the many designers (especially the database designer)than in visual forms editor. So often you close down the ToolBox or the Properties Sheet to make space and really motor in the Source Code Editor or whatever. On returning to visual-edit your form, you discover that the Property Sheet or Toolbox do not automatically pop up when the form is displayed. Figure 1 is a reminder of the toolbar


Figure 1

icons that you can click on to restore these important reference windows(these settings are also available in the View menu). Finally, we shall see identically the same icons when using VBA's macro editor in Word, Excel, PhotoPaint, etc.

Also Figure 1 helps to answer the question from tutorial 8 - how do you make a form non-resizable. Notice that on the Property Sheet the form's MinButton and MaxButton properties have been set to False. In addition, the BorderStyle property is set such that sizable is not enabled. With these settings your form cannot be resized.

Dynamic Database Components

A lot of VB projects involve displaying data either as reports or cross-tabulations - so users can get a feel for their data. The database grid and flex grid components included with VB 5 and 6 are adequate. However, I would encourage readers to download free demos from www.datadynamics.com of their ActiveReports and/or DynamiCube components. These components are very flexible and easy to program. DynamiCube will be used in the tutorial to show how easy it is to "supercharge" a component with some simple GUI elements. In this case two combo boxes which will allow us to control what fields get displayed in the DynamiCube.
Figure 2 shows the running DataCube application. DataCube is displaying marketing data from the CATS database (an Access table ; but it just as easily could be IBM DB2 or Oracle 8i database tables). The Unit Price field is about to be removed from the DynamiCube - the field name will be tranferred from the In Cube combobox to the Out of Cube combobox. As the Unit Price field is transferred between comboxes it is also removed from the DynamiCube. Users appreciate this capability because it allows them to simplify or drilldown into the data exactly the way they want.

The programming trick is to load up the combobox with the field names that are being used in the DynamiCube and then to know how to do the transfers.


Figure 2 - Dynamic DataCube application

Private Sub Form_activate()
Dim i As Integer
For i = 0 To Data1.Database.TableDefs("CATS").Fields.Count - 1
Combo1.AddItem Data1.Database.TableDefs("CATS").Fields(i).Name
Next
End Sub

First there is the Form_activate() code to load Combo1 (In Cube combo box) with the field names from the CATS database. If you plan to do any amount of VBA and VB programming you will have to get used to this VB collection classes naming conventions. It is all explained in the VB Help topics on collections and in particular the TableDef collection. Basically, the for loop is through the collection or list of fields used in the CATS table. The "Combo1.AddItem" adds each name into Combo1. The next piece of code transfers a field out of the DynaCube.

Private Sub Combo1_Click()
If Combo1.ListIndex >= 0 Then
Combo2.AddItem Combo1.Text
DCube1.Fields.Delete (Combo1.Text)
DCube1.RefreshData
Combo1.RemoveItem Combo1.ListIndex
End If
End Sub

First, the Click event on the combo box indicates a field name has been clicked or selected - this triggers the transfer. Notice the order of events. First, we add the field to the Out of Cube combo box (Combo2); then we delete it from the DynaCube (DCube1) and refresh/repaint the DynaCube; finally we remove the item from In Cube combo box (Combo1). The reverse code, Private Sub Combo2_Click(), follows the same pattern. For a little coding, one certainly gets very robust display of data.

As final convenience some simple resize code provides an alternate to the VS/OCX and other "smart" resizers we covered in our previous tutorial. When, as in this case, most of the dialog box is taken up with just the DynaCube (think also of database grids, large text/memo boxes or picture boxes) - then it is simple to code the Form_Resize() routine:

Private Sub Form_Resize()
Dim i As Integer
On Error Resume Next
DCube1.Move 0, 400, ScaleWidth, ScaleHeight - 400
End Sub

All we have to do is move the DynaCube to the same start position while resizing it to the new form dimensions, ScaleWidth and ScaleHeight. Note if those dimensions would cause an error then the resize On Error just leaves the old settings intact. Note also we leave a strip along the top 400 twips in size for the combo boxes (or any other controls). Visual Basic uses a device-independent unit of measurement, a twip, for calculating size and position. Two properties of the Screen object, TwipsPerPixelX and TwipsPerPixelY, can be used to determine the exact size of a display(converting twips to pixels or inches) at run time.

The Coriolis VB6 Black Book discusses combo boxes on page 252-272 and resizing plus twips on page 608-609 while Beginning Visual Basic 6 covers these topics on pages 206-227 and 636-637.
 
Top of Page  Tutorials Home