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

• Download source code - 232.7 KB

Introduction

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

Gotchas

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.

History

• 12^th September, 2018: Initial version