When importing records into an Access database table and then loading records from the table into a form or report, it may happen that the form or report does not show all the records. This issue has been reported by various Access users.
User Instances Reporting About the Issue
Instance 1: The user said that he uses an “Access database (.accdb) file having various objects like queries, tables, and forms. The problem is in a form that has a table. When trying to work through the form to edit the table in table layout, the layout usually provides a 'new record row (*)' in the last row and the row can be edited to insert a new record into the table. But the table won't show the new record row when he opens the .accdb file again.”
Instance 2: The user reported that “New records added to an Access table won’t show up in query report. A form that references that table runs a query and based on a number and date it returns records for that criteria. That part still works fine. It’s after the query returns rows you press another button and from that query a report opens displaying the old records. But the report won’t display the new records.”
What Causes the ‘Access Table Not Showing All Records’ Issue?
When you apply a filter to a newly created form or report and then save it without removing the filter, the filter cannot be turned off. And so, the form or report will display the filtered view of the underlying table or query, and does not display all of the data.
Tip: A table might also not display all the records if your Access database is corrupt. You may compact and repair the database and recover the table records. But if the compact and repair method does not work, use Stellar Repair for Access software to repair the corrupt database and restore the table and all its records in a few clicks. The free demo version of the software can help you perform the database repair process and provide preview of all the recoverable objects.
How to Resolve ‘Access Table Not Showing All Records’ Issue?
You need to remove the filter, before saving a newly created form or report, from the object’s design to view all the records in the underlying table or query.
In case, you have saved the object (i.e. a form or report) while it is still in filtered state, you need to edit the object’s Record Source property to remove a permanent filter. To do so, perform these steps:
- Open your Access database.
- Right-click the form or report from the Navigation Pane, and then click ‘Design View’ to open the object (i.e. the form or report) in design view.
Note: If you cannot see the property sheet, press F4 to open it.
Figure 1 - Open Access Database Objects in Design View
- Click ‘Property Sheet’ under the ‘Design’ tab.
- When the Property Sheet opens, click the three dots next to the Record Source property.
- The object’s recordset query is displayed in the query designer window. Search for the filtered fields and filter criteria in the query design grid. For instance, in the image below, you can see a single filtered field, CompanyName, and a single criterion, Contoso, Ltd.
- In the query design grid, click the gray bar above the Field row to select a column containing a filtered field.
- Hit the ‘DELETE’ button to remove the filtered field along with its criterion.
- Repeat steps 6 and 7 for each filtered field to be removed from the object's design.
- Exit the query designer. Click ‘Yes’ when prompted to save the changes made to the SQL statement and update the property.
Figure 2 - Open Property Sheet
Figure 3 - Record Source Property
Figure 4 - MS Access Query Designer Window
Conclusion
In this article, we’ve discussed about the ‘Access table not showing all records’ issue and how you can fix it by using the Record Source property. If you still encounter the issue, it means that the Access database table might be corrupt. Try compacting and repairing your database to restore the objects, or use a specialized Access database repair tool such as Stellar Repair for Access to quickly and easily restore your database and table records. The software can also help retrieve all the objects of the database.
7 min read
