Microsoft KB Archive/323202

= HOW TO: Change the Values in a Hyperlink Field from an HTTP Address to a MAILTO Address in Microsoft Access 2000 =

Article ID: 323202

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q323202



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK

 * SUMMARY
 * Method 1 Using an Update Query to Change the HTTP Address to a MAILTO Address
 * Method 2 Using Visual Basic for Applications Code to Change the HTTP Address to a MAILTO Address
 * Storing a Properly Formatted E-mail Address Automatically



SUMMARY
If you type an e-mail address into a Hyperlink field, the address defaults to an HTTP address for the link. Therefore, if you type someone@example.com, what is actually stored in the database is someone@example.com#http://someone@example.com#

This is expected behavior because a Hyperlink field is made up of two parts: the displayed text, and the actual address. Because the field type is Hyperlink, Access assumes that the text that is entered will be a Web site and not an e-mail address.

To change this so that you can click the hyperlink and send e-mail, you can use either Method 1 or Method 2 mentioned earlier. =Both methods update existing records only.

To automatically format data that is entered into the database, you can use the &quot;How to Store a Properly Formatted E-mail Address Automatically&quot; that is mentioned earlier. back to the top

=== Method 1

Using an Update Query to Change the HTTP Address to a MAILTO Address ===

 Create a query in Design View that is based on the table that has the e-mail addresses. Include just the e-mail field from the table in the query design grid. On the Query menu, click Update Query. This adds the Update To row to the query design grid. In the Update To row, type the following expression. Replace [Mail Field] with the name of your e-mail field:

IIf(Left([Mail Field],8)<>&quot;#mailto:&quot;,&quot;#mailto:&quot; & Left([Mail Field],InStr(1,[Mail Field],&quot;#&quot;)-1),[Mail Field])

 On the File menu, click Save. Save the query as qryUpdateHyperlink . On the Query menu, click Run. Note that you receive a message that indicates how many rows (records in the table) will be updated. Click Yes to update your records.</ol>

back to the top

=== Method 2

Using Visual Basic for Applications Code to Change the HTTP Address to a MAILTO Address ===

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

<ol> Create a new module and name it basUpdateHyperlinkToMail .</li>  Type or paste the following code in the module: Sub UpdateHyperlinkToMail On Error GoTo Err_UpdateHyperlinkToMail Dim DB As DAO.Database Dim RS As DAO.Recordset Dim strMail As String Set DB = CurrentDb Set RS = DB.OpenRecordset(&quot;E-mail Table&quot;, dbOpenDynaset) With RS       If .RecordCount > 0 Then .MoveFirst Do Until .EOF If Not IsNull(![Hyperlink Field]) Then If Left(![Hyperlink Field], 8) <> &quot;#mailto:&quot; Then strMail = &quot;#mailto:&quot; & Left(![Hyperlink Field], _                   InStr(1, ![Hyperlink Field], &quot;#&quot;) - 1) & &quot;#&quot; .Edit ![Hyperlink Field] = strMail .Update End If           End If            .MoveNext Loop End With MsgBox &quot;Update complete!&quot;

Exit_UpdateHyperlinkToMail: RS.Close Set RS = Nothing Set DB = Nothing Exit Sub Err_UpdateHyperlinkToMail: MsgBox &quot;Error: &quot; & Err.Number & &quot; - &quot; & Err.Description Resume Exit_UpdateHyperlinkToMail End Sub </li> Replace the &quot;E-Mail Table&quot; text in the code with the correct name of your table, and replace all instances of the text [Hyperlink Field] with the name of your field that has the e-mail addresses.</li> Click the pointer somewhere in this procedure, and then press F5 to run the code.</li></ol>

back to the top

Storing a Properly Formatted E-mail Address Automatically
In the future, if you want to store the e-mail addresses that you enter as an e-mail link instead of a hyperlink to an Internet page, follow these steps. This process requires that the data is entered through a form. <ol> Open your data entry form in Design View.</li> Right-click the field that you use to enter the e-mail address, and then click Properties.</li> In the Properties dialog box, click the Event tab.</li> Click in the AfterUpdate property box, click the arrow, and then click [Event Procedure] in the list.</li> Click the Build (...) button to start the Visual Basic Editor.</li>  What you see in the Visual Basic Editor depends on the name of your e-mail field. For example, if the name of your e-mail field is Text0, you see the following: Private Sub Text0_AfterUpdate

End Sub </li>  Type or paste the two following lines of code between the two lines of code shown in step 6. Make sure to replace [Text0] with the name of your field. If Len(Me![Text0]) = 0 Then Exit Sub Me![Text0] = &quot;#mailto:&quot; & Left(Me![Text0], InStr(1, Me![Text0], &quot;#&quot;) - 1) & &quot;#&quot; Whenever an e-mail address is now entered in this field, it is automatically formatted and stored as an e-mail address as soon as focus has moved away from this field. </li></ol>

back to the top

Additional query words: inf acc2000 turn into replace

Keywords: kbhowto kbhowtomaster KB323202

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.