ACC1x: Invalid Reference: Previewing Graph Based on Parameter

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

The message

   Invalid reference to field 'Field Name' in query 

CAUSE

The data source for the graph is a parameter query. The parameter query is run one time to collect the values required to display the graph in Form view or print preview. Once the parameters are collected through the Enter Parameter Value box, they no longer exist. When you try to print the graph, the graph must be regenerated from the underlying data. Since the parameters are no longer available and are not prompted for again, the error message stated above is generated.

RESOLUTION

Rather than enter the parameters using the Enter Parameter Value box, the parameters for the query should come from a source that is available while the form is active. Modify the query so that the parameters are referenced directly from a second form.

The following example demonstrates how to use a second form to collect the values:

1. Open the sample database NWIND.MDB. Create a blank, unbound form called Parameter Form with the following controls and properties:
   

   
         Form: Parameter Form
         Text box
            ControlName: Beginning Date
         Text box
            ControlName: Ending Date
         Command button
            Caption: Open Graph Form
            OnPush: Graph Form.Open 




2. Create a new macro called Graph Form with the following actions:
   

   
         MacroName      Action         Defined Below
         -------------------------------------------
         Open           SetValue             1
                        OpenForm             2
         Close          Close                3
   
         Graph Form Actions
         ------------------------------------------
         1. SetValue
               Item: Forms![Parameter Form].Visible
               Expression: False
         2. OpenForm
               Form Name: Graph Form
               View: Form
               Filter Name: <leave empty>
               Where Condition: <leave empty>
               Data Mode: Edit
               Window Mode: Normal
         3. Close
               Object Type: Form
               Object Name: Parameter Form 




3. Open the Employee Sales By Country (Parameter) query in Design view.



4. Change the criteria row for the Shipping Date column from
   
   Between [Beginning Date] And [Ending Date]
   
   to:
   
   NOTE: In the following example, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this example.
   
   Between Forms![Parameter Form]![Beginning Date] And _ Forms![Parameter Form]![Ending Date]



5. From the Query menu, choose Parameters. Change the parameters to be:
   

   
         Parameter                                    Data type
         ------------------------------------------------------
         Forms![Parameter Form]![Beginning Date]      Date/Time
         Forms![Parameter Form]![Ending Date]         Date/Time 

   
   NOTE: Your query may not have parameters explicitly defined in the Parameters dialog box; however, it is good practice to do so.



6. Open the Graph Form and alter the OnClose property to be:
   
   OnClose: Graph Form.Close



7. Open the Parameter Form form and type 1/1/92 in the first box, and type 12/31/92 in the second box. Choose the Open Graph Form button.
   
   Note that when you choose the Open Graph Form button, a macro hides the parameter form and then opens the graph form. Although the parameter form is hidden, it is still open, and the values on it are still available for the query that the graph is based on.



8. From the File menu, choose Print Preview.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Problem

1. Open the sample database NWIND.MDB. Create a blank, unbound form and save it with the name Graph Form.



2. Click the graphing tool in the toolbox, and then click in the blank form to start the GraphWizard.



3. In the "Also, Select a Data Source for Your Graph" box, select Employee Sales By Country (Parameter).



4. Choose Next to advance to the next screen.



5. In the Available Fields box, select Sale Amount. Choose the ">" button to add it to the Fields For Graph box.



6. In the Available Fields box, select Salesperson, and then choose ">".



7. Choose Next to advance to the next screen.



8. Choose the Design button.



9. From the View menu, choose Form.



10. Type 1/1/92 in the Beginning Date parameter value box, and then choose OK.



11. Type 12/31/92 in the Ending Date parameter value box, and then choose OK. A bar graph will be displayed.



12. From the File menu, choose Print Preview.
    
    The error message: "Invalid reference to field 'Beginning Date' in query" will be displayed.

Keywords          : kbtool IntpGrph 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: March 27, 1999