Changing datasheet shown in split form

[Kronix]

I want a split form with a button that changes the source of the data shown in the datasheet. However, when I assign a new SQL statement via Me.Recordsource in the VBA, it only replaces the data in the columns of the current datasheet that have the same name as the Query's columns, and the rest have "#NAME?" How do I make it change the whole table including the number of columns and their titles?

 

[Mark_]

If you are changing the record source, you would need a matching form. If you notice only those fields that you have included on your form will appear on the datasheet.

More to the point, why would you want to change the record source on a form when the user is on it? This would normally be a choice made prior to opening the form.

 

[bastanu]

As Mark pointed out you need to change the source object of the subform control, not the record source of the subform itself.

Cheers,
Vlad