ACC97: How to Create Hyperlink Fields in ASP Forms

• Microsoft Access 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article discusses the behavior of Hyperlink fields on ASP forms that you create in Microsoft Access 97.

Three main problems you may encounter when you export a Microsoft Access 97 form with a Hyperlink field to ASP format are as follows:

• When you enter a valid uniform resource locator (URL) in the Hyperlink field on an ASP form, it does not create a valid hyperlink in the Microsoft Access table.
• Hyperlink fields on ASP forms are not live. In other words, nothing happens when you click the hyperlink.
• Hyperlink fields on ASP forms display the hyperlink as text separated by number signs (#).

NOTE: This article contains information about editing ASP files. It assumes that you are familiar with Active Server, Visual Basic Scripting, and editing HTML files. Microsoft Access Technical Support engineers do not support modification of any HTML, HTX, IDC, or ASP files.

MORE INFORMATION

Entering a Valid URL on the ASP Form Does Not Create a Valid Hyperlink

If you enter hyperlink data in your ASP form using the correct syntax for DisplayText, Address, and Subaddress, then Microsoft Access stores the data as a valid hyperlink in the Microsoft Access table, for example:

   Microsoft Home Page#http://www.microsoft.com#

   http://www.microsoft.com

   -or-

   www.microsoft.com

Hyperlink Fields on ASP Forms Are Not Live

This behavior occurs because ASP forms you create with Microsoft Access 97 use the HTML Layout Control to display text boxes. You must write VB Script to get a Web browser to follow hyperlinks displayed in the text boxes; Microsoft Access 97 was not designed to create VB Script that achieves this functionality when it creates ASP forms. The example in the "An Example That Works Around All Three Problems" section walks you through creating the VB Script to parse the actual URL from the parts of the Hyperlink field, and then to set the window.location.href property to this URL in the MouseUp event of the text box that contains the hyperlink.

Hyperlink Fields on ASP Forms Display Text Separated with Number Signs (#)

In Microsoft Access 97, all Hyperlink fields consist of three distinct parts separated by number signs: DisplayText, Address, and Subaddress. The DisplayText is what you see in the table or form in your database; the Address and Subaddress information tells Microsoft Access what you want to view when you click the hyperlink. For example, the following hyperlink uses the first and second parts of the hyperlink field:

   Microsoft Home Page#http://www.microsoft.com#

   #http://www.microsoft.com/accessdev#

An ASP form opened in your Web browser displays all three parts of the hyperlink information that is stored in your table, including the number signs. The example in the "An Example That Works Around All Three Problems" section shows you how to write VB Script that causes the Web browser to show only the DisplayText, or to show the Address and Subaddress if there is no DisplayText. The VB Script checks if a number sign (#) is the first character of the field's value. If so, then the Address and Subaddress is parsed and displayed on the ASP form. If not, then the Displaytext is parsed and displayed.

An Example That Works Around All Three Problems

1. Start Microsoft Access 97 and open the sample database Northwind.mdb.

2. Export the Suppliers form to ASP format in a folder on your Web server where you have Execute permission. This step creates two files: Suppliers.asp and Suppliersalx.asp.

3. Use Notepad or another text editor to open the Suppliersalx.asp file, and make the changes described below.

Modification #1: Saving a Valid Hyperlink in the Microsoft Access 97 Table

Near the top of the Suppliersalx.asp file, you will see the following code:

   Function GetCtrlQueryString()
      Dim szRet
      Dim i
      for i = 1 to cMaxCtrls
         if rgszCtrls(i, 1) = "" Then Exit For
         szRet = szRet & rgszCtrls(i, 2) & "=" & _
         MakeHTMLValue(Suppliersalx.Controls(rgszCtrls(i, 1)).Value) & "&"
      next
      GetCtrlQueryString = szRet
   End Function

NOTE: If number signs are entered, the code does not check to see if http:// is entered. This allows a user to explicitly enter a URL that may, for example, point to an Office Object, which is a valid URL in Microsoft Access 97.

   Function GetCtrlQueryString()
      Dim szRet
      Dim i
      Dim HomePageValue
      Dim PoundLoc
      for i = 1 to cMaxCtrls
      if rgszCtrls(i, 1) = "" Then Exit For
         if rgszCtrls(i,2)="HomePage" then
            HomePageValue = Suppliersalx.Controls(rgszCtrls(i, 1)).Value
            PoundLoc = instr(HomePageValue,"#")
            if poundloc = 0 then 'NO # SIGNS WERE ENTERED.
               'CHECK IF HTTP:// WAS ENTERED. IF NOT, CONCATENATE IT.
               if instr(HomePageValue,"http://") = 0 then
                  HomePageValue = "http://" & HomePageValue
               end if
               'CONCATENATE # SIGNS BEFORE AND AFTER THE INTERNET ADDRESS
               HomePageValue = "#" & HomePageValue & "#"
            else
               'CHECK IF ONLY ONE # WAS ENTERED. IF SO, POP UP A MESSAGE.
               if poundloc+InStr(StrReverse(HomePageValue),"#") = _
                  len(HomePageValue)+1 then
               msgbox "Hyperlinks should have two # signs."
            end if
         end if
         szRet = szRet & rgszCtrls(i, 2) & "=" & _
         MakeHTMLValue(HomePageValue) & "&"
      else
         szRet = szRet & rgszCtrls(i, 2) & "=" & _
         MakeHTMLValue(Suppliersalx.Controls(rgszCtrls(i, 1)).Value) & "&"
      end if
      next
      GetCtrlQueryString = szRet
   End Function

Modification #2: Making the Hyperlink Live

Further down in the Suppliersalx.asp file, after the last Sub Procedure but before the --></SCRIPT> tag, add the following code. This code parses the Address portion of the Hyperlink field and saves it in a variable. The HomePage_MouseUp Sub Procedure executes when the hyperlink text box is clicked and causes the Web browser to navigate to the URL saved in the AddrPart variable.

   <%If Not IsNull(rs.Fields("HomePage").Value) Then%>
      'SAVE THE ADDRESS PORTION OF THE HYPERLINK IN A VARIABLE IN CASE
      'MODIFICATION 3 WAS IMPLEMENTED AND THE HOMEPAGE TEXT BOX ONLY
      'DISPLAYS THE DISPLAYTEXT PORTION OF THE HYPERLINK
      Dim AddrPart
      AddrPart = "<%=rs.Fields("HomePage").Value%>"
      AddrPart = Right(AddrPart,Len(AddrPart)-InStr(AddrPart, "#"))
   <%End If%>

   Sub HomePage_MouseUp(Button, Shift, X, Y)
      window.location.href = AddrPart
   End Sub

Modification #3: Displaying the DisplayText or the Address and Subaddress

In the Suppliersalx.asp file, find the <OBJECT> tag for the HomePage text box. It will be beneath the last Sub Procedure you wrote. Look for the following code which sets the Value property for the text box:

   <%If Not IsNull(rs.Fields("HomePage").Value) Then%>
   <PARAM NAME="Value"
   VALUE="<%=Server.HTMLEncode(rs.Fields("HomePage").Value)%>">
   <%End If%>

   <%If Not IsNull(rs.Fields("HomePage").Value) Then%>
   <PARAM NAME="Value" VALUE="<%
   Dim GetPart
   GetPart = rs.Fields("HomePage").Value
   If left(GetPart, 1) = "#" then
      'THERE IS NO DISPLAYTEXT, SO DISPLAY THE ADDRESS AND SUBADDRESS
      GetPart = Mid(GetPart,2)
   Else
      If instr(GetPart, "#") = 0 then
         'THERE ARE NO # SIGNS, SO DISPLAY EVERYTHING
         GetPart=GetPart
      Else
         'DISPLAYTEXT IS EVERYTHING TO THE LEFT OF THE FIRST # SIGN
         GetPart = Left(GetPart,InStr(GetPart, "#")-1)
      End If
   End If
   Response.Write Server.HTMLEncode(GetPart)
   %>">
   <%End If%>

Testing the ASP Form

1. Start Microsoft Internet Explorer 3.0.

2. Type the URL in the address box of your Web browser to view Suppliers.asp. For example, if you saved your ASP files in a folder called Test in the wwwroot folder of your Web Server, type:

         http://<servername>/test/Suppliers.asp
   

   Note that the URL depends upon where your files are located on the Web Server and that Internet Explorer 3.0 with the HTML Layout Control is required to view forms exported to ASP.

3. The Suppliers.asp form opens in your Web browser with a text box that displays the HomePage Internet addresses for various companies. Note that some of the hyperlinks point to Office documents and are not valid Internet Addresses. SupplierID 6, Mayumi's, contains a valid Internet address. If you move to that record and then click on the HomePage text box, Internet Explorer automatically moves to that home page.

   Note that if you want to enter a new URL, you must click in a different text box on the form, and then press TAB until you get to the HomePage text box. This avoids following the hyperlink, which is what happens if you click directly in the field. Once the pointer is in the HomePage text box you will be able to type in URLs such as http://www.microsoft.com or www.microsoft.com. You can also explicitly type the number signs (#)that Microsoft Access needs, but if you type only one number sign (#), a message box appears warning you that Microsoft Access hyperlinks require two number signs (#).

REFERENCES

For more information about the parts of a Hyperlink field, search the Help Index for "hyperlinks, returning hyperlink information."

For more information about exporting ASP files, search the help Index for "ASP files," or ask the Microsoft Access 97 Office Assistant.

For information about using hyperlink fields with queries or tables exported to ASP, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q163706
   TITLE     : ASP Files Display Hyperlinks as Text in Web Browser

Keywords          : kbinterop FmsOthr IntAsp
Version           : 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto