Others

How to Fix an Access Purchase Form That Cannot Add Products

Partner Editor Sep 30, 2026 868 views

Question details

The user is unable to add or select new products in a Microsoft Access purchase form designed for medical products.

How to Fix an Access Purchase Form That Cannot Add Products

Product: Microsoft Access
Device & OS: not provided
Scenario: Entering new product purchases into a database using a custom form.
Observed behavior: The form prevents the user from selecting or adding new products because it relies on a non-updateable multi-table query.

Before you start

Back up your Access database (.accdb) before making structural changes to your forms or queries to prevent accidental data loss.

Solution 1Recommended

Implement a Main Form and Subform Architecture

Separate your multi-table query into individual tables bound to a main form and a subform to ensure the data remains updateable.

Multi-table queries in Access often become read-only (non-updateable), which blocks data entry. By isolating the parent data (e.g., Purchase Order) and child data (e.g., Purchase Details), you restore the ability to add new records.

Create the Main Form

Open your database in Design View and create a new Main Form bound strictly to your primary table (e.g., Purchase Orders table).

Create the Subform

Create a separate Subform bound exclusively to your details table (e.g., Purchase Details table).

Embed the Subform

Embed the Subform into the Main Form using the Subform/Subreport control found in the Form Design ribbon.

Link the Forms

In the Property Sheet for the Subform control, link the Master Fields and Child Fields using the primary key (e.g., PurchaseOrderID) to synchronize the records.

Implement a Main Form and Subform Architecture

Reference Design: You can review the Northwind sample database in Access for excellent examples of functional main form and subform architectures.

Solution 2

Use Combo Boxes for Foreign Key Selection

Replace manual primary/foreign key entry fields with Combo Boxes to simplify selecting suppliers, products, units, and lots.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite?

While Microsoft Access is a specialized database tool, many everyday data management, inventory, and purchase tracking tasks can be handled efficiently with spreadsheets. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office for your document, spreadsheet, and presentation needs.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formatsManage lists, inventories, and simple data entry with WPS SpreadsheetLightweight application that runs smoothly on Windows, Mac, and LinuxFree to use with an intuitive, familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query become non-updateable?

An Access query typically becomes non-updateable when it joins multiple tables without a clear one-to-many relationship, uses aggregation (like GROUP BY), or lacks a defined primary key.

How do I handle products with multiple units or lots in Access?

You should create separate, related tables for 'Purchase Details' and 'Lots' to maintain database normalization. Use nested subforms or cascading combo boxes on your data entry form to manage this one-to-many data effectively.

What is the Northwind sample database?

Northwind is a free template provided by Microsoft Access that demonstrates database design best practices, including functional main/subform layouts, inventory management, and query structuring.

Office Quick Solutions