You are here:

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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.

[an error occurred while processing this directive]