Make a Filtered Query and Form Updateable in Access 2016
Question details
The user needs to edit records through an Access 2016 form whose source query joins two tables and filters records by status.
- Product
- Microsoft Access 2016
- Device & OS
- not provided
- Scenario
- A form uses a query that joins CredWorkBook and In_Process on NPI and selects records where CredWorkBook.Status is New or another specified value, such as FIX.
- Observed behavior
- The form's recordset becomes non-updateable when it is bound to the joined, filtered query.
Open both tables in Design View and check their primary keys and field data types. Before using NPI to populate CredWorkBookID, verify that each In_Process record matches exactly one CredWorkBook record; duplicate or unmatched NPI values need to be resolved first.
Correct the primary-key-to-foreign-key relationship
Use this solution when the query joins CredWorkBook and In_Process on NPI instead of linking the parent record's ID to a foreign key.
A simple status filter does not normally make a query read-only. For this case, the key correction is to join CredWorkBook.ID on the one side to In_Process.CredWorkBookID on the many side, rather than relying on NPI.
In the Navigation Pane, right-click each table and select Design View. Confirm that CredWorkBook.ID is its primary key and that In_Process also has a primary key. If CredWorkBook.ID is AutoNumber, set In_Process.CredWorkBookID to Number with Field Size set to Long Integer.
Select Create > Query Design, add CredWorkBook and In_Process, and temporarily join their NPI fields after confirming that each child record has exactly one parent match. Select Update on the Query Design tab, add In_Process.CredWorkBookID to the grid, and enter [CredWorkBook].[ID] in its Update To row. Review the matches in Datasheet View, then click Run to populate the foreign key.
Open the form's source query in Design View. Delete the join between the NPI fields and drag CredWorkBook.ID to In_Process.CredWorkBookID. Include the key fields in the query output and keep the desired status criterion, such as "New" or "FIX", under CredWorkBook.Status.
Select Database Tools > Relationships and add both tables. Replace the old NPI relationship, if present, with a relationship from CredWorkBook.ID to In_Process.CredWorkBookID. Select Enforce Referential Integrity and click Create. If Access rejects it, resolve unmatched foreign-key values or incompatible field types first.
Open In_Process in Design View, select CredWorkBookID, and set Required to Yes once existing records have valid values. Save the table, then open the source query in Datasheet View and try editing a non-key field. After verifying that nothing still depends on the obsolete zNPI field, remove it if it was used only for the old relationship.
Bind the form to the corrected query and enable edits
Use this check after the query is editable on its own but the form still prevents changes.
Use WPS Office to Review and Share Exported Access Data
WPS Office does not repair Access queries or run Access forms, but it is a free, lightweight alternative for working with exported data and related Office documents. Its familiar interface and Microsoft Office format compatibility make migration straightforward for spreadsheet, document, and presentation work.
Frequently Asked Questions
Does filtering Status to New or FIX make an Access query read-only?
Not normally. Open the query in Design View and check the Criteria row under Status. A simple criterion such as "New" or "FIX" generally preserves updateability when the underlying query is editable. Inspect table keys, joins, and query features such as totals or DISTINCT if edits are blocked.
Why should I use CredWorkBookID instead of NPI for the join?
CredWorkBookID explicitly identifies the parent record through CredWorkBook.ID. NPI may not uniquely identify a workbook record. In Database Tools > Relationships, link CredWorkBook.ID to In_Process.CredWorkBookID so the relationship follows the intended one-to-many structure.
What if Access will not enforce referential integrity?
Check the relationship in Database Tools > Relationships and verify compatible field types. An AutoNumber parent key typically needs a Number/Long Integer foreign key. Also check for non-null CredWorkBookID values that have no matching CredWorkBook.ID, and correct those records before enabling Enforce Referential Integrity.
Why is the query editable but the form still read-only?
Open the form in Design View and press F4. Under the form's Data properties, verify the Record Source, set Recordset Type to Dynaset, and set Allow Edits to Yes. For individual controls, check that Locked is No, Enabled is Yes, and Control Source refers to an editable field rather than a calculated expression.
Can WPS Office edit this Access database or fix its form?
No. WPS Office does not replace Access for .accdb database relationships, queries, or forms. To work with the results in WPS Spreadsheet, select the query in Access and use External Data > Excel to export it, then open the exported workbook in WPS.
