Is it possible to have one form but different SQLs to load data?

[path479]

Is it possible to have a form that would use different SQL statements to load its data depending on which button the user is used to invoke the form? If so, how to achieve this?
I have a form that is used for entering data to the table “Project”. It is also used to display existing data from “Project”. Some of the fields in the form are combo box linked to reference data in other tables.
When the form is to display existing project data, it also display a calculated (aggregated) value from the “Floor” table. “Floor” is a child of “Project”.
When the form is in data entry mode, the SQL needs to be:
SELECT p.proj_id, p.address, …. i.desc, w.desc
FROM (Industry AS i INNER JOIN (Work AS w INNER JOIN Project A p ON w.wk_id = p.wk_id) = p.wk_id) ON i.industry_id = p.industry_id;

However when the form is in displaying existing mode, the SQL needs to be:
SELECT p.proj_id, p.address, …. i.desc, w.desc,
Sum(f.sameFloorPlan_no * f.nla) AS Calculation
FROM (Industry as i INNER JOIN (Work as w INNER JOIN Project as p ON w.[wk_id] = p.[wk_id]) ON i.industry_id = p.industry_id) INNER JOIN Floor as f ON p.proj_id = f.proj_id;
GROUP BY i.industry_desc, w.desc, p.proj_id, ………..

Or should I create two forms that are exactly the same form except the SQL so to cater for the two different scenarios?

 

[Ranman256]

Put the different queries in a table,
Caption, qry

Put a combo box on a form, the user picks the caption
And opens the qry.

Docmd.openQuery cboQry

 

[pbaldy]

I personally never let users into tables or queries, just forms and reports. You can certainly change the record source property of the form to either of those, either using saved queries or SQL.

 

[Wiz47]

I personally never let users into tables or queries, just forms and reports. You can certainly change the record source property of the form to either of those, either using saved queries or SQL.

Could he use a combo box and have the recordsource selected at that time? I use this method to create different reports using the same report form. For example 30, 60, and 90 day queries of the data. I use the combo in the form, then have the report read it from the combo and load it in the report on the onload event.

 

[pbaldy]

Sure. With only 2 options I'd lean towards an option group or toggle button, but a combo is certainly an option.