Knowledge Base

ACC97: Dynamic Combo Box to Filter Access Data in IDC Format

Article ID: 165359

Article Last Modified on 1/19/2007

APPLIES TO

This article was previously published under Q165359
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

You can filter Microsoft Access data in HTX/IDC format by specifying criteria from a Web browser. One way that you can do this is to export a parameter query from Microsoft Access to HTX/IDC format. An HTML file is created, which enables you to type criteria into a text box in order to filter the records returned to the browser. However, you may prefer to select criteria from a combo box based on a table or query in the database. To implement a combo box that you can use from a browser, you must create an additional IDC file and a corresponding HTX file.

MORE INFORMATION

The following example uses the Northwind sample database. It assumes that you have Microsoft Personal Web Server or Microsoft Internet Information Server installed on your Web server computer.

NOTE: This article contains information about writing and editing IDC and HTX files. This information is provided as is. Microsoft Access Technical Support professionals do not support customizing your IDC or HTX files.
  1. On your Web server, create a System DSN based on the Northwind sample database, Northwind.mdb, and name the data source Nwind97.
  2. Start Microsoft Access and open the Northwind sample database.
  3. On the File menu, click Save As HTML. When the "Publish to the Web Wizard" appears, click Next on the opening screen.
  4. On the "What do you want to publish?" screen, click the Orders table, and then click Next.
  5. Click Next on the screen that prompts you to select a default template.
  6. On the "What default format type do you want to create?" screen, click Dynamic HTX/IDC, and then click Next.
  7. On the "What are, or will be, the settings for the Internet database?" screen, enter Nwind97 in the Data Source Name box, and then click Next.
  8. On the "Where do you want to publish to?" screen, select a folder on your Web server where you have Execute permission, for example InetPub\Scripts or Webshare\Scripts, and then click Finish. The Publish to the Web Wizard creates two files, Orders_1.htx and Orders_1.idc.
  9. Start a text editor, such as Notepad, and type the following lines: 
            datasource: Nwind97
        template: Custlist.htx
        SQLStatement:
        +SELECT customers.customerid, customers.companyname
        +FROM customers

    Save this file in the same folder on your Web server where you saved Orders_1.idc, and name this new file Custlist.idc.
  10. Open a new file in your text editor and type the following lines.

    NOTE: Substitute the relative path to the location on your Web server where you saved Orders_1.idc in the line <FORM METHOD = "POST" ACTION = "/scripts/Orders_1.idc"> 
          <HTML>
      <TITLE>Customers</TITLE>
      <BODY>
      <FORM METHOD = "POST" ACTION = "/scripts/Orders_1.idc">
      Select the customer whose orders you'd like to see:<BR>

      <SELECT NAME = "customerid">
      <%BeginDetail%>
      <OPTION VALUE = <%customerid%>> <%companyname%>
      <%EndDetail%>
      </SELECT>
      <P>
      <INPUT TYPE = "Submit" VALUE = "Submit">
      </BODY>
      </HTML>

    Note that the <%BeginDetail%> and <%EndDetail%> tags determine where the records returned from the database will appear; column names are enclosed in <%%> to indicate where IDC will insert the dynamic data.

    Save this file in the same folder on your Web server where you saved Orders_1.htx, and name this new file Custlist.htx.
  11. Open Orders_1.idc in your text editor, and change the following line: 
            SELECT * FROM [Orders]

    to 
            SELECT * FROM [Orders] WHERE customerid = '%customerid%'

    Save the file and close it.
  12. Start Microsoft Internet Explorer 3.0 or another Web browser program and type the Universal Resource Locator (URL) for Custlist.idc in the address box. For example:

    http://<servername>/scripts/custlist.idc
  13. Select a customer name in the combo box and click the Submit button. The only orders that display will be those for the customer you selected.

REFERENCES

For more information about creating a System DSN, search the Help Index for "ODBC, Help files," and click the "Display the ODBC Help file" link. Then search the ODBC Help Index for "system DSNs."

For more information about exporting a table to HTX/IDC, search the Help Index for "IDC files," or ask the Microsoft Access 97 Office Assistant.

Keywords: kbhowto kbinterop KB165359


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.