Grid control as a report tool

In this example, data is populated into an unbound grid control. The data selection is based on the user-entered contents of text boxes in the form’s predefined Criteria section. Three business object data retrieval methods are included to illustrate the differences in syntax and performance of each method.

Figure 3-9 Standard report using a grid

The hardest part of this example is formatting the form, frmReportInGrid, with controls to hold the selection criteria, the method of data retrieval, and the report result (grid). Once you’ve set these controls up, simply add the lines of code documented in this section for a fully functional Company Details viewer.

In the module modReportInGrid, the routine Sub Main() is linked as the startup object in the ReportInGrid Project Properties dialog box. This routine contains a single line of code to display the form.

In the form frmReportInGrid Declarations section, we add one line of code to create a new instance of the CompanyDetails business object:

Private csCompDet As New CompanyDetails

The New statement ensures that the object is created automatically at run time.

In the Form_Load event, code is entered to inform the business object of the default method of data retrieval. In the Form_Unload event, basic housekeeping is performed to ensure that no trailing reference to the business object is left.

Private Sub Form_Load()
    ' Default to the data pass method.
    optCommMethod(csCompDet.DataPassFormat).Value = True
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set csCompDet = Nothing
End Sub

Note Setting csCompDet to Nothing doesn’t necessarily mean that the object is destroyed. It ensures only that the current reference to the object is removed. Only when the last reference to the object is removed will the object destroy itself.

Now place the following code in the option box event optCommMethod_Click:

Private Sub optCommMethod_Click(Index As Integer)
    csCompDet.DataPassFormat = Index
End Sub

In the cmdProcess_Click event, I link the Index value of the option box controls to the methods of passing data: csPassByProperties, csPassByVariant, and csPassByFile.

Apart from closing the form and initializing the grid with the correct number of rows and columns, we only have to add the code to retrieve the company details and to populate the return result into the grid:

csCompDet.BuildCompanyList Limit:=txtNoOfItems, _
    CompanyID:=txtCriteria(0), _
    CompanyDescription:=txtCriteria(1), _
    CompanyInceptionDate:=txtCriteria(2), _
    CompanyCategory:=txtCriteria(3)

In the code above, the BuildCompanyList method is invoked, which in turn builds up the appropriate SQL from the selection criteria parameters passed. This method also executes the SQL and creates the result in memory, with the business object ready for retrieval by the calling application.

Pass by parameter, data retrieval The first method of data retrieval (csPassByProperties) uses the public methods and properties of the CompanyDetails object (csCompDet):

For nPos = 1 To csCompDet.Companies.Count
    griDisplay.AddItem csCompDet.Companies.Item(nPos).ID 
Next nPos

This format is easy to read and understand. Unfortunately, it is exceptionally slow compared with the other methods, making it impractical to use with high-volume data. The poor performance is a result of the overhead associated with cross-process-boundary communication; the Visual Basic application and the CompanyDetails object reside in different processes. Each time the calling application makes a reference to a property or method, the program must communicate (cross boundaries), which requires setting up stack space, pointer references, and communication handshaking. In the preceding code, this would occur for each pass through the loop.

Pass by variant, data retrieval This method (csPassByVariant) addresses the performance issues in the previous method, and it returns the data as a variant:

vList = csCompDet.GetCompanyList
For nPos = 1 To csCompDet.Companies.Count
    griDisplay.AddItem vList(nPos)
Next nPos

This method cuts down cross-process-boundary communication significantly. With the GetCompanyList method, cross-process-boundary communication is required only once. It’s up to you, however, to place the data into the variant and to strip it out at the other end. I’ve intentionally stored the data in the variant as a single-dimension array with each value containing multiple values delimited by tabs. This type of storage allows us to pass the relevant array value to the grid, which in turn automatically splits the data according to the tabs.

Pass by file, data retrieval Oddly enough the fastest method (csPassByFile) of the lot, retrieving data from a disk drive, is quicker in some cases than retrieving the data from memory structures. Again, cross-process communication is a factor; here the physical data is transferred to and from the disk from separate processes rather than across a process boundary:

nFile = FreeFile
Open csCompDet.GetCompanyList For Input As nFile
Do Until EOF(nFile)
    Input #nFile, sDetail
    griDisplay.AddItem sDetail
Loop
Close nFile

This method poses a security risk, however, by creating a public file on the network. It also requires you to trap all file error situations to ensure that the method is stable. For these reasons, I don’t recommend this method unless you need to retrieve high volumes of data and it’s not practical to hold the information in memory.