Is there a way to filter MS Access queries by multiple forms without creating multiple queries?-CodePudding

I maintain an MS Access database that manages forecasting, inventory, and purchasing.

Sometimes I need to update forecasts or inventory for all items, sometimes I just need to update for a single warehouse, and sometimes I need to update for a single item.

These processes are controlled by forms, and usually start with deleting existing tables, so I'll focus on those three queries.

To delete all, the user presses the button for all which runs the following:

DELETE Forecast_Days.*
FROM Forecast_Days;

To delete all records for a specific warehouse, the user presses the button on the line for that warehouse:

DELETE Forecast_Days.*
FROM Forecast_Days
WHERE [Forecast_Days]![Whse]=[Forms]![frm_Forecast_Update]![Whse_t];

To delete a single record for a single item, the user presses the button in a different form for that specific item:

DELETE Forecast_Days.*
FROM Forecast_Days
WHERE [Forecast_Days]![Whse]=[Forms]![frm_Forecast_Item_Edit]![Whse_t] AND 
      [Forecast_Days].[Item]=[Forms]![frm_Forecast_Item_Edit]![Item_t];

This is just the first step in a series of processes. For each process, I need to create an _all, _whse, and _item query; the only difference being the WHERE statement.

To reduce the clutter and simplify maintenance: is there a way I can set this up so that I am injecting the WHERE statement to the base query from the button.

Or is there a way to write a single query that looks at all potential parameters? My struggle with this approach is that if I push the button from form A, I don't want the query prompting the user for the values that normally come from form B.

CodePudding user response：

@HansUp provided the general direction I needed. I figured I would post an answer and close this thread.

Assuming the following is a saved query named "Delete_Forecast_Days"

DELETE Forecast_Days.*
FROM Forecast_Days

In VBA, I can do the following:

   Dim whereStr as String
   Dim sqlStr as String

   whereStr = " WHERE Whse='" & [Forms]![frm_Forecast_Update]![Whse_t] & "';"
   sqlStr = CurrentDb.QueryDefs("Delete_Forecast_Days").SQL
   sqlStr = left(sqlStr, Len(sqlStr) - 3) & whereStr
   CurrentDb.Execute sqlStr, dbFailOnError

This will grab the SQL from a saved query, remove the ";" at the end, then append the where statement I want.

Ultimately, I decided against this approach, because I didn't want to find myself in 6 months changing a query and getting unexpected errors because the WHERE statement I wrote in VBA doesn't work with that query anymore.