Assigning queries to forms as recordsets on the fly

[georgedwilkinson]

Sorry I haven't posted in so long and am showing my laziness by asking this instead of figuring it out myself.

I would like for my users to be able to write a query and then assign the query as the recordsource for a form. This will allow the advanced user to re-use already designed forms/subforms without having a developer make a modification or a whole new set of forms. This is similar to the filtering concepts found here, just the user will be writing the sql instead of the developer.

I envision the user will select his new query in a drop down list which will then refresh the form, using the newly written/executed query to populate the "main" form, essentially allowing highly advanced filtered forms.

(my users are indeed that good and they don't have time to write their own database programs)

Has anybody done this?

 

[Galaxiom]

There are several ways to do it in VBA:

Assign the name of the query to the RecordSource of the form.
Assign the SQL of the query to the RecordSource of the form.
Open a recordset on the query and Set the form's RecordSet property to it.

Doing any of these things will automatically requery the form.

Of course you will need to have controls on the form with the appropriate ControlSource to show the fields. These ControlSources can also be assigned on the fly by parsing the query to get the field names.