How to Reference All Records from a Linked Access Subform
Question details
The user needs a method to access or search the complete underlying table of a linked Access subform, as the default recordset only shows filtered results.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user is entering customer details in an order form's subform and wants to either retrieve existing customer details from the full database or seamlessly create a new customer.
- Observed behavior
- When a subform is linked to a main form, the subform's RecordsetClone is automatically filtered to match the current main-form record, hiding the rest of the underlying table's data.
Identify the exact name of your subform's underlying source table or query, and verify that your VBA editor has the Microsoft Office DAO library reference enabled.
Open a Separate DAO Recordset Using VBA
Bypass the linked subform's filtered state by opening an entirely separate, unfiltered recordset directly against the underlying table using VBA.
Because the subform's RecordsetClone is intentionally filtered by the main form's link fields, it cannot be used to search the whole table. Opening a fresh DAO Recordset allows you to traverse all available records without affecting the current form's display state.
Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor, and locate the module for your main order form.
Inside your search or button click event, declare a DAO recordset by typing: Dim rs As DAO.Recordset.
Instantiate the recordset against your target table (e.g., 'Customers') by adding: Set rs = CurrentDb.OpenRecordset("Customers", dbOpenDynaset).
Use the FindFirst method to locate the specific record. For example: rs.FindFirst "CustomerID = " & Me.txtSearchID. If a match is found (Not rs.NoMatch), you can extract and use the data.
Use a Combo Box with LimitToList and ListItemsEditForm
Improve user experience by replacing standard text boxes with a configured Combo Box that allows users to pick existing customers or add new ones on the fly.
Verify Relational Data Modeling
Ensure your database tables are properly structured with One-to-Many relationships to avoid data duplication and subform syncing errors.
Looking for a Free and Lightweight Office Suite?
While WPS Office does not feature a direct database application like Microsoft Access, it is an exceptional, free alternative for all your primary document, spreadsheet, and presentation needs. If you regularly export database reports to Excel or PDF, WPS Office offers a seamless environment to analyze, format, and share your data without the high subscription costs.
- 1. Visit the WPS Website: Go to the official WPS Office website to download the latest installer.
- 2. Install the Suite: Run the installer file and follow the simple on-screen instructions to set up Writer, Spreadsheet, Presentation, and PDF tools.
- 3. Open Your Exported Data: Launch WPS Spreadsheet to open, edit, and analyze any CSV or XLSX reports exported from your database.
Frequently Asked Questions
Why does RecordsetClone only show a few records in my subform?
When a subform is linked to a main form via Master/Child fields, Microsoft Access automatically filters the subform's data to only display records related to the current main-form record. The RecordsetClone property inherits this filter, which is why it does not reflect the entire underlying table.
Can I add a new record directly from a combo box dropdown?
Yes. By setting the combo box's 'Limit To List' property to True and specifying a form in the 'List Items Edit Form' property, Access will automatically prompt you to open that form to add a new record when an unmatched item is typed.
What is the correct database relationship for Orders and Order Details?
The standard relationship is One-to-Many. The main 'Orders' table connects to an 'OrderDetails' table in a 1:M relationship. The 'OrderDetails' table then connects to a 'Products' table in a Many-to-One (M:1) relationship, allowing a single order to contain multiple products.
What does dbOpenDynaset do in Access VBA?
Using dbOpenDynaset when opening a DAO Recordset creates a dynamic set of records. This allows you to both view and update the data in the underlying tables, and you can freely navigate through the recordset using methods like FindFirst, MoveNext, or MovePrevious.
