Microsoft KB Archive/172868

From BetaArchive Wiki



HOWTO: Implementing ComboBox in ASP Exported from Access Forms

Last reviewed: January 7, 1999
Article ID: Q172868



The information in this article applies to:

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

To complete the sample tutorial the user will need the following: - Visual InterDev 1.0.

- Internet Information Server 3.0

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

  working.

- Access 7.0 or Access 97.

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.

- If you have Access 7.0 (Win95) you will not need to

  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
   %>

The code above uses the ServerVariables to obtain both the ServerName and the relative path to the Web project. A string is then built which forms the relative path to the ASP files regardless of where they are placed inside the Web project. URLPathInfo is set to a string with the following syntax: <ServerName>/<WebProjecName>/. This string can then be dynamically inserted via ASP into the two exported files, thereby allowing them to be placed anywhere in the Web project and still be browsed.

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

The imports the text/code inside the .inc file into frmCustomers.asp prior to processing it. The adovbs.inc file declares various constants that are useful in ASP. Change the line 'rs.Open sql, conn, 3, 3' to the following:

   rs.Open sql, conn, adOpenStatic, adLockOptimistic

By using the INCLUDE statement and including the adovbs.inc file the constants can be referred to by name instead of by magic numbers. Delete or comment out the following lines of code:

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

The above lines of code need to be deleted because CustomerID is an AutoNumber primary key and will generate an error if an attempt is made to manually update the field. Microsoft Access, by default, exports the above code, so it will need to be removed or commented out. Inside the frmCustomers.asp file locate the following lines of code:

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

Add the following ASP code below immediately after the On Error Resume Next statement:

   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

When the user selects an item in the combo box, a QueryString is set up with two elements. The first element in the QueryString is called 'nav_btn' and its value is set the name of the control selected by the user. The second element in the QueryString is called 'CustomerID' and its value is set to the bound column of the combo box, which is the CustomerID of the selected item. The returned 'CustomerID' value from the QueryString is then compared record by record to the values in the recordset until a matching record is found or the end of the recordset is reached. 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">

Back at the top of the code, find the closing </HEAD> tag and insert the following code just before it:

   <%
   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
   %>

The above code allows the error checking code to be used below. Locate the 'nav_btn_MoveCancelUpdate' line and insert the following line of code after 'rs.CancelUpdate':

   If Err.Number <> 0 Then HandleError

This allows the error being generated by the rs.CancelUpdate to be displayed. The rs.CancelUpdate will raise an error execpt in one case; when the user clicks AddNew and then decides they don't want to add a new record and immediatley clicks 'Cancel.' In this situation the rs.CancelUpdate cancels the update and restores the recordset to the state prior to the AddNew command. 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")

Add the statement 'tempRS.Requery' immediately after it. 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

Edit the procedure adding the three lines of code shown below:

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

Locked and TabStop are run-time properties that determine if the control can be edited and receive tab focus respectively. ListWidth is a run-time property that determines the width of the drop-down list for the combo box. Depending on the number of columns displayed the width will need to be adjusted accordingly. 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

For each of the Click event procedures modify the URL path being assigned to Window.Location.Href from:

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

To:

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

Locate the <OBJECT> tag for the cboCustomers object and edit the following line of code from:

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

To:

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

Note: The Width parameter controls the width of the columns.

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 kbVisID600 kbVisID100
Technology        : internet
Version           : WINDOWS:1.0,7.0,97; WINDOWS NT:1.0b



================================================================================



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 7, 1999
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.