HOWTO: Implementing ComboBox in ASP Exported from Access Forms

• Microsoft Visual InterDev, version 1.0
• Microsoft Access versions 7.0, 97
• Microsoft Active Server Pages, version 1.0b

SUMMARY

This article describes how to use a combo box in an Active Server Pages (ASP) page to return detailed information about the item selected in the combo box, and then use the detailed information to populate a form.

In addition, the following topics are covered:

• How to create an Access Form based on the Customers table in the Adventure Works database (AdvWorks.mdb) and add a combo box that allows the user to obtain detailed information about the selected item.
• How to create a Form based on the Customers table in the AdvWorks.mdb database.
• How to export the Form to Active Server Pages and add the exported Active Server Pages files to a Visual InterDev Web project.
• How to modify the code to implement the combo box in the Active Server Pages.

- Visual InterDev 1.0.

- Internet Information Server 3.0

- Active Server Pages 1.0b with Adventure Works samples installed and

  working.

MORE INFORMATION

Additional Notes

- The files adovbs.inc and SessionVars.inc have been

  renamed to adovbs.txt and SessionVars.txt to allow
  them to be sent via email. These files will need to
  be renamed back to the .inc extension to work inside
  the Web project. They needed to be renamed due to the
  fact that .inc files appear to be lost in the email.

  convert the database to Access 97 in step one below.
  This is done to allow editing of the database in
  Access 97.  To export the Form as an ASP file you
  must open the database in Access 97.

Step One: Convert the AdvWorks.mdb database to Access 97

1) Open Access 97 and from the Tools > Database Utilities menue select Convert Database.

2) Locate the AdvWorks.mdb file located in the AdvWorks directory ([drive]:\InetPub\ASPSamp\AdvWorks), select it and click Convert.

3) Save the AdvWorks.mdb with the same name but to a temporary location.

4) After saving the converted database to the temporary location, copy it back into the AdvWorks directory replacing the original AdvWorks.mdb file with the new Access 97 AdvWorks.mdb file.

Step Two: Create the frmCustomers Form

1) Open Access, open the AdvWorks database ([drive]:\InetPub \ ASPSamp \ AdvWorks)and select the Forms tab. Click the New button. Select Form Wizard and in the drop-down box in the bottom of the "New Form" dialog, choose the Customers table from the listbox. Click OK.

2) Click the >> button to select all the fields and then click Next. Accept the defaults by clicking Next until you reach the dialog box asking you to name the Form. Name the Form frmCustomers to distinguish it from the existing Customers Form in the AdvWorks database. Click "Finish".

3) When the form appears, select View / Design from the menu. Move and resize the controls on the Form so they are clearly visible. Allow approxiamately 1/4 of an inch between the controls and the left hand side of the Form and the top of the Form so the controls will display correctly on the HTML page generated by the exported ASP file.

5) Drag the Detail bar down until there is about 1/2 of an inch of Form Header available. Make sure View / Toolbox is visible, and drop a ComboBox control onto the Form Header. The Combo Box Wizard dialog box will appear. Select the 'Find a record on my form based on the value I selected in my combo box' option button and click Next.

6) Click the > button and add the following fields: CustomerID and CompanyName and click next. On the next dialog box leave the 'Hide key column' checked and click Next.

7) Enter 'Select Customer' for the combo box lable and click Finish.

8) Resize the combo box so it will display correctly in the ASP page as described above.

9) Change the name property of the combo box label to lblSelectCustomers and change the name property of the combo box to cboCustomers. Rename each of the controls accordingly, renaming the lables with the lbl prefix and text boxes with the txt prefix (i.e., lblCustomerID and txtCustomerID).

10) Select Form View from the View menu and confirm that when a Customer is selected in the combo box that their detail information is shown below in the Detail section of the form. Close and save the changes made to frmCustomers.

11) Close the Form and say "yes" to saving changes.

Step Three: Export the frmCustomers form to ASP

1) From within Access 97 select the Forms tab and select the frmCustomers form.

2) From the File menu select Save As/Export.... Accept the default 'To an External File or Database' and click OK.

3) The 'Save Form frmCustomers in' dialog box will appear. In the 'Save as type' listbox select 'Microsoft Active Server Pages (*.asp)' and then browse to the wwwroot directory for the Web server (\InetPub\wwwroot for NT Server 4.0 and Internet Information Server and \WebShare\wwwroot for Windows 95 and Personal Web Server) for the "Save in:" box. You want to save/export the ASP files to the wwwroot directory of the respective Web server.

4) After browsing to the wwwroot directory click the Export button. The Microsoft Active Server Pages Output Options dialog will appear. In the Data Source Name textbox enter the name of the System DSN for Adventure Works (i.e. AdvWorks) and click OK. Leave everything else blank. The files frmCustomers.asp and frmCustomersalx.asp will be exported to the wwwroot directory.

5) To test the exported ASP pages Execute permissions will need to be set on the wwwroot directory from within the Internet Service Manager. Open the Internet Service Manager and double click on the www service to open its Properties page. Select the Directories tab and select the wwwroot <home> diretory. Click the Edit Properties button and check the Execute checkbox under Access. Start and stop the www service to assure that the changes have taken effect. Once this has been done attempt to browse the frmCustomers.asp by entering the following URL in Internet Explorer's Address textbox: http://<ServerName>/frmCustomers.asp.

At this point you should be able to view the form, but the combo box will not function until some modifications are mde to the code in the ASP files.

Step Four: Add the exported ASP files to a Web project

1) Create a new Web project is Visual Interdev called Customers.

2) Add the files adovbs.inc (use Start/Find to locate the file).

3)Add the files frmCustomers.asp and frmCustomersalx.asp to the workspace.

4)Add a text file to the Web project named SessionVars and rename its file extension from .txt to .inc (i.e. SessionVars.inc). Open the SessionVars.inc file in Notepad and copy the following lines into it:

   <%
   If Session("URLPathInfo") = "" Then

     Dim i
     Dim strTemp
     Dim nTrim
     Dim ServerName
     Dim PathInfo
     Dim FullPathInfo

     ServerName   = Request.ServerVariables("SERVER_NAME")
     PathInfo     = Request.ServerVariables("PATH_INFO")
     FullPathInfo = ServerName & PathInfo

     For i = 1 To Len(PathInfo)
       strTemp = Right(PathInfo, i)
       If ( Left(strTemp, 1) = "/" ) Then
         nTrim = i - 1
    Session("URLPathInfo") = Left(FullPathInfo, _
            (Len(FullPathInfo) - nTrim))
    Exit For
       End If
     Next

   End IF
   %>

Step Five: Modify frmCustomers.asp file

Open the frmCustomers.asp file and add the following line of code after the opening <BODY> tag:

   <!--#INCLUDE FILE="adovbs.inc"-->

   <!--#INCLUDE FILE="SessionVars.inc"-->

Change the line 'rs.Open sql, conn, 3, 3' to the following:

   rs.Open sql, conn, adOpenStatic, adLockOptimistic

Delete or comment out the following lines of code:

   If cstr(Request.QueryString("CustomerID")) <> "" Then
     rs.Fields("CustomerID").Value = Request.QueryString("CustomerID")
   End If

Inside the frmCustomers.asp file locate the following lines of code:

   tempVar = Request.QueryString("nav_btn")
   On Error Resume Next

   If cstr(tempVar) = "nav_cbo_Customers" Then
     Dim strRecordsetCustomerID
     Dim strQueryStringCustomerID
     strQueryStringCustomerID = cstr(Request.QueryString("Customer_ID"))
     rs.MoveFirst
     For i = 1 To rs.RecordCount
       strRecordsetCustomerID   = cstr(rs("CustomerID"))
       If strRecordsetCustomerID <> strQueryStringCustomerID Then
         rs.MoveNext
    If rs.EOF Then
      rs.MovePrevious
      If rs.BOF Then
        rs.AddNew
      End If
    End If
       Else
         Exit For
       End If
     Next
   End If

Locate the <OBJECT> tag at the bottom of the frmCustomers.asp file and replace the VALUE attribute with the following code:

   VALUE="http://<%= Session("URLPathInfo") %>frmCustomersalx.asp">

   <%
   Sub HandleError()
     Response.Write("Error Source: " & Err.Source & "<br>")
     Response.Write("Error Number: " & Err.Number & "<br>")
     Response.Write("Error Description: " & Err.Description & "<br>")
     Err.Clear
   End Sub
   %>

   If Err.Number <> 0 Then HandleError

At other times when the statement is executed there have been no changes to the server side recordset. The rs.Resync requeries the underlying recordset and returns it to the client displaying the first record, which makes it appear to the client that the changes were cancelled. In reality no changes were ever made to the underlying recordset.

Step Six: Modify frmCustomersalx.asp file

Open the frmCustomersalx.asp file and locate the following line:

   Set tempRS = Session("RS_frmCustomers_cboCustomers")

This requeries the recordset so added records are visible in the combo box.

Locate the frmCustomersalx_OnLoad procedure:

   Sub frmCustomersalx_OnLoad()
      cboCustomers.list = cboCustomers_tempList
   End Sub

   Sub frmCustomersalx_OnLoad()
     txtCustomerID.Locked   = True
     txtCustomerID.TabStop  = False
     cboCustomers.ListWidth = 150
     cboCustomers.list = cboCustomers_tempList
   End Sub

Locate the nav_btn_MoveFirstRecord_Click event procedure, and just above it, copy the cboCustomers_Click event procudure listed here:

   Sub cboCustomers_Click()
    Call AddCtrlToList("cboCustomers", "Customer_ID")
    Window.Location.Href = "http://" & _
          "<%= Session("URLPathInfo") %>" & _
   frmCustomers.asp?nav_btn=nav_cbo_Customers&"& _
          GetCtrlQueryString()
   End Sub

   ...Href = "frmCustomers.asp?...

   ...Href = "http://<%= Session("URLPathInfo") %> "frmCustomers.asp?...

   <PARAM NAME="Width" VALUE="0;n;n;">

   <PARAM NAME="Width" VALUE="0;2500;">

REFERENCES

http://microsoft.com/accessdev/accfeat/mar97feat.htm#asp

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

   http://support.microsoft.com/support/vinterdev/

Keywords          : VIASP VIMisc
Technology        : internet
Version           : WINDOWS:1.0,7.0,97; WINDOWS NT:1.0b
Platform          : NT WINDOWS
Issue type        : kbhowto