Microsoft KB Archive/207845

= ACC2000: Reference Data in Lookup Fields Is Ignored During Export to Internet Formats =

Article ID: 207845

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207845



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

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



SYMPTOMS
After you create Lookup fields in tables, the Lookup data is ignored when you export the table to Hypertext Markup Language (HTML), Internet Database Connector (IDC), or Active Server Pages (ASP) format. Data from the bound column for the Lookup field is displayed in the Web browser rather than as the Lookup data that you see when you open the table in Datasheet view in Microsoft Access.



CAUSE
If you exported data to HTML format by using the Export command on the File menu, you did not click to select the Save Formatted check box in the Export Table  As dialog box.

If you exported data to IDC or ASP format, the SQL statement that Microsoft Access generated contained the actual data that was stored in the table's Lookup field. The bound column of a Lookup field's RowSource property is what is actually stored in the table, rather than what you see in Datasheet view of the table in Microsoft Access.



RESOLUTION
If you want to create static HTML files, click Export on the File menu, and then click to select the Save Formatted check box in the Export Table  As dialog box.

If you are exporting to IDC or ASP format, create an AutoLookup query that includes the original table, as well as the table from which the Lookup field obtains its data. Use the corresponding fields from the Lookup table in the query instead of the Lookup fields from the original table. Then export the query to IDC or ASP format.

For example, if you open the sample database Northwind.mdb and view the Orders table in Datasheet view, you see the complete customer name in the Customer column. However, if you export the Orders table to IDC or ASP format, only the CustomerID is exported; CustomerID is the bound column in the Customer Lookup field in the Orders table. If you create an AutoLookup query that contains both the Customers and Orders tables, you can add all the fields from the Orders table to the query design grid except the CustomerID field; add the CompanyName field from the Customers table instead. When you export the query, it presents the same data as the Orders table, including the full customer name.



Steps to Reproduce Behavior
To reproduce this behavior in exporting a static HTML file, which is the same if you export to IDC or ASP file formats, follow these steps:

 Start Microsoft Access and open the sample database Northwind.mdb. Open the Products table in Design view. Select the SupplierID field, and then click the Lookup tab in the Field Properties section of the Design window.

Information on this tab indicates that SupplierID is a Lookup field. Close the Products table, and leave it selected in the Database window. On the File menu, click Export.</li> In the Export Table 'Products' As dialog box, select a folder on your Microsoft Internet Information Server (IIS) where you have Read permission, or save the file locally and copy the HTML file to the your IIS Web server folder after the export is complete.</li> In the Export Table 'Products' As dialog box, select HTML Documents (*.html; *.htm) in the Save As Type box, and then type Products.html in the File Name box. Verify that the Save Formatted check box is not selected, and then click Save.</li> Start your Web browser, and type the Uniform Resource Locator (URL) that you want in the Address box to view the Products.html file. Notice that the URL depends upon where your files are located on the Web server. For example, if you saved Products.html in the Wwwroot folder of your Web server, type:

http:// /products.html

When the Products.html file opens, notice that the SupplierID column shows the actual SupplierID code and not the Company Name.</li></ol>

<div class="references_section">