Visual Basic Database Tutorial - Part 6, Page 5
Some really tricky users don't just want to throw information into a system, they also want a little output in the form of reports. Sure, we know they're just being picky... but you'd better oblige in the faint hope of a pay rise sometime within the next millennia.
There are a few ways you can create reports via Visual Basic. The most popular are:
- Transfer query results direct to Excel for processing
- Produce reports in Access and print out occasionally
- Use Crystal Reports (usually the cut-down version on VB CD)
- Utilise the built-in Data Report widget
In this section, we're going to check out the latter Data Report object. This allows you to create a report in Visual Basic very similar to those found in Microsoft Access and it can be viewed, printed or exported direct through your application.
So let's have a stab at creating our first Data Report in the 'Groovy Pet Adder' program we created earlier:
- Click Project, More ActiveX Designers, Data Environment
Every Data Report requires a source of information. In Visual Basic, this comes from a Data Environment.
Now stick a big hat on my head, call me Professor and dance around ten times chanting "He's Einstein, Shakespeare and Angela Lansbury rolled into one"... but I personally understand a Data Environment to simply be an environment. For data.
Anyway, let us continue fiddling with it:
- Click on the node titled 'DataEnvironment1'
- Change its Name property to 'Vets'
- Change the Name property of Connection1 to Surgery
- Now click on the 'Add Command' button at the top of your screen
- Change the Name property of Command1 to Owners
The Finished Product
All this 'naming' ensures we keep track of the information these objects will hold.
- Right-click Surgery and select Properties
- Choose Jet 3.51 if your Surgery database was created in Access 97, or Jet 4.0 if you used Access 2000
- Hit Next
- Type in the database path and click OK
That's told the Surgery object all things below it (ie, Owners and Pets objects) will get their information from the Surgery database.
- Right-click Owners and select Properties
- Click the 'SQL Statement' radio button and enter the following SQL:
Select * from Owners
This statement simply tells the Data Environment's Owners object to retrieve all fields from the Owners table.
- Click OK
- Tap the little + next to the Owners object
You should see a list of fields your query returns OwnerID, OwnerName and Address. But in this report, I want to display each owner followed by every pet name and breed owned by that character. So we need to tell our Data Environment about the Pets table:
- Right-click Owners and select 'Add Child Command'
- Rename the command you just created, 'Pets'
- As with the previous command object, enter an SQL statement of:
Select * from Pets
- Click OK
So far we've told the Data Environment about the existence of both tables, but not how they're related to each other. So:
- Right-click Pets and select Properties
- Click the Relation tab this is where you define the link between the parent object Owners and the child object Pets
- In Parent Fields combo box, select OwnerID and likewise in Child Fields. Then click the 'Add' button. This links the two lots of information together
- Click OK
Top Tip: You can change the SQL statement behind your 'command' in code with -
YourDataEnvironmentName.Commands("Owner").CommandText = _"select * from owners"
This allows you to dynamically change the database information your reportwill be based on. So instead of selecting ALL the owners, you could perhapspick out just one.
So we've sorted out the information we want to get from the database... now we just need to throw it into a report:
- Click Project, Add Data Report
This is the report window in which you may design your report. But before telling it what to display, you need to tell it to link to your Surgery Data Environment:
- Click on the 'DataReport1' title to display relevant information in the Properties window
- Change the DataSource property to Vets and the DataMember to Owners
- Now right-click anywhere on the Data Report and select Retrieve Structure clicking Yes when prompted
Now select Window, Tile Vertically. This should enable you to see both the Data Environment and Data Report screens at once.
First, drag the OwnerName field from the DataEnvironment direct over to the 'Group Header (Owners_Header)' band. Do the same with the Address field.
Next, drag and drop the PetName and Breed fields over from the Data Environment into the 'Detail (Pets_Detail)' band.
Now try formatting the report. Perhaps you'd like to bold or rename a few of the field labels, shrink the Details section or add a title using the toolbox controls.
This is what my report looked like after a little fiddling:
Return to the main 'Groovy Pet Adder' screen and add a command button with a caption of 'View List of Pets'. Behind that button, add the code:
where 'MyReport' is, err, the name of your report.
Then run your program and hit the command button. Your report should look something like this:
Note the buttons in the top left-hand corner. Although you can remove these by setting a few properties, they allow your user to easily print the report even export it to various file formats, including a HTML document
Cool, eh? In fact, it's cooler than Mr Cool the Cola Bear, winner of... oh darn, I've used that one.
For more information on creating a Data Report, search help for "Creating a Simple Data Report".