I have a form with a subform, and I want the subform record source that populates the subform to change depending on the inputs on some combo boxes in the main form.
Using VBA, I have built a function that generates the SQL statement I want to populate the subform with. I know this works because I have tested it with the msgbox and it gives me the SQL statement I want. The sql statement uses an aggregate function so the resulting table has a different structure than the table it is querying from.
The code I use to change the subform record source is:
me![subformname subform].form.recordsource=myfunction()
This has worked for me in the past, but does not work here, I simply get "#Name?" in the subform on my form.
When I open the subform separately I get "#Name?" but when I open the Recourd Source and run the query from the record source I get a value so I am confused.
Any ideas?
Best Answer
Try setting up two subforms - one for each format that you need - and when you swap the underlying recordsets also swap or hide/unhide the relevant subform. Then you can set the controlsource for the relevant controls to the fields that are actually available in the current recordset.