ACC97: How to Create a Data Entry Form in ASP Format

MORE INFORMATION

When you export to ASP format a Microsoft Access 97 form that has its DataEntry property set to Yes, the ASP file displays the underlying records when you open it in a Web browser. That is because, when Microsoft Access 97 exports a form to ASP, it uses the form's RecordSource property to generate a SQL Statement for the ASP file. It also automatically generates the VBScript for all of the buttons on the form that enable you to move between records, add new records, and delete existing records. The export to ASP feature in Microsoft Access 97 was not designed to create a data entry form that does not display records.

There are two different techniques you can use to create a data entry form with ASP files:

• You can export a parameter query to ASP in order to generate an HTML form automatically, and then modify the ASP so that it executes an INSERT SQL Statement rather than a SELECT.
• You can create a Microsoft Access 97 form based on a record source that does not return any records, and then modify the ASP file so there is only one navigation button, the Add New button.

Method 1 - Modifying a Parameter Query Exported to ASP Format

Following is an example of how to create an HTML Insert Form that uses ASP files to insert records into a Microsoft Access 97 database.

Creating the ASP Files in Microsoft Access:

1. Start Microsoft Access 97 and open the sample database Northwind.mdb.
2. Create a new query called EnterShipper based on the Shippers table:

          Query: EnterShipper
          -------------------
          Type: Select Query
   
          Field: CompanyName
            Table: Shippers
   					

3. On the Query menu, click Parameters.
4. Type the following in the Query Parameters dialog box, and then click OK:

          Parameter          Data Type
          ----------------------------
          [EnterName]        Text
   					

5. Save the EnterShipper query and close it.
6. Select the EnterShipper query in the Database window, and then click Save As/Export on the File menu.
7. In the Save As dialog box, click "To an External File or Database," and then click OK.
8. In the "Save Query 'EnterShipper' In" dialog box, select Microsoft Active Server Pages (*.asp) in the Save As Type box, and type EnterShipper.asp in the File Name box. Note the folder where the exported files will be stored, and then click Export.
9. In the Microsoft Active Server Pages Output Options dialog box, type the name of a System DSN on your Web server that points to the Northwind sample database.
   
   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:

   159682 ACC97: "Data Source Name Not Found" Err Msg Opening Web Page

10. In the Server URL box, type the uniform resource locator (URL) of the Web server location where your ASP files will be stored. For example, if you will store the ASP files in the \ASPsamp folder on the \\PubTest server, type http://pubtest/aspsamp/ in the Server URL box. Click OK.
11. Click OK in the Enter Parameter Value dialog box that appears. The ASP output creates two files: EnterShipper.HTML and EnterShipper.asp.
12. Copy EnterShipper.HTML and EnterShipper.asp to a folder on your Web server computer where you have both Read and Execute permission. Read permission is necessary to browse the HTML file, and Execute permission is necessary to run the ASP file. This must be the same folder indicated by the Server URL that you entered in step 10.
    
    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:

    162975 ACC97: Permissions Necessary to View HTML, IDC, and ASP Files

Customizing the ASP file to Permit Inserting Records

1. Use Notepad or another text editor to open the EnterShipper.ASP file.
2. You need to modify the ASP script so that it will use the value passed from the HTML form and insert it into the Shippers table of the Northwind sample database. Near the top of the ASP file, you will see some script that looks as follows:

         <%
             sql = "SELECT Shippers.CompanyName  FROM Shippers   "
             If cstr(Param) <> "" And cstr(Data) <> "" Then
                 sql = sql & " WHERE [" & cstr(Param) & "] = " & cstr(Data)
             End If
             Set rs = Server.CreateObject("ADODB.Recordset")
             rs.Open sql, conn, 3, 3
         %>
   						

   Change this part of the script to the following:

         <%
             sql = "INSERT into Shippers (CompanyName) values ('" &_
             Request.QueryString("[EnterName]") & "');"
             conn.execute sql
             sql = "SELECT Shippers.CompanyName FROM Shippers"
             If cstr(Param) <> "" And cstr(Data) <> "" Then
                 sql = sql & " WHERE [" & cstr(Param) & "] = " & cstr(Data)
             End If
             Set rs = Server.CreateObject("ADODB.Recordset")
             rs.Open sql, conn, 3, 3
         %>
   					

3. Save the EnterShipper.ASP file and close it.
4. Start Microsoft Internet Explorer 3.0, or another Web browser program.
5. Type the URL in the address box of your Web browser to view EnterShipper.HTML. For example, if you saved your files in a subfolder called ASPsamp in the \InetPub folder on your Web server, type:

   http://<servername>/ASPsamp/EnterShipper.HTML

   Note that the URL depends upon the relative path to your files on the Web server.
6. The EnterShipper.HTML form opens in your Web browser and displays an [EnterName] box and a Run Query button. Type a name into the box and then click the Run Query button. A new record with the name you typed is inserted into the Shippers table in the Northwind sample database. Note that the ASP file requeries the database and returns an HTML table that displays the records in the Shippers table.

Method 2 - Modifying an Exported Form That Returns No Records

Following is an example of how to create a Microsoft Access 97 form with a record source that returns no records. Then you export the form to ASP format and modify it to create a data entry form.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Creating the ASP Files in Microsoft Access:

1. Start Microsoft Access 97 and open the sample database Northwind.mdb.
2. Open the Customers form in Design view.
3. Set the form's RecordSource property to an SQL statement that will not return any records:

   SELECT * from Customers WHERE False

4. Save the Customers form and close it.
5. Select the Customers form in the Database window, and then click Save As/Export on the File menu.
6. In the Save As dialog box, click "To an External File or Database," and then click OK.
7. In the "Save Form 'Customers' In" dialog box, select Microsoft Active Server Pages (*.asp) in the Save As Type box, and type Customers.asp in the File Name box. Note the folder where the exported files will be stored, and then click Export.
8. In the Microsoft Active Server Pages Output Options dialog box, type the name of a System DSN on your Web server that points to the Northwind sample database.
   
   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:

   159682 ACC97: "Data Source Name Not Found" Err Msg Opening Web Page

9. In the Server URL box, type the uniform resource locator (URL) of the Web server location where your ASP files will be stored. For example, if you will store the ASP files in the \ASPsamp folder on the \\PubTest server, type http://pubtest/aspsamp/ in the Server URL box. Click OK.
10. Copy the two ASP files that are created, Customers.asp and Customersalx.asp, to a folder on your Web server computer where you have Execute permission. This must be the same folder indicated by the Server URL that you entered in step 9.

Customizing the ASP file to Create a Data Entry Form

1. Use Notepad or another text editor to open the Customersalx.asp file.
2. You must modify portions of the ASP script so that the only navigation button that displays is the Add New button. At the bottom of the ASP file, you will see a series of <OBJECT> tags with the following Object IDs:

   
   nav_btn_MoveCancelUpdate, nav_btn_MoveDeleteRecord,
   nav_btn_MoveCommitRecord, nav_btn_MoveLastRecord,
   nav_btn_MoveNextRecord, nav_btn_MovePrevRecord, and
   nav_btn_MoveFirstRecord.
   

   Delete each of these <OBJECT> tags, making sure that you delete everything between the beginning <OBJECT> and the ending </OBJECT> tag.
   
   NOTE: Make sure you do not delete the <OBJECT> tag with the nav_btn_MoveAddRecord Object ID.
3. Near the top of the ASP file you will see the following procedure:

          Sub UpdateRefreshBtn()
             nav_btn_MoveCancelUpdate.Caption = "Cancel"
          End Sub
   						

   Delete the line between the Sub and the End Sub lines so that it looks as follows:

          Sub UpdateRefreshBtn()
          End Sub
   					

4. Save the Customersalx.asp file and close it.
5. Start Microsoft Internet Explorer 3.0, or another Web browser program.
6. Type the URL in the address box of your Web browser to view Customers.asp. For example, if you saved your files in a subfolder called ASPsamp in the \InetPub folder on your Web server, type:

   http://<servername>/ASPsamp/EnterShipper.HTML

   Note that the URL depends upon the relative path to your files on the Web server.
7. The Customers.asp form opens in your Web browser. Type a unique five letter Customer ID into the customer ID box. Type a company name into the company name box. Click the Add New button. A new record with the name you typed is inserted into the Customers table in the Northwind sample database.