Knowledge Base

ACC2000: You Are Unable to Print a Data Access Page That Is Based on a Parameter Query

Article ID: 253976

Article Last Modified on 8/17/2007

APPLIES TO

Microsoft Access 2000 Standard Edition

This article was previously published under Q253976

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you try to print a data access page that is based on a parameter query in Microsoft Internet Explorer 5.0, the page is not printed.

CAUSE

When you print from Internet Explorer, a new document object is created for the print job. When the Microsoft Office Web Components are reloaded during the print job, they detect that parameter values are required and attempt to display a dialog box to get the values. In versions of Internet Explorer earlier than 5.5, Internet Explorer cannot provide the correct window context to display the dialog box at this point. Therefore, the page is not printed and you do not receive an error message. Additional attempts to print the page do not work until you quit Internet Explorer, and then restart it.

RESOLUTION

To resolve this problem, upgrade to Microsoft Internet Explorer 5.5 or later. To obtain the latest version of Internet Explorer, visit the following Microsoft Web site:

http://www.microsoft.com/windows/products/winfamily/ie/default.mspx

To work around this problem without upgrading, you can use one of the following methods:

Method 1: Use Script from the Page to Prompt for the Parameter Values
Method 2: Use Script to Create and Read Cookies That Are Based on Values That Are Entered on a Separate Page

Method 1: Use Script from the Page to Prompt for the Parameter Values

Create a data access page that is based on the "Employees Sales by Country" query in the Northwind sample database. Save it as EmpSalesByCountry.htm.
In Design view for the page, click Macro on the Tools menu, and then click Microsoft Script Editor.

Add the following script immediately before the closing HEAD tag for the page:

<SCRIPT language=vbscript>
<!--
Document.MSODSC.RecordsetDefs(0).ParameterValues.Add "[Beginning Date]", _
inputbox("Enter Beginning Date","[Beginning Date]",#1/1/1996#)

Document.MSODSC.RecordsetDefs(0).ParameterValues.Add "[Ending Date]", _
inputbox("Enter Ending date","[Ending Date]",#1/1/1997#)
-->
</SCRIPT>

NOTE: For pages that are based on SQL Server stored procedures, use the following syntax:

<SCRIPT language=vbscript>
<!--
Document.MSODSC.RecordsetDefs(0).ParameterValues.Add "@Beginning_Date", _ 
inputbox("Enter Beginning Date","Beginning Date",#1/1/1996#)
 
Document.MSODSC.RecordsetDefs(0).ParameterValues.Add "@Ending_Date", _ 
inputbox("Enter Ending Date","Ending Date",#1/1/1997#)
-->
</SCRIPT>

Method 2: Use Script to Create and Read Cookies That Are Based on Values That Are Entered on a Separate Page

Create a file that contains the following HTML, and then save it as dapOpen.htm:

<HTML>
  <HEAD>
  <TITLE>Enter Date Range</TITLE>
  <SCRIPT language="VBScript">
   Sub writeCookie(strVariableName, varVariableValue)
      Document.Cookie = strVariableName & "=" & varVariableValue
   End Sub

   Sub openDAP()
      Dim varBegin 
      Dim varEnd 
              
      varBegin = document.all.txtBegin.value
      varEnd = document.all.txtEnd.value

      writeCookie "pBegin", varBegin
      writeCookie "pEnd", varEnd
      'change "servername" to the name of your server.
      window.navigate("http://servername/EmpSalesByCountry.htm")

   End Sub
  </SCRIPT>
  </HEAD>
  <BODY>
   <FORM id="form1">
     <TABLE>
       <TR>
         <TD>Beginning Date:</TD>
         <TD><input type="text" name="txtBegin" size="20"></TD>
       </TR>
       <TR>
     <TD>Ending Date:</TD>
         <TD><input type="text" name="txtEnd" size="20"></TD>
       </TR>
    </TABLE>
    <P><input type="button" value="Open DAP"
        name="B2" onClick="openDAP()"></P>
  </FORM>
  </BODY>
</HTML>

Create a data access page that is based on the "Employees Sales by Country" query in the Northwind sample database. Save it as EmpSalesByCountry.htm.

In Design view for the page, click Macro on the Tools menu, and then click Microsoft Script Editor. Add the following script immediately before the closing HEAD tag for the page:

<SCRIPT language="VBScript">
Function readCookie(strVariableName)
   Dim intLocation
   Dim intNameLength
   Dim intValueLength
   Dim intNextSemicolon
   Dim strTemp

   ' Calculate length and location of variable name.
   intNameLength = Len(strVariableName)
   intLocation = Instr(Document.Cookie, strVariableName)
    
   strTemp = Right(Document.Cookie, Len(Document.Cookie) - intLocation + 1)

   ' Find the position of the next semicolon.
   intNextSemicolon = Instr(strTemp, ";")

   ' If not found, assume you are at the end.
   If intNextSemicolon = 0 Then 
      intNextSemicolon = Len(strTemp) + 1
   End If
    
   intValueLength = intNextSemicolon - intNameLength - 2
   readCookie = Mid(strTemp, intNameLength + 2, intValueLength)
End Function
</SCRIPT>

<SCRIPT language="VBScript">
   Dim startDate
   Dim endDate
   startDate = "pBegin"
   startDate = readCookie(startDate)
   endDate = "pEnd"
   endDate = readCookie(endDate)
   Document.MSODSC.Recordsetdefs(0).ParameterValues.Add _
      "[Beginning Date]", startDate
   Document.MSODSC.Recordsetdefs(0).ParameterValues.Add _
      "[Ending Date]", endDate

'For pages based on SQL Server stored procedures, use the following syntax:
'   Document.MSODSC.Recordsetdefs(0).ParameterValues.Add _
'      "@Beginning_Date", startDate
'   Document.MSODSC.Recordsetdefs(0).ParameterValues.Add _
'      "@Ending_Date", endDate
</SCRIPT>

Open the page by using the http://servername/dapOpen.htm URL, the HTML file that you created in step 1, instead of opening EmpSalesByCountry.htm directly.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

Open the Northwind.mdb sample database.
Create a new query.
On the View menu, click SQL View.

Type the following SQL statement:

PARAMETERS a Text ( 255 );

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
WHERE (((Customers.CustomerID)=[a]));

Save and then close the query.
Create a new data access page that is based on the query.
Save the page to a Web folder, and then close it.
Open the page in Internet Explorer 5.0.
Type ALFKI when you are prompted for the parameter.
On the File menu, click Print.

Notice that the page is never printed. Additional attempts to print the page do not work until you quit Internet Explorer, and then restart it.

Additional query words: pra

Keywords: kbbug kbfix KB253976

Microsoft KB Archive/253976

Contents