Show Filtered Subform Records and Add a New Record in Microsoft Access
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.

- 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.
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.
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.
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.
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.
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.
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.

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.

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.




