|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
|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
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
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()
Private Sub Form_Resize()
Dim i As Integer
On Error Resume Next
DCube1.Move 0, 400, ScaleWidth, ScaleHeight - 400
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.