Using MS Access/Using query as filter in DoCmd.OpenForm
I cannot make OpenForm method to work with FilterName parameter. Have table tbParts and table tbVendors with a key field Parts in a both. Form fmParts based on tbParts (updatable) supposed to display only records filtered by query. I made query qrFilter which includes all fields from tbParts and one field Vendor from tbVendors on which I apply filters criteria; tables are linked by key field Parts. When I open fmParts using DoCmd.OpenForm “fmParts”,,”qrFilter” it returns not filtered records. Can you help please? I tried it in Access 10 and Access 07
Frankly, I have never used that property. It is supposed to filter the form by the criteria in the query. But its easier to just use the WHERE clause.
However, part of the problem here may be your table structure. It doesn't make sense to link tblparts and tblVendors on PartID. This would require a record in tblVendors for each part. Instead VendorID should be a foreign key in your Parts table.
Your form should be bound to just tblParts and should include the VendorID field bound to a combobox to select the vendor. If you want to open the form filtered for a specific vendor, you use a WHERE clause like:
"[VendorID] = " & some ID
Usually you will have a combo to select a vendor on the form where you trigger the OpenForm method and you reference that control in WHERE clause. For example:
"[VendorID} = " & Me.cboVendor
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA