Data Management

FrontPage and Access guest books

Rick Dobson shows you how to collect contact information with FrontPage 2000 wizards in HTML format.

Web sites often run promotions that offer prizes or other benefits in exchange for visitors submitting contact information and completing a guest book form. FrontPage 2000 (and earlier versions of FrontPage) enable web administrators to record contact information in HTML format to a web page for easy viewing from FrontPage. You also can transfer guest book registrations directly to a Microsoft Access database. However, in some situations, this direct transfer may not be appropriate. For example, FrontPage may be running on a UNIX system that doesn’t support Access, or the web server may not have Access installed.

In this article, we’ll show you how to collect contact information with FrontPage 2000 wizards in HTML format. We’ll present and describe VBA procedures for transferring contact data from a web page file to a table in an Access database. The approach doesn’t require Access to be running on the server, and a local web administrator doesn’t need to be knowledgeable about Access databases, DSN connections, or other esoteric information for this technique to work.

Overview of FrontPage guest books
A FrontPage guest book application consists of at least two files. The initial file is a web page with the HTML form for gathering contact information. You open the first file with a browser. Clicking the Submit button on the form generates a confirmation message and transfers the form's field values to the second file. Think of this second file as a register that stores the contact information gathered from the form.

FrontPage offers a wizard to design and implement a guest book application. The New Page wizard offers preformatted form fields for gathering contact information. You also can add your own fields and reformat the wizard's fields.

The New Page wizard adds all the fields and buttons to the form. You readily can customize the form by adding a title for the form, field descriptions, and instructions for completing the form.

Clicking the Submit Form button opens the default confirmation page. This page echoes the entries in the form, and it offers a hyperlink for returning to the form. If your application calls for a different style of confirmation, you can replace the default confirmation page with a custom one that’s designed specifically for your needs. Even with the default confirmation page, users can navigate easily to recently visited pages at your site with the Back button.

While the guest register appears as a formatted text page on the Normal tab, the HTML tab includes HTML formatting codes to position the text on the page. These codes lay out the contact information in such a way that web administrators can read it easily. At the same time, these codes complicate the task of programmatically capturing the contact information for entry into an Access database.

Creating a guest book form and register
You use the File | New | Page command to launch the creation of a new guest book form and its register. In the New dialog box, select Form Page Wizard rather than Guest Book. The Guest Book icon creates a log for visitors who leave comments, but it does not offer any assistance in gathering contact information, such as name, street address, or e-mail address.

After highlighting the Form Page Wizard, click OK to launch it. Click past the introductory dialog box that explains what the wizard does; then, click Add in the second dialog box to select a type of data for collection. In the third window, select Contact Information in the top box. Notice that a prompt appears in a box below. As you change the type of information that the form collects, the prompt adjusts automatically. You can overwrite the default prompt and input a custom prompt.

The next dialog box lets you select different types of information for your form. Generally, you’ll want to save first and last name fields separately, rather than saving a full name in a single field. It’s easier to look up registrants in a database by their last names. You may want to remove fields that aren’t essential to your application or add fields that are especially important to you.

After selecting the fields for a type of information, you can return to select fields for other types of information. When you’ve finished selecting preformatted field options, click Next, and you’ll see a dialog box that lets you control the presentation of your form. Its default selections are often appropriate. The next-to-last dialog box allows you to specify an output format for the data gathered by the form. This example uses the default choice of a web page format. This dialog box also specifies a filename for the guest register file. Click Finish to open the form on a new page.

Completing the Form Page Wizard generates a form template with built-in features that you can adjust. The template contains a page title, a brief description of the form's purpose, and simple instructions for completing the form. You can adjust built-in features and overwrite template entries to fit the special objectives of your situation. The form fields occupy the second column of a table that controls the form's layout. Deleting a field is as easy as selecting it and choosing Edit | Delete. Deleting a row is a two-step process. First, position the cursor in the table's first column and choose Table | Select | Row. Then, issue the Table | Delete Cells command.

You also can modify the built-in field settings. For example, the default URL field width is 25 characters and is too narrow for many URLs. You may want to lengthen this field, as well as the one for the e-mail address.

Adding a form field is a two-step process.First, position the cursor just before the row at which you want to add a new form field. Select | Insert | Rows Or Columns. In the Insert Rows Or Columns dialog box, accept the default settings by clicking OK. After you add the new row, add a form field to its second column. Choose Insert | Form to open a selection of form field types. If you don’t see the field type that you want, select the double down arrow to expose all available field types. After customizing a form field's properties, you can add a label to the first column that identifies the type of information to enter in the new field.

Once you’ve completed the design of a form, save it to a folder by using the File menu’s Save As command. In the Save As dialog box, highlight a target folder for the guest book web page in the Save In drop-down box. Designate a name for your guest book form page and click Save. When your page with the form saves, you may have to click View | Refresh to expose your guest book register. Remember that you designated a filename when you used the Form Page Wizard. This filename is likely to appear initially in the root folder, but you’ll want to drag and drop it into the Private folder. FrontPage will update your form's settings automatically with the new location for its register.

Using a parser for going from a FrontPage register to an Access table
Creating a guest register as a web page is useful when you want to view guest registrants’ contact information with FrontPage. The HTML version of a web page formats the contact data for easy display with the HTML tab view of a page. You can navigate with the Page Up and Page Down keys or the arrow keys.

You need a VBA parser to extract data from a guest register and enter it in an Access table. There are two reasons for this need:
  • Since Access displays the text best without the HTML tags, you can use the parser to strip the tags (and other special HTML codes) for easy viewing.
  • Access represents guest registrants in a table one row at a time. However, FrontPage represents each field's entry by a pair of rows—one for the label and one for the field's value. The parser needs to collect multiple rows from the HTML formatted page and transfer them into a single row for Access.

As you work with a web-based guest register, you’ll make a copy of the register and start over periodically with a new empty register file. The copy retains the entries to the register. This process of emptying the guest register speeds its opening from within FrontPage. You can parse these older register copies while FrontPage records new registrants in the original version.

Overview of the VBA register parser
The program that performs the parsing of the guest register has four major parts. First, some global declarations make selected variables and objects available to multiple procedures within the application. Second, a sub procedure opens the HTML formatted guest register file as a text file and loops through its lines to find the start of a record. This sub procedure also opens a recordset based on an Access table that holds the parsed contact data. When the first sub procedure finds the beginning of a contact record, it calls a second sub procedure. The third part parses the data from the HTML formatted text stream and adds the parsed data to the recordset that was opened in the first procedure. If the new record already has duplicate primary key values to a record in the recordset, then the procedure overwrites the old record with the new values. The second sub procedure performs some of its parsing by calling a function procedure that participates as the fourth part of the process. This function procedure strips a text line of special HTML escape codes and replaces them with their character counterpart. Listing A shows an excerpt from the full application for parsing data from an HTML guest register into an Access table.
Dim txtobj1 As Scripting.TextStream
Dim strTemp As String
Dim rst1 As ADODB.Recordset

Sub LookForNameStart()
Dim fs As Scripting.FileSystemObject
'Form a reference to the file system
'and use it ot open a text object based
'on the local file holding the GB register
Set fs = New Scripting.FileSystemObject
Set txtobj1 = _
fs.OpenTextFile _
("C:\Inetpub\wwwroot\TRSamples" & _
"\formrslt_copy(3).htm", _

'Open a recordset on the tblContacts table
Set rst1 = New ADODB.Recordset
rst1.Open "tblContacts", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

'Loop through text object to find line
'just before the FirstName field
Do Until txtobj1.AtEndOfStream
strTemp = txtobj1.ReadLine
If InStr(1, strTemp, "Contact_FirstName") _
<> 0 Then
End If

'Cleanup resources
Set rst1 = Nothing
Set txtobj1 = Nothing
Set fs = Nothing

End Sub
Sub ProcessContact()
On Error GoTo MyErrorTrap
Dim strFname As String
Dim strLname As String
Dim strCname As String
Dim strSt1 As String
Dim strSt2 As String
Dim strCity As String
Dim strRegion As String
Dim strPostalCode As String
Dim strCountry As String
Dim strEmailAddr As String
Dim intFirst As Integer
Dim intLen As Integer
Dim cmd1 As ADODB.Command

'Extract First Name in Proper Case
strTemp = txtobj1.ReadLine
If InStr(1, strTemp, "&nbsp;") = 0 Then
intFirst = InStr(1, strTemp, ">") + 1
intLen = InStr(InStr(1, strTemp, ">"), _
strTemp, "<") – intFirst
strFname = UCase(Mid(strTemp, intFirst, 1)) & _
LCase(Mid(strTemp, intFirst + 1, intLen - 1))
strFname = ""
End If

'Extract Last Name in Proper Case
strTemp = txtobj1.ReadLine
If InStr(1, strTemp, "&nbsp;") = 0 Then
intFirst = InStr(1, strTemp, ">") + 1
intLen = InStr(InStr(1, strTemp, ">"), _
strTemp, "<") – intFirst
strLname = UCase(Mid(strTemp, intFirst, 1)) & _
LCase(Mid(strTemp, intFirst + 1, intLen - 1))
strLname = ""
End If

'Extract Organization Name in any case
strTemp = txtobj1.ReadLine
If InStr(1, strTemp, "&nbsp;") = 0 Then
intFirst = InStr(1, strTemp, ">") + 1
intLen = InStr(InStr(1, strTemp, ">"), _
strTemp, "<") – intFirst
strCname = CleanText(Mid(strTemp, _
intFirst, intLen))
strCname = ""
End If

'Extract selected other fields, including strEmailAddr
'Add a record if it has a valid primary key
If strFname <> _
"" And strLname <> "" And _
strEmailAddr <> "" Then
With rst1
If strFname <> _
"" Then .Fields("FirstName") = _
If strLname <> _
"" Then .Fields("LastName") = _
If strCname <> _
"" Then .Fields("CompanyName") = _
If strSt1 <> _
"" Then .Fields("Address") = _
If strSt2 <> _
"" Then .Fields("Address1") = _
If strCity <> _
"" Then .Fields("City") = _
If strRegion <> _
"" Then .Fields("StateOrProvince") = _
If strPostalCode <> _
"" Then .Fields("PostalCode") = _
If strCountry <> _
"" Then .Fields("Country") = _
If strEmailAddr <> _
"" Then .Fields("EMailName") = _
End With
End If

Exit Sub

If Err.Number = -2147217887 Then
'Trap duplicate key error and replace record
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = _
.CommandText = "DELETE * " & _
"FROM tblContacts " & _
"WHERE tblContacts.EMailName " & _
"= '" & strEmailAddr & "'"
.CommandType = adCmdText
End With
Debug.Print Err.Number; Err.Description
Resume MyExit
End If

End Sub
Function CleanText(strText As String)

'Replace HTML special characters
'such as &amp; with & and &quot; with "
CleanText = Replace(strText, "&amp;", "&")
CleanText = Replace(CleanText, "&quot;", """")

End Function

The declarations before the procedures in Listing A are global ones that make a text object, a recordset, and a string variable to all the procedures available. The text object declaration requires a reference to the Microsoft Scripting Library. The first sub procedure, LookForNameStart, opens a copy of the guest register, formrslt_copy(3).htm, as a text file. It also opens a recordset based on the tblContacts table in the current project. You must customize both of these lines when you’re applying the technique to your sample guest register and Access table for storing the data. After looping through the last line in the text file, the procedure cleans up by closing open resources.

The second procedure performs the parsing of a guest register entry and adds that entry to an Access table. Various built-in functions and a function procedure parse the data entered by guest registrants. The sample code uses the same kind of techniques to extract the fields that show as the techniques that were excluded from the excerpt to save space. After parsing the data, the second procedure invokes the AddNew method for the Recordset object to add the new data to the Access table. If the new primary key values duplicate those for a record already in the recordset, the procedure generates a run-time error. The second procedure traps this error, deletes the old record, and attempts to add the new record again.

Generating an HTML-formatted guest register is a straightforward task with FrontPage 2000. With VBA, an intermediate-level programmer can transfer the guest register to a table in an Access database. This approach has appeal in situations where FrontPage runs on a UNIX server or web administrators have limited database experience. The excerpt from a VBA sample in this article demonstrates typical steps for parsing guest registration data for an Access database. You can get the full VBA code sample from the Samples section at my Programming Access site.

Rick Dobson, Ph.D., and his wife operate a development and training consultancy. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic and numerous computer periodicals. In addition, he has presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains ( and ).

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks

Free Newsletters, In your Inbox