ACC1x: Where Condition Refers to Fields, Not to Controls

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

On page 348 of the Microsoft Access "Language Reference" manual, the description for the action argument Where Condition reads:

   To open a form in which the value of one of its controls
   equals the value of a control on another form, use this
   syntax:

      [controlname on opened form] =
         Forms!formname![controlname on other form]

RESOLUTION

The description for the action argument Where Condition on page 348 should read:

   To open a form and show only records where one of the fields
   matches the value of a control on another form, use this
   syntax:

      [field in opened form's recordsource] =
         Forms!formname![controlname on other form]

MORE INFORMATION

The action argument Where Condition is used to filter the records in the form's dynaset. Microsoft Access compares the specified fields in each record on the form being opened to the controls on the original form. Records on the form being opened will be included in the dynaset only if the record's fields satisfy the Where Condition argument.

The steps below demonstrate the correct use of the Where Condition argument:

1. Open the sample database NWIND.MDB. Create a new unbound form with the following controls. Save the form as ChooseState.

         Textbox
         ------------------------
   

         ControlName: ChosenState
   

         Command Button
         ------------------
   

         OnPush: Open Emp 2
   

2. Create a new form, bound to the Employees table, with the following controls. Save the form as Emp 2.

         Textbox
         ------------------------
   

         ControlName: Last Name
         ControlSource: Last Name
   

         Textbox
         --------------------
   

         ControlName: State
         ControlSource: State
   

3. Create a new macro with the following action. Save the macro as Open Emp 2.

         Action: OpenForm
         ----------------------------------------------------------
   

         Formname: Emp 2
         Where Condition: [State] = Forms!ChooseState![ChosenState]
   

4. Open the ChooseState form. Type "WA" (without the quotation marks) in the text box, and choose the command button. The Emp 2 form opens and shows only those employees who live in Washington state.

5. Close the forms.

6. Open the Emp 2 form in Design view. Change the ControlName property of the State text box to Test. Save and close the form.

7. Open the form ChooseState. Type "WA" (without the quotation marks) in the text box, and choose the command button. The Emp 2 form will open and show the same records as in step 4. This demonstrates that you are filtering the underlying data, not the controls on the form.

REFERENCES