January 2000

Present Kiosk Data Interactively with Access and PowerPoint

by Sean Kavanagh

PowerPoint has typically been the Office application most neglected by Access developers. This is understandable-although it's likely that developers use Word, Excel, or Outlook in their daily operations, most programmers don't have an immediate need to create PowerPoint presentations. It's often easy to think of presentations as disposable applications that would hardly benefit from automation-you show them once, and then throw them away.

Contrary to this line of thought, PowerPoint's kiosk presentations are ideally suited for integration with Access data. In this article, we'll show you how to build an interactive presentation, like the one shown in Figure A. Your audience will be able to modify the presentation's graphics and text on the fly, using presentation detail stored in an Access database. While we won't get into a great deal of detail about our code, you can easily use snips of it to develop your own applications.

Figure A: Users control what data is displayed in this presentation using a combo box.
Figure A

Why use kiosks?

Most people associate PowerPoint presentations with staff meetings and sales pitches-window dressing to spruce up a presenter's speech. A kiosk is a slide show that runs continuously, independent of a presenter. Since it runs unattended, you can use a kiosk presentation in the lobby of your office to educate visitors about your company's products. Several kiosks at a trade show let you influence a wide audience while leaving your sales force free to concentrate on serious buyers.

How can Access help?

If your presentation displays similar information about different topics, you can reduce development and maintenance time by storing the presentation's slide details in an Access database. In addition, the presentation will have greater impact-instead of being just a slide show that your audience watches passively, your audience is able to control what data is displayed.

For example, suppose your company owns several product lines. It might be tempting to create slides with the details for every product. However, if you have the same type of information for each product-price, features, pictures, etc.-you can simply create one set of slides for each topic. Then, when the kiosk is running, users can select which product they want information on and the slides are modified accordingly with code. If you tie the presentation to live data in your inventory database, viewers will accurately know what products are available for purchase.

Set up the sample files

For our example, we'll build a self-running slide show that displays information about your company's products. Users select which product they're interested in using a combo box.

To create our presentation, we'll save time by using sample data and templates that come with Access and PowerPoint. Keep in mind that we're taking some liberties with the data, graphics, and normalization, since our focus here is on technique. Also, we won't update every aspect of the presentation, we'll just make selective modifications to illustrate different techniques and help you develop your own ideas.

Create the basic presentation

To create the skeleton presentation for your kiosk, start PowerPoint and choose File/New from the menu bar. Click on the Presentations tab and double-click on the Product Overview (Online).pot icon. (If you're using PowerPoint 2000, you can download the template from our FTP site). After PowerPoint creates the basic presentation, save your file as Kiosk. Before continuing, take a few moments to familiarize yourself with the layout of this presentation.

Organize your graphics

For our example, we'll use clip art that comes with Office 97 for the presentation graphics. However, you can use files of almost any standard graphic format.

All of the graphics will be stored in one location on your hard drive. Open your My Documents folder. Then, create a subfolder entitled Presentation Images. Copy the image files you want to use into this folder. You'll need a total of five images. To use the same graphics we do in the article, copy the following files from the \Clipart\Office\ directory on the Office 97 CD: Tea4two.wmf, Beermug.wmf, Dnrgravy.wmf, Pepper2.wmf, and Mussels.wmf.

Create the database

Now we're ready to create the Access database. Our database will be based on Access's inventory template. Start Access and choose File/New Database from the menu bar. Click on the Databases tab and then double-click on the Inventory Control.mdz icon. Enter KioskData in the File Name text box, and then click Create. When the Database Wizard appears, click Next, select the Yes, Include Sample Data check box, and then click Finish.

After the wizard is finished building the database, you need to make some modifications to the Products table to accommodate additional data. To do so, restore the Database window and open the Products table in Design view. Leave the existing fields as they are and add the new ones shown in Table A.

Table A: New fields for Product table
Field Property Value
ProductPicture Data Type Text
SupplierID Data Type Number
Features Data Type Text

 Field Size 255

When you've finished, save the table and switch to Datasheet view. Before we enter any data in the new fields, let's discuss what they do. The ProductPicture field will contain the filename of the graphic to display on the first slide of the presentation. The SupplierID field is used to join the Products table to the Supplier table. Ordinarily, you would need to use a linking table to do so, but we'll break the rules and take this shortcut. Features will store the bullet items that appear on the Features & Benefits slide in the kiosk presentation.

If you aren't familiar with this database structure, take some time to examine the tables and relationships. When you're ready, enter the additional data that your presentation will use in the Products table. Using Table B and the ProductID field as guides, populate the newly created fields. Other than the SupplierID information, feel free to use your own pictures and bullets. To enter a line break between bullets, press [Ctrl][Enter]. Finally, save and close the database and exit Access.

Table B: New field values
ProductID ProductPicture SupplierID Features
1 Tea4two.wmf 4 Imported directly from India
Proceeds aid refugees
2 Beermug.wmf 1 Smooth and relaxing
It's Namastésty!
3 Dnrgravy.wmf 3 Unique flavor
Quake-proof serving bottle
4 Pepper2.wmf 2 30% hotter than before
Cheaper than decongestants
5 Mussels.wmf 2 Guaranteed fresh
Gumbo with gusto

Modify the slide show layout

At this point, switch back to your PowerPoint presentation. Before we create the code that updates the presentation, we need to make some changes to the slide show.

First, we'll add the combo box that users will use to select product information. If necessary, move to the first slide of the presentation. We need to make some room for the control, so click on the box labeled Insert Product Photograph Here. Then, hold down the [Shift] key and use one of the corner resizing handles to shrink the box. You'll want about 1.5 inches between the bottom of the box and the edge of the slide. Finally, recenter the box on the slide.

Now, add the combo box. Right-click on a toolbar and select Control Toolbox. Click the Combo Box button on the Control Toolbox, and then click and drag to create a combo box on the bottom of the slide, leaving about an inch of space between it and the photograph box. Then, close the Control Toolbox.

Next, make a label for the combo box. Click the Text Box button on the Drawing toolbar. Then, click above the combo box and type Please select your product of interest. Finally, apply underline and bold formatting to the text.

Now let's assign the properties for the combo box. Right-click on it and choose Properties from the shortcut menu. At this point, you may be in for an unexpected surprise. One of the drawbacks to developing with PowerPoint is that it uses an entirely different group of ActiveX form controls than Access. We can't just assign an SQL statement to the control's Row Source property-it doesn't have one. Instead, we'll populate the control with code using the AddItem method. In the meantime, simply rename it cboSelectProduct.

At this point, we need to modify some objects in the presentation. The placeholders that currently exist in the slide have meaningless names like Rectangle 1, Rectangle 2, etc. Here's where another PowerPoint shortcoming rears its head. With the Properties dialog box still open, click on any other shapes on the slide. You'd expect to be able to change the Name property in the Properties dialog box. However, PowerPoint provides property information only for the current slide.

It might be tempting to just refer to the shapes using their present names. Unfortunately, PowerPoint doesn't provide an indication as to what the names are. To compound the issue, even when you know the names, many VBA methods fail when applied to shapes with default names. However, they work just fine with renamed shapes.

To rename the placeholders in the slide, we'll create a simple VBA utility. Close the Properties box, and press [Alt][F11] to launch the VBE. Then, select Insert/Module from the menu bar and enter the code shown in Listing A.

Listing A: Utility to rename slide shapes 

Sub Rename()
Dim strNewName As String
On Error Resume Next
With ActiveWindow.Selection.ShapeRange
    If MsgBox("Current Name: " & .Name & Chr(10) _
        & "Rename this object?", vbYesNo, 
"Object Name") = vbYes Then
          strNewName = InputBox("Enter new name: ", _
             "New Object Name", .Name)
             .Name = strNewName
     End If
End With
End Sub

Switch back to Slide 1 of the presentation and select the title placeholder, making sure that you can see the outline and sizing handles. Then, press [Alt][F8] and click the Run button in the Macro dialog box. The Object Name dialog box displays the current name for the object and prompts to rename it. Click Yes, enter strProduct, and click OK. Then, select the photograph box, press [Ctrl]Y to rerun the macro, and rename the shape picProductPicture. Rename the title and detail placeholders on the remaining slides, using the names shown in Table C.

Table C: New shape names
Slide Placeholder New name
2 Title strOverview
3 Title strFeatures
3 Bullet area strFeaturesDetail
4 Title strApplications
5 Title strSpecifications
6 Title strPricing
6 Bullet area strPricingDetail
7 Title strAvailability
7 Bullet area strAvailabilityDetail

Create the code to update the presentation

Before we create the code to drive the presentation, save your file. Then, switch back to the VBE. Since we'll be using Access functions to determine whether you have products in stock, you'll need to reference its Object library. Select Tools/References from the menu bar. Then, select the Microsoft Access 8.0 Object Library check box (or whichever is appropriate for your version). We'll also use DAO, so select the Microsoft DAO 3.51 Object Library check box. Click OK.

Now, double-click on Slide 1 in the project explorer. Select cboSelectProduct from the Object dropdown list and, if necessary, choose Change from the Procedure dropdown list. We'll attach the bulk of our code to this event. At the insertion point, enter the code shown in Listing B.

Listing B: Code to change presentation data 


Dim db As Database
Dim rst, rstAddress, rstInventory As Recordset
Dim wrk As Workspace
Dim intStock, intProdNum As Integer
Dim strStock, strPicturePath As String

On Error GoTo ErrorHandler:

intProdNum = cboSelectProduct.Column(0, _
    cboSelectProduct.ListIndex)

Set wrk = CreateWorkspace("", 
"admin", "", dbUseJet)
Set db = wrk.OpenDatabase("C:\My Documents\
KioskData.mdb"

Set rst = db.OpenRecordset("SELECT  
Products.ProductID, " _
    & "Products.ProductName, 
Products.ProductPicture, " _
    & "Products.UnitPrice, 
Products.Features " _
    & "FROM products WHERE 
products.ProductID=" _
    & intProdNum & ";")
  
Set rstAddress = db.OpenRecordset
("SELECT Suppliers.* " _
    & "FROM Suppliers INNER JOIN Products ON " _
    & "Products.SupplierID = Suppliers.SupplierID 
WHERE " _
    & "Products.ProductID=" & intProdNum & 
";")

Set rstInventory = db.OpenRecordset("SELECT " _
    & "Sum([Inventory Transactions].UnitsReceived) 
AS " _
    & "[TotalReceived]," _
    & "Sum([Inventory Transactions].UnitsSold) AS " _
    & "[TotalSold]," _
    & "Sum([Inventory Transactions].UnitsShrinkage ) 
AS " _
    & "[TotalShrinkage]" _
    & " FROM [Inventory Transactions] WHERE " _
    & "[Inventory Transactions].[ProductID]=" _
    & intProdNum & ";")

strPicturePath = "C:\My Documents\Presentation Images\" _
    & rst.Fields("ProductPicture")

intStock = Nz(rstInventory.Fields("TotalReceived")) _
    - Nz(rstInventory.Fields("TotalShrinkage")) _
    - Nz(rstInventory.Fields("TotalSold"))
If intStock < 1 Then
    strStock = "Available Soon!" & Chr(13)
Else
    strStock = intStock & " units currently in stock!" & Chr(13)
End If

With ActivePresentation.Slides("Slide1")
.Shapes("strProduct").TextFrame.TextRange.Text = _
    rst.Fields("ProductName")
    With .Shapes("picProductPicture")
        .Fill.ForeColor.RGB = RGB(255, 255, 255)
        .Fill.UserPicture strPicturePath
        .TextFrame.TextRange = ""
    End With
End With

With ActivePresentation.Slides("Slide2")
.Shapes("strOverview").TextFrame.TextRange.Text = _
    rst.Fields("ProductName") & ": Overview"
End With

With ActivePresentation.Slides("Slide3")
.Shapes("strFeatures").TextFrame.TextRange.Text = _
    rst.Fields("ProductName") & ": Features"
.Shapes("strFeaturesDetail").TextFrame.TextRange.Text = _
    Nz(rst.Fields("Features"), "Coming Soon!")
End With

With ActivePresentation.Slides("Slide5")
.Shapes("strApplications").TextFrame.TextRange.Text = _
    rst.Fields("ProductName") & ": Applications"
End With

With ActivePresentation.Slides("Slide6")
.Shapes("strSpecifications").TextFrame.TextRange.Text = _
    rst.Fields("ProductName") & ": Specifications"
End With

With ActivePresentation.Slides("Slide7")
.Shapes("strPricing").TextFrame.TextRange.Text = _
    rst.Fields("ProductName") & ": Pricing"
.Shapes("strPricingDetail").TextFrame.TextRange.Text = _
    "Currently priced at only $" _
    & rst.Fields("UnitPrice") & " per unit!"
End With

With ActivePresentation.Slides("Slide8")
.Shapes("strAvailability").TextFrame.TextRange.Text = _
    rst.Fields("ProductName") & ": Availability"
    With .Shapes("strAvailabilityDetail").TextFrame. _
        TextRange
        With .ParagraphFormat
            .Bullet.Visible = msoFalse
            .LineRuleWithin = msoTrue
            .SpaceWithin = 0.75
        End With
    .Text = strStock & Chr(13) & "Manufactured By: " _
        & Chr(13) & rstAddress.Fields("SupplierName") _
        & Chr(13) & rstAddress.Fields("Address") _
        & Chr(13) & rstAddress.Fields("City") _
        & " " & rstAddress.Fields("StateOrProvince") _
        & Left(rstAddress.Fields("PostalCode"), 5)
    .Lines(3, 1).Font.Underline = msoTrue
    End With
End With

Set rst = Nothing
Set rstAddress = Nothing
Set rstInventory = Nothing
Set db = Nothing
Set wrk = Nothing
Exit Sub

ErrorHandler:
Select Case Err
    Case 321
        Resume Next
   Case 381
        Exit Sub
   Case Else
        MsgBox "Error: " & Err.Number _
           & "  " & Err.Description
End Select

All that's left is to add the code that populates the combo box with the product names from our Access table. This should be updated each time you start the presentation, so we'll also use this macro to launch the show. Enter the code shown in Listing C in Module 1. Then, close the VBE and save your presentation.

Listing C: Code to load list box and start kiosk 


Sub RunKiosk()
Dim db As Database
Dim rst As Recordset
Dim wrk As Workspace
Dim fld As Field
Dim cbo As ComboBox
Dim varProdlist

Set cbo = Slide1.cboSelectProduct
With cbo
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "0;3in"
    .TextColumn = 2
End With

Set wrk = CreateWorkspace("", 
"admin", "", dbUseJet)
Set db = wrk.OpenDatabase("C:\My Documents\
KioskData.mdb")
Set rst = db.OpenRecordset("SELECT  
Products.ProductID, " _
    & "Products.ProductName FROM products 
ORDER BY " _
    & "[ProductName];")
rst.MoveFirst

Do While Not rst.EOF
    varProdlist = Array(rst.Fields(0).Value, rst.Fields(1).Value)
    cbo.AddItem rst.Fields(0).Value
    cbo.Column(1, cbo.ListCount - 1) = rst.Fields(1).Value
    rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing
Set wrk = Nothing

With ActivePresentation.SlideShowSettings 
        . ShowType=ppShowTypeKiosk
    .Run.View.AcceleratorsEnabled = True
End With
End Sub

Run the slide show

To start your kiosk presentation, press [Alt][F8] and double-click on RunKiosk. When the show first starts, the placeholders are still blank. However, as you select choices from the combo box, the slides change accordingly.

Compare the slides to the appropriate sections of code to see how the property changes affect each slide. For example, we disabled the bullets on the Availability slide. Otherwise, each line of the address would be bulleted, like the separate lines on the Features slide.

Also experiment with changing the data in the KioskData database and reviewing the results in the presentation. See what happens when you remove all the bullets from the Features field or when you change inventory counts by adding transaction records (use the Products form to do so).

Conclusion

Although integrating PowerPoint and Access requires some work, the payoff is worth it. By leveraging the power of Access through PowerPoint, you can create original, attention-grabbing kiosks.


Copyright © 2000, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.