Possible to change MS Access subform fields through VBA?

Question

I can find plenty of examples of how to return specific records in a subform through altering the underlying source query in code and re-querying, but I'm struggling to use the same principle to alter the fields that are returned.

I have the following situation:

Combobox to select 1 of 5 fields. Subform is supposed to show the selected field plus a couple of static fields, lets call them fields 6 and 7

So in the case the user selects field 1 from the dropdown, subform should show fields 1, 6 and 7. In the case they pick field 4, subform should show fields 4, 6 and 7 etc.

This is what (amongst other things) I've tried:

Set the subform up through the wizard with a query (select field1, field6, field7) as source, amend said query after combobox selection is made:

Set qd = CurrentDb.QueryDefs("myqueryname")
qd.SQL = "Select " & mycomboboxselection & ",field6,field7 from mytablename"
Form_mymainformname.mysubformname.Requery

The query itself updates fine if I run that standalone after the change, but the subform within the main form doesn't change and when I click on the subform itself from the navigation window it seems to be stuck looking for field 1 as it asks me to input a parameter value

Can anyone help with how to achieve this please?

Answer 1

Ok I found a solution after a few more hours searching and trial and error.

There are two seperate problems here. Firstly creating the subform uses the newly created form as the source object rather than directly using the query, and it seems that no matter how you try to manipulate the record source of said form, it doesn't like changing the fields it was built with.

So, create the subform, change the source object from the form to your query (and delete the now pointless newly created form), then you can use the code I started with:

Set qd = CurrentDb.QueryDefs("myqueryname")
qd.SQL = "Select " & mycomboboxselection & ",field6,field7 from mytablename"

Which works! sort of....

You have to close and reopen the form every time to see the actual updates though, which obviously isn't what we're going for

The other line does nothing:

Form_mymainformname.mysubformname.Requery

It works fine for a change of records, but apparently not for a change of fields. I suspect this is a bit of a ms quirk

The below, however, works, even though I feel like it should do exactly the same as the code line above:

Form_MyForm.MySubForm.SourceObject = Form_MyForm.MySubForm.SourceObject

Answer 2

Set the RecordSource of the subform to the SQL:

Dim SQL As String

SQL = "Select " & mycomboboxselection & ",field6,field7 from mytablename"
Me!YourSubformControl.Form.RecordSource = SQL

It will force a requery of the subform.