0

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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Afraid this isn't working, if I select the field I have set up with the initial source object it's fine, however if I select any other field, the fieldname in the subform remains the same and it just populates all records in the field with #name?
@HelloWorld that's because if you have controls bound to a a field named mycomboboxselection1 and you change your query to be mycomboboxselection2 the first field no longer exists so the control doesn't know what to display, hence #Name? You need to change the control source appropriately, too.
@Brad, This should probably be a standalone answer, if I've got the right end of the stick there's no need to update the recordsource each time you can just update it to .* once and change the field through the control source for that field individually i.e. Me!MySubForm.Form!MyField.ControlSource = Me!MyComboBoxSelection. Thanks, works great
0

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
Improve this answer

1 Comment

You could make life a lot easier for yourself if you separated the field names from the form text box names like this: SQL = "Select " & mycomboboxselection & " AS Text0, field6 AS Text1,field7 AS Text2 from mytablename. The changing your source to this: SQL = "Select " & mycomboboxselection & ",field3 AS Text1,field4 AS Text2 from mytablename will work smoothly as you no longer bind the fields themselves but just their aliases so you don't have to change your bindings on requery

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.