Access VBA: Changing record source of subform

3
votes

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?

3
ms-accessvba
Does your new RecordSource contain all of the fields that your subform needs? For example, if you have a Textbox with a ControlSource of Street_Address is there a column in your new RecordSource that says Street_Address. – Newd
Have you tried: Forms!frmMyMainFormName.frmMySubName.Form.RecordSource = myfunction() – Wayne G. Dunn
I think my problem is that I am trying to change the structure of the subform when I change the Record Source. I originally have two columns, location, and population, and after I change the Recourd source I have an aggregate function that sums the population in the locations I want so the new table has only the column "number of people in locations" – Carl
So your RecordSource was missing a needed ControlSource? – Newd
Make sure your query returns the same names of fields to be used in your subform textboxes -- and that you have the correct number of fields. If old RS produced list of locations and counts, the aggregate is OK as long as you keep location and 'count' fields. – Wayne G. Dunn

3 Answers

0
votes

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.

0
votes

I got it to work by changing the structure of the subform I was changing so that the structure stayed the same when I altered the recordsource. The lesson is, you can change the recordsource in VBA, but you can't change the structure of the subform.

0
votes

My problem occurred because I used an alias in my subform record source SELECT query. When I replaced the alias table names with the actual database table names in my VBA code, the .recordsource = strSQL01 statement worked and the data appeared in my subform.