Criteria

[David W]

Is there a way to take an existing query and set the criteria by VB?
I have a query named ppo1 with a field named vendor, when the form that uses
the query loads, it shows all vendors, which is fine.
But when a user selects a vendor from a combo, I am wanting to set the
criteria for ppo1 through code.
Is this possible?

 

[Wayne Morgan]

Yes, you can modify the SQL property of the QueryDef object. You may also be
able to just set the Filter property of the form with the value selected in
the combo box then set Me.FilterOn to True.

It is also possible to set the criteria in the query to show all records if
the combo box is Null, but to filter the records if the combo box isn't
Null.

Example:
=Forms!frmMyForm!cboMyCombo Or Forms!frmMyForm!cboMyCombo Is Null

If the combo box is Null, the second part returns True, causing the query to
return all records. If the combo box isn't Null, the first part will supply
the needed value for that field. You will need to Requery the form in the
combo box's AfterUpdate event for this to work.

 

[David W]

That did the trick!
Thanks Wayne

Yes, you can modify the SQL property of the QueryDef object. You may also
be able to just set the Filter property of the form with the value
selected in the combo box then set Me.FilterOn to True.

It is also possible to set the criteria in the query to show all records
if the combo box is Null, but to filter the records if the combo box isn't
Null.

Example:
=Forms!frmMyForm!cboMyCombo Or Forms!frmMyForm!cboMyCombo Is Null

If the combo box is Null, the second part returns True, causing the query
to return all records. If the combo box isn't Null, the first part will
supply the needed value for that field. You will need to Requery the form
in the combo box's AfterUpdate event for this to work.