I recently embedded a query as a subform in another form and encountered an issues.

Here’s the gist of the situation. For a project I was working on, I have a process that updates a tables data. I then use a crosstab query to display it properly (or as the user wants it displayed). The issue being that the fields in the crosstab change with iteration of the process. Thus, it was impossible to build a form since the fields were not constant.

So the solution was to embedded a query directly as a subform.

The Real Issue

The issue I then encountered was that after the process ran, I could requery the query’s subform and the data would indeed update to refelect the latest iteration’s values, but the field that were displayed did not get updated. I tried Requery, Refresh, Repaint, nothing seemed to be able to get the subform to show the proper listing of fields from the source query. Yet, if I opened the query, the fields had indeed been changed.

The Fix

Well, after fighting with this for long enough, I decided to take the bull by the horns as I couldn’t find any method that worked. Ultimately, the solution was to clear the subform’s Source Object and then set it back.

Dim sSourceObject As String

sSourceObject = Me.YourFormName.SourceObject
Me.YourFormName.SourceObject = ""
Me.YourFormName.SourceObject = sSourceObject

Or, per David’s comment below, it can be reduced to a single line of code

Me.YourFormName.SourceObject = Me.YourFormName.SourceObject

By doing so, it forces Access to reload the object thus resulting in the proper fields being listed. It’s a little overkill, but what do you want, sometimes we just need things to work!

Useful Resources

SubForm.SourceObject property (Access)

Use the SourceObject property to identify the form or report that is the source of the subform or subreport on a form or report.