ACC97: Form Control Format Property Ignored when Exported to ASP

ID: Q163034

The information in this article applies to:

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 Product Support professionals do not support customization of any HTML, HTX, IDC, or ASP files.

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

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

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.

Select frmFormatTest in the Database window.

On the File menu, click Save As/Export.

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.

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.

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:

Q159682 "Data Source Name Not Found" Err Msg Opening Web Page

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/ as your Server URL. Click OK. The ASP output creates the files frmFormatTest.asp and frmFormatTestalx.asp.

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.

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:

Q162975 Permissions Necessary to View HTML, IDC, and ASP Files

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

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.

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."

Additional query words: iis pws peer web services personal server

Keywords : kbinterop IntAsp
Version : WINDOWS:97; winnt:3.0
Platform : WINDOWS winnt
Issue type : kbprb