by Rupert Walsh - GUI Computing
It's been said that VB is just the glue that holds a bunch of components (custom controls) together. However, since version 5, you've been able to write your own controls with VB. Does this mean that you're simply gluing the glue together <g>?
Creating your own custom controls can save you (and possibly your colleagues) a lot of programming time in the future, provided you follow these simple guidelines:
They're the same criteria you'd use if you were buying a control from a third party. In this article, I'm going to go through what we discovered whilst building a custom control, concentrating on the third point above - and how using property pages and a wizard has helped us create a truly useful tool. I'm not going to go into full detail about the inner workings of the control itself - just trust me, that would be way too much to describe in a single article!
It should also be noted that our control is not made entirely from "glue" - it actually uses Apex's True DBGrid and DataDynamics' ActiveReports internally. This may seem like a lot of overhead, but we (like a lot of you) use these excellent controls in almost all of our new developments anyway.
Not another Data Form!
We have developed a custom control that we use to maintain reference data. By reference data, I mean the fundamental data used in any system that is normally (no pun intended) stored in a single table. As an example, you could use it to maintain the Authors or Publishers in the PUBS database that comes with VB. It's a bit like the VB Data Form addin, except that we actually find it useful!
The control started life not as a control at all, but rather as an ActiveX DLL engine for reference data maintenance. The initial interface to this engine was simply a VB form that we copied and pasted each time we used it, tuning the form for each project. This meant an extra version for MDI projects, and generally stuffing around with the form each time to tune it for each project.
Using a custom control, and including the engine code within made a lot more sense, and a lot less stuffing around for the developer.
Here's the control on a form, being used to maintain data common to many systems - a table of users:
As you can see, the control is made up of two sections - a list of records at the top, and the details of the currently selected record below. The control also uses Active Reports to create basic reports on reference data. And uses property pages and a wizard to make it easier to use. It really fills up the project window pretty nicely.
In order to make the control flexible enough to be of use, we needed to have settings to describe how to display data from each table, and each field within the table. For example the caption to display for the table, which fields to display in the list, the width to use for each field, and whether the field is updateable by the user. The control also supports some simple validation rules for each field.
This is implemented within the control's code as a class hierarchy. There is a table class that has a collection of fields which in turn has a collection of validation rules:
This is going to make things easier, isn't it?
Although it is possible, it is unwieldy to set up this class structure in code each time we used the control. Here's a simplified example of what might be required to set up the control to maintain one table with just two fields, one of which has two validation rules:
Sub ManualClassLoad() Dim oTable As RefTable Dim oField As RefField Dim clFields As RefFields Dim oValidation As RefValidation Dim colValidations As RefValidations 'Add the table Set oTable = New RefTable oTable.Name = "tblUsers" oTable.Caption = "Maintain Users" ' 'Add Username Field with no validations ' Set oField = oTable.RefFields.Add("Username", 1, "User Name", "", dvNone, dtInteger , <many more parameters>, Nothing) ' 'Add IQ field ' 'Add Validation rules: Minimum 0, maximum 200 Set oValidation = colValidations.Add(vtMin, 0, vtString) Set oValidation = colValidations.Add(vtMin, 0, vtString) 'Add the field Set oField = oTable.RefFields.Add("IQ", 2, "Intelligence Quotient", "", dvNone, dtInteger , <many more parameters>, colValidations) End Sub
Phew! As most reference tables would have at least three or four fields, and a system normally has many more than a single reference table, this soon becomes unworkable. Instead, we store this data in the database with a table for each of the three objects, called sysrefTable, sysrefField and sysrefValidation. Naturally each of these tables contains fields for each corresponding object property, while sysrefField contains a foreign key to the Table Name, and sysrefValidation foreign keys to both the table name and field name to which it applies. I then wrote a function to loop through these database records to fill the class structure dynamically. The user simply has to add records to the database rather than learn the code above.
So easy to use, even the guy who wrote it can!
When we first built the control, we were setting up each reference table by manually entering the values we wanted into the database. This was not too bad if you knew the control inside out. We had enumerated many of the properties in VB (the data-types for example), so that they pop-up in code, and that's not much help when you're adding data to a table in Access or SQL Server.
The answer was to create a wizard to enter or edit this information, and because we were building a custom control, we decided to launch the wizard from a property page for the control.
Making it easier, part 1: The Property Page
I added the property page using the VB Property Page wizard. This wizard works reasonably well for adding simple property pages, but I wanted more functionality.
My control has a DisplayMode property, which is an integer, but can be one of three valid values:
Of course, in my code I use enumerated constants in place of these values.
You would never make an end-user use a text box to enter these values, so you should not make your developers use the unfriendly text box that the Property Page Wizard adds either. I removed it and instead added a combo box to my property page a named it cboDisplayMode.
I added the following code to Sub PropertyPage_Initialize() to fill the combo box:
cboDisplayMode.AddItem "List and Detail" cboDisplayMode.AddItem "Detail Only" cboDisplayMode.AddItem "List Only"
Note that I added the items so that the corresponding listindex property of the combo would reflect the actual values of my DisplayMode property.
I also needed to replace code in Sub PropertyPage_SelectionChanged() to use my combo instead of the text box:
cboDisplayMode.ListIndex = SelectedControls(0).DisplayMode
This routine is used to update the property pages so that the current values for the properties are used, and is called whenever the property page is launched, or when the property is edited in the VB Properties window.
Similarly, Sub PropertyPage_ApplyChanges() is used to update properties when the user clicks on Apply or OK on the property page:
SelectedControls(0).DisplayMode = cboDisplayMode.ListIndex
Property pages also have a built-in property called Changed that should be updated whenever the user edits a property. This needs to be set to true in the Click event of the combo box:
Private Sub cboDisplayMode_Click() Changed = True End Sub
I also added two buttons - one for testing the Data Source the user has typed in, and another for launching the 'Data Wizard' used to set up the reference tables. Unlike the DisplayMode combo, neither of these buttons affects the values of my controls properties, no extra support code is necessary.
The 'Test' button simply tries to open a new connection using the ConnectionString and displays in a messagebox the error message if there is one, or OK if not. The 'Data Wizard' button simply displays the first page of my wizard.
Making it easier, part 2: The Reference Data Wizard
This wizard is used to edit the records in the sysrefTable, sysrefField and sysrefValidation tables that are eventually loaded into the class structure. One of the features of all wizards is the ability to move back and forwards though the wizard, making changes where you like, while still having the option of cancelling at any time. In order to achieve this, I follow the following principles:
If the user cancels, the memory is simply freed without saving back to the database.
For the reference data wizard, my permanent storage consists of the 3 "sysref" tables. For the temporary memory, I re-utilized the class structure that I had already defined to use for the actual control. Neat - and I had already written a function that would load the database records into this structure for use at run time. I only needed to write a function to do the opposite - save from the class structure back to the database.
The first page of the wizard lets the user select which table they wish to maintain. It also has a button 'Create Reference Tables' which when clicked, runs three "Create Table" SQL statements to the database to create my sysrefTable, sysrefField and sysrefValidation tables, if they do not already exist.
The next page allows the user to set the various options for a table, including permissions and an SQL order by clause used to change the order presented to the user in the list at the top of the control. It also has allows the developer to control automatic auditing of the table, and will add the special fields our auditing scheme requires if necessary.
The user is next asked to select which fields they want the control to maintain. Note that the special audit fields are visible here, but not selected (UserStamp, DateTimeStamp and Deleted):
The wizard next displays a page for each field selected on the previous screen, allowing them to enter any of the available options for a field:
Field validations are also entered on this page. Here's an example of validating a number to be between 0 and 999999:
Of course, there's also a final page to let the wizard user know they've reached the finish! The project explorer actually looks pretty impressive after all this, having used nearly every possible type of module, but of course users of the compiled control will only ever see the little icon in the toolbox:
Adding this wizard to the custom control has made it much more accessible to developers. Don't forget that they will appreciate a good UI just as much as a typical end user.