Others

Show Filtered Subform Records and Add a New Record in Microsoft Access

Elise WilliamsElise Williams Nov 14, 2026 869 views

Question details

The user wants an Access subform to show matching credit card charges and move to a blank new record after an account is selected.

Show Filtered Subform Records and Add a New Record in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
A main form combo box selects a credit card account, and a subform displays its transactions.
Observed behavior
After DoCmd.GoToRecord moves to a new record, only one previous record is visible, and the blank record may not appear at the bottom of the subform.
Before you start

Check that the transaction table has an account ID related to the credit card account table, and note the names of the main form, combo box, and subform control. Make sure the subform's record source is updateable and permits additions.

Solution 1Recommended

Filter the Access subform and move to a new record

Use this approach to display transactions for the selected account and place the cursor on a blank record.

The example names below are placeholders. Replace them with the names in your database. The subform control name can differ from the name of the form it contains.

1
Filter the transaction query

Open the subform's record source in Query Design. In the account ID field's Criteria row, enter Forms![frmAccounts]![cboAccount], using your main form and combo box names. Save the query.

2
Configure the subform

Open the subform in Design View and use the Property Sheet to set Default View to Continuous Forms or Datasheet, Allow Additions to Yes, and Data Entry to No. Enlarge the subform control on the main form to show more matching records at once.

3
Requery after selection

On the main form, open the combo box's Property Sheet, select the After Update event, and choose Event Procedure. In the VBA editor, add these lines inside the procedure: Me!subTransactions.Requery; Me!subTransactions.SetFocus; DoCmd.GoToRecord , , acNewRec. Put each command on its own line.

4
Test the account selection

Open the main form in Form View and choose an account from the combo box. Confirm that the subform shows its matching transactions and moves to a blank record. Ensure the new transaction receives the selected account ID, for example through correctly configured Link Master Fields and Link Child Fields or an appropriate default value.

Filter the Access subform and move to a new record
New-record position: Access does not reliably provide a way to force the blank new record to the bottom of the visible subform. A taller subform can show more records, but scrolling may still be needed when there are too many to fit.
Free Microsoft Office alternative

Work with transaction lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with Office files. Move spreadsheet-based transaction lists into WPS Spreadsheets for easy viewing and editing; it does not replace or repair an Access subform.

View and filter transaction lists in a spreadsheetOpen and edit Microsoft Excel XLSX files for easier migrationUse a familiar, easy-to-navigate Office-style interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access subform show only one record?

The subform may be set to Single Form view or may be too short to show more rows. In its Property Sheet, set Default View to Continuous Forms or Datasheet, and enlarge the subform control on the main form.

How do I refresh the subform when the combo box changes?

In the main form combo box's After Update event procedure, call Me!subTransactions.Requery, replacing subTransactions with your subform control's name.

Why can't I add a new record to the subform?

Check that Allow Additions is Yes in the subform's Property Sheet and that its record source is updateable. Also confirm that required fields, including the account ID, can be supplied for the new transaction.

Can I force the blank new record to appear at the bottom?

Access does not reliably offer a way to control that scroll position. You can enlarge the subform to show more records, but the new record may still appear elsewhere when focus moves to it.