Navigation:  Articles > Jan-1998 >

Access the PalmPilot Using Pendragon Forms

Previous pageReturn to chapter overviewNext page

199802_ip1 Ivan Phillips          

 
One of the premier development tools for the hot new Personal Digital Assistant is Pendragon's Access-based development system. Here's a look at what you can do with this tool from the system's developer.

 

In August 2011, HP decided that the latest version of the Palm operating system, web os, would be phased out

Over the past two years, palmtop computers have become hot items, both with consumers and corporate users. Part of the reason for this resurgence is last year's release of the 3Com PalmPilot. The PalmPilot organizer was designed with simplicity and efficiency in mind. The device has only seven small buttons, a serial port, and a touch screen, but the PalmPilot's simplicity, light weight, and long battery life have helped it capture over 50 percent of the market (according to Dataquest). This success has led IBM to release an OEM version of the PalmPilot called the IBM WorkPad.

 

 
Although the PalmPilot won't run Microsoft Access, you needn't feel left out. Users do want to collect their data on a pocket-sized device, but they want to manage and analyze it with desktop tools like Access and Excel. In June of 1996, Pendragon was inspired to create a mobile data collection tool for the PalmPilot that would interface with desktop databases. Pendragon chose Microsoft Access as the desktop component for our product, which was later named Pendragon Forms. Pendragon Forms was designed with two goals in mind: to be used by non-programmers, and to be extended by developers and power users.
 
In this article, I'll demonstrate a few of the capabilities of Pendragon Forms for PalmPilot by creating a helpdesk workorder entry system. In this simple example, trouble tickets are entered on the PC and dispatched to handheld units according to the PalmPilot user name. The tickets will remain on the PalmPilot until the mobile user checks a completion box on the form (see the sidebar, "Keeping It Hot: The PalmPilot, Your PC, and Access," for a description of how a PC interacts with a PalmPilot).
 
First, I'll create the data collection form and set the synchronization rules with the built-in functionality of Pendragon Forms. After the basic foundation is in place, I'll show you how to extend these capabilities with a little VBA code. This example illustrates just one approach to customizing the basic system provided by Pendragon Forms.
 
Pendragon Forms
Pendragon Forms consists of three components: a runtime program for the PalmPilot, a conduit DLL, and a desktop database and designer called the Pendragon Forms Manager. The Pendragon Forms Manager is an Access database that stores both the form design and the user form data.
 
The Pendragon Forms conduit reads and writes ASCII files in a data transfer directory on your PC. The Pendragon Forms Manager is really a front end to these ASCII files.

199802_ip1Figure 1
 
The first step in creating a form is to design the interface of the form. This can be done very easily, even by end-users, because of the form designer's wizard-like interface. Figure 1 shows the form designer in action. After entering the form name, the user simply enters the field names and selects the field types (there are 18 different field types to choose from). The field types are listed in Table 1, along with the corresponding Access field types that are used to hold the data.
 
Table 1. Pendragon Forms field types.

Field Type

Description

Access Datatype

Text

Freeform text, up to 511 characters. Also includes support for bar code reader input.

Memo

Numeric

Up to 10 digits fixed point.

Double

Currency

± 9,999,999.99

Currency

Yes/No

Appears as a selector or check box.

Text ("Y," "N," or NULL)

Scale of 1 to 5

Can also display any scale of up to 5 cells for example, ABC.

Long integer

Pop-up List

A list of up to 127 characters, typically 8 items or less.

Text

Date + Time

Date and Time

Date/Time

Date Only

Date Only

Date/Time

Time Only

Time Only

Date/Time

Time Check box

A check box that records the time it was checked.

Date/Time

Jump Pop-up List

A Pop-up List for which the selection takes the user to the named Section field.

Text

Section

Used to separate sections on a form and as the targets for Jump Pop-up List entries.

Text (not used)

Lookup List

A list of up to 1,000 items. The user can write in a response if there are no applicable responses in the list.

Text

Exclusive

A Lookup List field for which the Lookup List user can't write in an entry not in the list.

Text

Signature

Prompts the user to enter a digital ink signature.

Text (the name of the bitmap file)

Completion Check box

A check box that can be used to determine whether a record should remain on the PalmPilot.

Text

Subform List

A Pop-up List, but the entries refer to other forms. When a selection is made, the PalmPilot displays the requested form.

Text (not used)

Read-Only Text

A text field that can't be updated on the PalmPilot.

Memo

 
 
The form designer displays a preview of the form as it will appear in Field View on the PalmPilot runtime. Forms can also be displayed in Record View, which shows 11 fields on a screen.
 
The design of the work order form is shown in Table 2. Here, I created fields for the user name, location, and description of the problem. I also created fields to be entered by the mobile user, including fields to record the resolution of the problem, the start and completion times, and a completion check box.
 
Table 2. The design of the Help Desk Work Order form.

Field Number

Field Name

Field Type

1

User name

Text

2

Location

Text

3

Problem Description

Text

4

Resolution

Text

5

Time started

Date + Time

6

Time completed

Date + Time

7

Completed

Completion Check box

Each form design has data persistence properties that determine what happens to records on the PalmPilot after a HotSync. These properties are set via the Form Properties dialog box, shown in Figure 2. The default behavior is to remove records from the PalmPilot. This is ideal for applications such as expense reports, surveys, and inspections. In this case, records must be kept on the PalmPilot until the completion check box is marked by the mobile user. This is achieved by checking the "Keep incomplete records on PalmPilot" box in the form properties.

199802_ip2 Figure2
 
The next step is to freeze the form design. Freezing a form design serves two purposes. The first is to create a table in the database that will hold collected data. As you can see in Table 3, Pendragon Forms adds four control fields to the table layout. These fields are used to identify records across multiple PalmPilots. The TimeStamp field is set to the date and time the record was created on the handheld. The UnitId field can be configured to identify a particular PalmPilot but is usually left as its default value of zero. The TimeStamp and UnitId form the primary key for the table in the PalmPilot's datastore. Each PalmPilot has a user name, and this is recorded in the UserName field.
 
Table 3. The resulting table created when the form is frozen.

Field Name

Field Type

RecordId

Long Integer

UnitId

Long Integer

UserName

Text

TimeStamp

Date/Time

User name

Memo

Location

Memo

Problem description

Memo

Resolution

Memo

Time started

Date/Time

Time completed

Date/Time

Completed

Text

 
Choosing to distribute the form design schedules the transfer of the design to the PalmPilot during the next HotSync. On the PalmPilot, the form will look like the ones in Figure 3, Figure 4, and Figure 5.

199802_ip3 Figure 3 199802_ip4 Figure 4 199802_ip5 Figure 5
 
Out of the box, Pendragon Forms provides some basic data management facilities. It's possible to edit data in the Access table and then transfer it to the PalmPilot via the Send To PalmPilot dialog box pictured in Figure 6. This simple dialog box allows data to be sent to the PalmPilot either by user name or by specifying a SQL WHERE clause. In the case of this Work Orders system, some of this built-in functionality will need to be enhanced and automated.

199802_ip6 Figure 6
 
Enhancing the built-in functionality
An effective dispatching system must have an easy way for a dispatcher to create a new work order. The system also needs a way to automatically distribute work orders to the appropriate user, and track the status of work orders.
 
An Access form for entering new records into the table can be easily created by Pendragon Forms. This form will be the creation point for new work orders. There are two special considerations here. The first is that the uniqueness of the UnitId-TimeStamp combination be maintained. One approach is to use the UnitID field as a Work Order ID number. It's straightforward to create a unique index on the UnitId field and write a function to provide the next unique Work Order ID number. This function, and its use in the BeforeInsert event, can be seen in Listing 1.
 

Listing 1. A function to generate a unique Work Order ID.

 

Function NewWorkOrderId()

 

 Dim db As Database

 Dim rs As Recordset

 Dim result%

 On Error Resume Next

 Err = 0

 Set db = CurrentDb()

 'Open the work order id table

 Set rs = db.OpenRecordset("select lastWorkOrderId " & _            

 "from WorkOrderID", dbOpenDynaset)

 'Guarantee no one else is doing this at the same time

 rs.LockEdits = True

 'Get the first record

 rs.MoveFirst

 'Update the counter by one

 rs.Edit

 result% = rs!lastWorkOrderId

 result% = result% + 1

 rs!lastWorkOrderId = result%

 rs.Update

 If Err > 0 Then

   MsgBox "Unable to create unique " & _

  "Work Order ID number. " & Error$

 End If

 NewWorkOrderId = result%

End Function

 

 

Private Sub Form_BeforeInsert(Cancel As Integer)

 'Assign a unique UnitId

 UnitID = NewWorkOrderId()

 If Err > 0 Then

   Cancel = True

 End If

End Sub

 
 
The Work Order Entry form also includes a combo box to select a Technician name. The names in the combo box are generated from the UserName field values in the table itself. This input will determine which user will receive the work order on his or her PalmPilot.
 
Now, it's time to tackle the more challenging part. How do the right work orders actually get onto the appropriate PalmPilot? The nicest solution would be to generate each user's list of work orders at the time they HotSync. This can be done by using the Pendragon Forms custom control to intercept the HotSync. The Pendragon Forms Manager uses this same custom control to trap the end of the HotSync process and read data from temporary ASCII files into the appropriate tables. In this case, the HotSyncStart event of the control can be used to identify the user and write an ASCII file containing the relevant work orders for the user's PalmPilot. I hosted the ActiveX control in my Access form.
 
Included with the source code for the Pendragon Forms Manager is a procedure called WriteFormData
that passes selected data in a string, formatted so the conduit can transform it into PalmPilot data records. With carefully chosen parameters, this function will serve to create the ASCII file that will be used during the HotSync. The procedure accepts the form ID number, a file number, a user name, and a SQL WHERE clause:
 

Sub WriteFormData(wFormID As Variant, wFileNum%, _                            

sUsername As Variant, sSQL As Variant)

 
 
The HotSync manager retrieves the user name associated with the PalmPilot currently being HotSynced, and this information is placed in the conduit in the file RECENT.DAT in the data transfer directory. RECENT.DAT is updated immediately, before the HotSyncStart event is fired. Here's a sample RECENT.DAT file:
 

Username=Ivan Phillips

Date=24-Oct-97 16:07:25

 
 
My code for the HotSyncStart event reads the user name from the file and then calls WriteFormData to prepare outbound data for that user (see Listing 2).
 

Listing 2. Trapping the start of the HotSync with the

Pendragon Forms ActiveX control.

 

Private Sub PFCtl_HotSyncStart()

 Dim sFilename$, sLine$, sUsername$

 Dim wFileNum%

 Dim ok%

 On Error Resume Next

 Err = 0

 sFilename$ = gDefaultPath$ & "\RECENT.DAT"

 'Get a free file number

 wFileNum% = FreeFile

 Open sFilename$ For Input As #wFileNum%

 'Read the first line of the file

 Line Input #wFileNum%, sLine$

 'Extract the user name

 sUsername$ = _

  Right$(sLine$, Len(sLine$) - InStr(sLine$, "="))

 Close #wFileNum

 If Err > 0 Then

 MsgBox "Unable to identify the PalmPilot user. " _

  & Error$

 Else

   'output the appropriate data

   ok% = OutputWorkOrders%(sUsername$)

 End If

End Sub

 

 

Function OutputWorkOrders%(sUsername As String)

 

 Dim wFileNum%

 Dim wFormID

 Dim sFilename$

 Dim ok%

 'This is the form ID we froze

 wFormID = 62866047

 'We can compute the path name

 'Note: gDefaultPath$ is a global variable

 'defined by the Pendragon Forms Manager

 sFilename$ = gDefaultPath$ & "\" & _

   Hex$(wFormID) & ".PLT"

 OutputWorkOrders% = True

 On Error Resume Next

 Err = 0

 If LockConduit%() Then

   'Get a free filenumber

   wFileNum% = FreeFile

   Open sFilename$ For Output As #wFileNum%

   If Err = 0 Then

   

     'This call writes the relevant data

     Call WriteFormData(wFormID, wFileNum%, "", _

       "UserName = '" & sUsername & "' and _

RecordId = 0 and Completed is null")

     Close #wFileNum

   Else

     MsgBox "Unable to open file " & sFilename$ & "."

     OutputWorkOrders% = False

   End If

 

   ok% = UnlockConduit%()

 Else

   'Another application is using the directory

   OutputWorkOrders% = False

 End If

End Function

 
 
This work order example is, as it stands, rather simplistic. A full-fledged application would track which records had been sent to the PalmPilot, and provide for contingencies such as restoring data to a PalmPilot that lost persistent storage. However, as you can see, very simple data collection applications can be built in minutes, while multi-user applications can be built in a day.
 
The tip of the iceberg
Of course, there are hundreds of possible applications for this technology. We've seen customers use Pendragon Forms for everything from tracking lobster populations to inventory control. The software's support for an external bar code reader and for digital ink signatures extends the power of the PalmPilot/Access combination even further.
 
Given the ease of development, the ease of use, and the low cost of the PalmPilot, I think that we'll see many PalmPilot-enabled Access applications in the next few months.
 
Download PDRAGON.ZIP
 
Ivan Phillips is the president of Pendragon Software Corporation.
 
Sidebar: Keeping It Hot: The PalmPilot, Your PC, and Access
 
The PalmPilot communicates with the PC via a HotSync. A HotSync is a serial conversation between the PC and the handheld, during which each application on the PalmPilot has the opportunity to share its data with the PC. The HotSync Manager on the PC controls the HotSync process by listening for the HotSync request from the PalmPilot, and then executing conduit DLLs to perform data transfers to and from the PalmPilot. The system is extensible so that custom applications on the PalmPilot can have their own custom conduits to transfer their data to the appropriate PC or server data format. For example, a spreadsheet application on the PalmPilot would use a conduit to read and write Excel or 1-2-3 files on the PC, and read and write corresponding data records on the PalmPilot.
 
The HotSync architecture results in a one-button synchronization process. The user drops the PalmPilot in its cradle and presses a button to synchronize every application. This ease of use, coupled with the PalmPilot organizer's speed and small size, make it very appealing as a data collection tool. Developing a PalmPilot application from scratch is a daunting task if you're using C/C++. With Pendragon Forms, the task is rather easy.
 
Pendragon Forms allows you to design a data collection form for the PalmPilot without any programming. When you freeze the form design, Pendragon Forms creates an Access database table corresponding to the fields on the form. The form design can then be sent, via HotSync, to the PalmPilot, and data collection can begin. Subsequently, each HotSync will transfer collected data to the PC and provide events for an Access application to use to participate in the process.