Access VBA: Changing record source of subform

Question

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?

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
– Newd, Commented Jun 15, 2015 at 18:31
Have you tried: Forms!frmMyMainFormName.frmMySubName.Form.RecordSource = myfunction() — Wayne G. Dunn
– Wayne G. Dunn, Commented Jun 15, 2015 at 18:32
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
– Carl, Commented Jun 15, 2015 at 18:50
So your RecordSource was missing a needed ControlSource? — Newd
– Newd, Commented Jun 15, 2015 at 19:09
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
– Wayne G. Dunn, Commented Jun 15, 2015 at 19:12

jhTuppeny · Accepted Answer · 2015-06-23 16:43:22Z

0

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.

answered Jun 23, 2015 at 16:43

jhTuppeny

9901 gold badge12 silver badges16 bronze badges

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

Comments

Carl · Accepted Answer · 2015-06-23 18:15:15Z

0

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.

answered Jun 23, 2015 at 18:15

Carl

5,7897 gold badges45 silver badges83 bronze badges

Comments

pushkin · Accepted Answer · 2018-09-28 20:35:37Z

0

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.

edited Sep 28, 2018 at 20:35

pushkin

10.4k16 gold badges65 silver badges112 bronze badges

answered Sep 28, 2018 at 20:13

lstevens9524

11 bronze badge

Collectives™ on Stack Overflow

Access VBA: Changing record source of subform

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related