Microsoft KB Archive/172868

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