Click here to Skip to main content
15,608,989 members
Articles / Database Development
Posted 12 Sep 2018


2 bookmarked

Method to Change Source of a Form’s Subform Source to a Query or Table in Microsoft Access

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
12 Sep 2018CPOL2 min read
Method to change source of a Form’s Subform source to a Query or Table in Microsoft Access


It is often desirable to show different data in a Microsoft Access Form. This would be done using a Subform. There are some choices.

The normal solution is to have multiple Subform controls on a From and make only one visible. One of the things I dislike about this solution is that it clutters up the Access project with all those Form objects, and there is no way to organize all these modules using objects. Using a Subform is the default when the desired view is a DataSheet view; as far as I can tell, there is no particular advantage of building a Form that is to be displayed in a Subform only in DataSheet view. An advantage of using a Form is that then only the fields that have associated controls will be visible, but this could also be accomplished in a query by deselecting the Show (same as not including them in the Select part of the SQL clause), but this is only possible in a Query. A disadvantage of using a form and that is that if there are field changes in the underlying Table/Query, it has to be changed in Form.

One of the cases where it is very desirable to have a dynamic view is to display data that changes dynamically, like when different Excel spreadsheets are imported into the same temporary Table, and columns are different.

The Code

Below is shown the code behind an Option button that changes the SourceObject of a Subform to a Table and a Query:

Private Sub optionShowQuery_Click()
    subFormData.SourceObject = "Query.TablesJoined"
    optionShowTable = False
    optionShowQuery = True
End Sub

Private Sub optionShowTable_Click()
    subFormData.SourceObject = "Table.Table_Countries"
    optionShowTable = True
    optionShowQuery = False
End Sub

Image 1

Initial Form

Image 2

Show Table Selected

Image 3

Show Query Selected


There are three buttons on the Form. The first one will delete all records from the Table, and the second will replace the records in the Table from a backup Table. If the first button in pressed (“Delete Table Contents”), in a couple of seconds, all the fields displayed will change to the “#Deleted”. Then, when the second button is pressed (“Replace Table Contents”), there will be no change. The last button, “Requery”, for force the SubForm to do a requery of the data. This will result in the actual content of the Table being displayed.

This means that changes in the underlying data do not appear automatically in real time, and that to get this data, a Requery on the SubForm will have to be performed.

Image 4

View after Table data deleted


  • 12th September, 2018: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior) Clifford Nelson Consulting
United States United States
Has been working as a C# developer on contract for the last several years, including 3 years at Microsoft. Previously worked with Visual Basic and Microsoft Access VBA, and have developed code for Word, Excel and Outlook. Started working with WPF in 2007 when part of the Microsoft WPF team. For the last eight years has been working primarily as a senior WPF/C# and Silverlight/C# developer. Currently working as WPF developer with BioNano Genomics in San Diego, CA redesigning their UI for their camera system. he can be reached at

Comments and Discussions

-- There are no messages in this forum --