ACC97: How to Create Hyperlink Fields in ASP Forms

ID: Q166413

The information in this article applies to:

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 (#).

This article first discusses the causes for this behavior; it then provides an example that works around all three problems.

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 Product Support professionals 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#

However, users who are familiar with entering hyperlink data in Microsoft Access will be accustomed to entering URLs in the format:

   http://www.microsoft.com

   -or-

   www.microsoft.com

Then Microsoft Access parses the data and creates the correct Hyperlink field. In the example in the "An Example That Works Around All Three Problems" section, you create VB Script with your ASP form that checks what the user types and adds the number signs and http:// if necessary to create a valid hyperlink in the Microsoft Access table.

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#

This hyperlink displays the text "Microsoft Home Page" in a Microsoft Access 97 table or form, and it opens http://www.microsoft.com when you click the link. The following example uses only one of the parts of the Hyperlink field:

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

In this case, the Microsoft Access 97 table or form displays http://www.microsoft.com/accessdev by default because there is no specific DisplayText portion of the hyperlink field; it also opens http://www.microsoft.com/accessdev when you click the link.

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

Modify this function so it looks like the function below. The code checks to see if number signs are in the text that is entered in the hyperlink text box. If no number signs are entered, the function concatenates number signs before and after the text. The code also checks to see if http:// is entered, and adds it to the beginning of the URL if necessary.

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%>

Modify the code to match the code below. This code makes the text box in the HTML Layout Control display the DisplayText portion of the hyperlink if there is DisplayText. It will display the Address and Subaddress portion of the hyperlink if there is no DisplayText:

   <%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%>

Save and close the Suppliersalx.asp file.

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

Additional query words:

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

Last Reviewed: May 18, 1999