ACC97: Form Control Format Property Ignored when Exported to ASP

• Microsoft Access 97
• Microsoft Internet Information Server version 3.0

SYMPTOMS

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

When you browse to an ASP file that was exported with Microsoft Access 97, the Format property of fields in Microsoft Access forms is not preserved. This article discusses the workaround for this behavior.

CAUSE

When forms are exported to ASP format, Microsoft Access 97 generates a SQL statement for the ASP file that retrieves the data from the underlying tables. This functionality is not designed to preserve Format properties of fields or form controls. Numbers will be displayed in General Number format, Dates will be displayed in General Date format, Times will be displayed in Long Time format, and Yes/No fields will be displayed in True/False format.

RESOLUTION

In order to preserve the Format properties of fields when exporting tables and queries to ASP format, one workaround is to export a Microsoft Access query that uses the Format function around the fields that you want formatted. However, this workaround should not be used for forms. When exporting forms to ASP format, the fields that use the Format function will not be updateable and ASP forms are designed for use with updateable recordsets.

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

  1. Open the sample database Northwind.mdb in Microsoft Access 97.

  2. Use the AutoForm: Columnar Wizard to create a new form based on the
     Products table.

  3. Switch to Design view of the form, delete the ProductID label and text
     box because it is an AutoNumber field that you do not want users
     to enter data in. Save the form and call it frmFormatTest. Close the
     form.

  4. Select frmFormatTest in the Database window.

  5. On the File menu, click Save As/Export.

  6. In the Save As dialog box, click to select "To an External File or
     Database," and click OK. Note that the "Save Form 'frmFormatTest' In"
     dialog box appears.

  7. In the Save As Type box, select Microsoft Active Server Pages (*.asp)
     and type frmFormatTest.asp in the File Name box. Note the folder where
     the files will be exported to. Click Export. Note that the Microsoft
     Active Server Pages Output Options dialog box appears.

  8. In the Data Source Name box, enter the name of a System DSN that
     points to the sample database Northwind.mdb.

     For more information about how to define a system DSN, search the Help
     index for "ODBC, setting up data sources," and see the following
     article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q159682
        TITLE     : ACC97: "Data Source Name Not Found" Err Msg Opening Web
                    Page

  9. In the Server URL box, enter the URL that points to the Web Server
     location where your ASP files will be stored. For example, if you
     store the ASP files in the \ASPsamp folder on the \\PubTest server,
     type "http://pubtest/aspsamp/" (without the quotation marks) as your
     Server URL. Click OK. The ASP output creates the files
     frmFormatTest.asp and frmFormatTestalx.asp.

 10. Use Notepad or another text editor to open the frmFormatTestalx.asp
     file. Towards the middle of the file, you will find the following code
     which sets the value of the UnitPrice object equal to the value of the
     current UnitPrice field in the recordset.

      <OBJECT ID="UnitPrice"
      CLASSID="CLSID:8BD21D10-EC42-11CE-9E0D-00AA006002F3"
      STYLE="TOP:225;LEFT:168;WIDTH:231;HEIGHT:25;TABINDEX:5;ZINDEX:8;">
      <%If Not IsNull(rs.Fields("UnitPrice").Value) Then%>
      <PARAM NAME="Value"
      VALUE="<%=Server.HTMLEncode(rs.Fields("UnitPrice").Value)%>">
      <%End If%>
      <PARAM NAME="BackStyle" VALUE="1">
      <PARAM NAME="BackColor" VALUE="2147483653">
      <PARAM NAME="BorderStyle" VALUE="1">
      <PARAM NAME="BorderColor" VALUE="0">
      <PARAM NAME="ForeColor" VALUE="2147483656">
      <PARAM NAME="FontHeight" VALUE="160">
      <PARAM NAME="Font" VALUE="MS Sans Serif">
      <PARAM NAME="FontName" VALUE="MS Sans Serif">
      <PARAM NAME="Size" VALUE="6006;650">
      <PARAM NAME="SpecialEffect" VALUE="2">
      <PARAM NAME="VariousPropertyBits" VALUE="2894088219">
      </OBJECT>

      To format the value as Currency, you should use the VB Script
      formatcurrency() function by modifying the <PARAM NAME="Value"...>
      so that it matches the following:

      <PARAM NAME="Value"
      VALUE="<%=Server.HTMLEncode(formatcurrency(rs.Fields("UnitPrice")
      .Value))%>">

     NOTE: VB Script has the FormatCurrency, FormatNumber, FormatDatetime,
     and FormatPercent functions, but there is no Format function with
     which you can provide custom formats. Please refer to your VB Script
     Language Reference available in the ASP online documentation for more
     information on VB Script functions.

     Save and then close frmFormatTestalx.asp.

 11. Copy frmFormatTest.asp and frmFormatTestalx.asp to a folder on your
     Web Server computer where you have Execute permission. For more
     information about configuring Microsoft Internet Information Server
     (IIS) permissions, please refer to the IIS Help Index, and see the
     following article in the Microsoft Knowledge Base:

        ARTICLE-ID: Q162975
        TITLE     : ACC97: Permissions Necessary to View HTML, IDC, and ASP
                    Files

 12. Start Microsoft Internet Explorer 3.0, or another Web browser program.

 13. Type the Uniform Resource Locator (URL) in the address box of your Web
     browser to view frmFormatTest.ASP. For example, if you saved your ASP
     file in a folder called Test in the wwwroot folder of your Web Server,
     type:

       http://<servername>/test/frmFormatTest.ASP

     Note that the URL depends upon where your files are located on the Web
     Server.

 14. Note that the UnitPrice field has the Currency format applied.

REFERENCES

For more information about exporting forms to ASP, search the Help Index for "ASP files," and then "Export a form to dynamic HTML format."