Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

As SetParameters creates the new text controls, it sets their Text properties to the required default values. After it has created all of the controls, it arranges them on the form.

FIGURE 12.8  ParameterForm in Visual Basic design mode.

FIGURE 12.9  ParameterForm ready for user input.

Public Sub SetParameters(params As String)
Const gap = 60
Dim not_done As Boolean
Dim prompt As String
Dim default_value As String
Dim i As Integer
Dim y As Single
Dim dy As Single
Dim label_right As Single

    i = 0
    label_right = 0
    not_done = GetToken(params, “:”, prompt)
    Do While not_done
	not_done = GetToken(“”, “;”, default_value)
	
	i = i + 1
	Load PLabel(i)
	PLabel(i).Caption = prompt
	If label_right < PLabel(i).Width Then _
	   label_right = PLabel(i).Width
	Load PText(i)
	PText(i).Text = default_value
  
	not_done = GetToken(“”, “:”, prompt)
    Loop
    NumFields = i
  
    TextLeft = label_right + 2 * gap
    y = PLabel(0).Top
    dy = PText(0).Height + gap
    For i = 1 To NumFields
	PLabel(i).Move _
	    label_right - PLabel(i).Width, y
	PText(i).Move TextLeft, y
	PLabel(i).Visible = True
	PText(i).Visible = True
	y = y + dy
    Next i
End Sub

The user should enter values in ParameterForm and then press the Ok button. The ReportListForm can then use ParameterForm’s GetParameters function to obtain a semi-colon-delimited list of the values entered by the user. It passes this list directly to the ReportList object to generate the final report.

Public Function GetParameters() As String
Dim txt As String
Dim i As Integer
    txt = PText(1).Text
    For i = 2 To NumFields
	txt = txt & “;” & PText(i).Text
    Next i
    GetParameters = txt
End Function

This version of ParameterForm is quite simple. A more robust application would provide field and form validation features. This can be particularly important in database applications because the errors returned by database calls tend to be nondescriptive. For example, if the user enters “X” in the Minimum Cost field on the ParameterForm shown in Figure 12.9, the report server returns the following error message:

Error 3061 processing select statement.

SELECT * FROM Purchases WHERE Cost>=X ORDER BY PurchaseDate

Too few parameters. Expected 1.

The message “Too few parameters. Expected 1.” is not very helpful to the user. Only a user experienced in SQL and familiar with the database design will be able to read the SQL statement and discover the problem.

ReportForm

ReportForm is extremely simple. All it does is display a report in a text box that has scroll bars.

One of the few nontrivial things ReportForm does is it allows the user to save the report into a file. It uses a common dialog control to allow the user to specify the file. The file dialog’s initial directory is set to the value specified in the ReportDir variable. This variable is declared globally in module SetPos.BAS so all ReportForms have access to the same value.

After it has saved the report, ReportForm stores the file dialog’s current directory in ReportDir. If the user saves another report using this or a different ReportForm, the file dialog will initially begin in this same directory. This is convenient for a user who keeps many reports in the same directory.

One simple enhancement that could be made to this program would be to store ReportDir in the system registry. Then it could be reloaded when the program starts. This would make the file dialog begin in the same directory each time the user ran the program.

The only other nontrivial task ReportForm performs is to manage the main MDI form’s Save Report As menu item. This item should be enabled only when a ReportForm has the input focus. It would not make sense for the user to select this command when no reports were visible.

To make this command available when a ReportForm is active, the form’s Activate event handler enables the command. To make the command unavailable when no ReportForm is active, the form’s Deactivate and Unload event handlers disable the command.

For example, suppose ReportForm1 is active and the user clicks on ReportForm2 to bring it to the top of the other MDI child windows. First ReportForm1 receives a Deactivate event so it disables the Save Report As command. Next, ReportForm2 receives an Activate event so it reenables the command. This happens quickly so the user is never aware that the command was disabled and reenabled.

Private Sub Form_Activate()
    QueryServerForm.mnuFileSaveReportAs.Enabled = True
End Sub

Private Sub Form_Deactivate()
    QueryServerForm.mnuFileSaveReportAs.Enabled = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
    QueryServerForm.mnuFileSaveReportAs.Enabled = False
End Sub

Building Librarian

The Librarian’s job is to return a list of the reports that are available and the servers that support them. The Librarian project contains a single class, ReportLister, to perform this service. ReportLister performs its duty by querying the Reports table in Reports.MDB. This table contains two data fields. The Descr field contains a description of an available report. The Server field contains the name of the class that serves the report.

ReportLister’s GetReportNames function selects all of the records in the Reports table and builds a semi-colon-delimited string listing the report descriptions and server names. This string is returned to the ReportList client program. ReportList separates the descriptions and server names to present the user with a list of the available reports.

Public Function GetReportNames(txt As String) As Integer
Dim query As String
Dim rs As Recordset

    On Error GoTo GetReportNamesError
  
    query = “SELECT Descr, Server FROM Reports ORDER BY Descr”
    Set rs = TheDB.OpenRecordset(query, dbOpenSnapshot)
  
    ‘ Load the names.
   txt = “”
    Do Until rs.EOF
	txt = txt & rs!descr & “;” & rs!Server & “;”
	rs.MoveNext
    Loop
    GetReportNames = rs.RecordCount
    Exit Function
GetReportNamesError:
    GetReportNames = 0
End Function

ReportLister’s Instancing property is set to 2 - Creatable MultiUse. When a new client requests a ReportLister object, it will be provided by an existing Librarian program if one is already running. In this program, the database connection is stored in global variable TheDB, declared in the main program’s module LibrMain.BAS. If more than one ReportLister object is running within the same program, they can both access this database object. The program’s Main subroutine opens the database when the server program starts running.

Sub Main()
Const REPORT_LIST_DB = “D:\Ch12\Libr\Reports.MDB”
  
    Set TheDB = DBEngine.Workspaces(0).OpenDatabase(REPORT_LIST_DB)
End Sub

In this application, Librarian is implemented as an OLE server. The list of reports could be built by simply querying a local database. A remote table could even be attached to the local database so the actual data could still be stored centrally. This would make generating the report list a little faster.

ReportList is simple enough, however, that it gives adequate performance implemented as an OLE server. It also provides a bit more flexibility than a direct query would.

For example, one of the next logical enhancements to the Query Server system would be user access control. With user groups and group privileges similar to those used by the PeopleWatcher application described in Chapter 8, the program could decide which users should be given access to particular reports.