Others

How to Fix Access Form Not Displaying All Records

John Wilson Oct 15, 2026 868 views

Question details

New records fail to appear in a database form and its datasheet view despite having no active form filters applied.

How to Fix Access Form Not Displaying All Records

Product: Microsoft Access
Device & OS: not provided
Scenario: Viewing or entering new data into a form that uses a multi-table query as its underlying record source.
Observed behavior: Newly created records are completely hidden from the view because the record source query relies on INNER JOINs, which filter out entries lacking matching foreign keys in related tables.

Before you start

Open your Access database in Design View and locate the specific query acting as the Record Source for your problematic form so you can inspect its structural joins.

Solution 1Recommended

Change Query Joins from INNER JOIN to LEFT JOIN

Modifying the join type ensures that all records from your primary table are displayed, even if there are no matching related records in the joined tables.

When a form's record source utilizes INNER JOINs across tables (e.g., Samples, Lab Tests, and GL Codes), it strictly returns only the rows that contain matching data in all interconnected tables. Converting these to LEFT JOINs forces the query to return every record from the primary table regardless of whether the related tables have data.

Open the Query Builder

Open your Access form in Design View. Open the Property Sheet, go to the Data tab, and click the three dots (...) next to the Record Source property to open the Query Builder.

Modify the Join Properties

Right-click the line connecting your primary table to a related table in the query design window and select Join Properties.

Select the LEFT JOIN Option

Choose the option that says 'Include ALL records from [Primary Table] and only those records from [Related Table] where the joined fields are equal' (usually option 2 or 3), then click OK.

Save and Verify

Save the changes to the query, close the Query Builder, and switch your form to Form View to verify that the missing records now appear.

Change Query Joins from INNER JOIN to LEFT JOIN

Verify Foreign Keys: Even with a LEFT JOIN, ensure that your data entry workflow correctly populates required foreign keys like the GL Account or Sample values in the related tables.

Solution 2

Redesign Using a Main Form and Subform

Avoid using complex, multi-table queries as a single form's record source, which can restrict data visibility and violate database normalization principles.

Free Microsoft Office alternative

Looking for a Lightweight Data Management Alternative?

While WPS Office does not include a direct relational database application like MS Access, WPS Spreadsheet is a highly capable tool for managing flat-file databases, tracking lists, and analyzing datasets. As a comprehensive and free office suite, it provides a familiar interface without the complexity of SQL queries.

1. Export Access Data: In Microsoft Access, right-click your table or query, select Export, and choose the Excel (.xlsx) file format.
2. Open with WPS Spreadsheet: Launch WPS Office and open the exported spreadsheet to view all your records without being restricted by INNER JOIN limitations.
3. Manage Data Easily: Utilize built-in data forms and filters under the Data tab in WPS Spreadsheet to enter, sort, and manage your records effortlessly.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Easily manage and analyze large datasets using advanced filtering, data forms, and pivot tables.Lightweight installation with an intuitive, unified tabbed interface.Free to use, reducing software overhead for daily office productivity tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query return fewer records than my table actually holds?

If your query combines multiple tables using INNER JOINs, Access will only display rows that have perfectly matching data in every connected table. If a record lacks a matching entry in even one related table, the entire row is omitted from the results.

Should I use spaces or special characters in Access table or field names?

No. Using spaces or symbols like '#' (e.g., 'Sample #') can cause syntax errors or force you to use bracket enclosures throughout your SQL queries. It is best practice to use underscores or CamelCase (e.g., 'SampleNumber') for all database object names.

Can I enter new records directly into a multi-table query form?

While it is technically possible, it is not recommended. Data entry can often be blocked or cause missing records if the query is not updatable or lacks required foreign key assignments. Using a main form linked to a subform is a much more reliable approach for handling related data entry.

Office Quick Solutions