Delete set of records using button on a form

MarcieFess · January 21st, 2013, 05:16 PM

Hello all. I'm new to this forum. I'm learning Access 2010 "by fire", having been hired through an office staff temp agency to develop an Access application.

I have a form with 2 combo boxes where the user selects first a company, then a store location within that company. From there they can generate reports for the specific stores.

They have requested that they also have a button that will allow them to delete all product information for a particular store, so they assure that the quantities for all products are zeroed out.

The reason they want to completely remove all records for a store, instead of setting case quantities to zero, is that they will be importing the new information from an Excel spreadsheet and want to be assured that when Access finds a matching record, that it doesn't just skip over the import of the new quantities (essentially meaning that there would be no products in the store).

I've created a delete query:

Code:

DELETE tblStoreProducts.StoreProductKey, tblStoreProducts.UPC, tblStoreProducts.StoreKey, tblStoreProducts.Cases, tblStoreProducts.Storage, tblStoreProducts.Use, tblStoreProducts.AnnualWaste, tblStoreProducts.ProductKey
FROM tblStoreProducts
WHERE (((tblStoreProducts.StoreProductKey)=[Forms]![frmChooseStore]![cboStore]));

My problem is that Access won't let me create an event that calls this query.

Any suggestions?

Marcie Fessler

rampantlion · February 3rd, 2013, 08:41 PM

Marcie, is this a stored query? If so, you will need to modify it to accept Parameters:

PARAMETERS cboStore Text;
DELETE tblStoreProducts.StoreProductKey, tblStoreProducts.UPC, tblStoreProducts.StoreKey, tblStoreProducts.Cases, tblStoreProducts.Storage, tblStoreProducts.Use, tblStoreProducts.AnnualWaste, tblStoreProducts.ProductKey
FROM tblStoreProducts
WHERE (((tblStoreProducts.StoreProductKey)=cboStore));

I assumed cboStore is Type = Text. If it is Long, replace 'Text' with 'Long'. In the VBA behind you button's On Click event, you'll need to Dim your QueryDef and assign the proper value to the Parameter:
Dim dbsDeleteProducts As DAO.Database
Dim qdfDeleteProducts As DAO.QueryDef
Set dbsDeleteProducts = CurrentDb
Set qdfDeleteProducts = dbsDeleteProducts.QueryDef("YourStoredQuerysNameHe re")
qdfDeleteProducts.Parameters("cboStore").Value = Me.cboStore.Value
DoCmd.SetWarnings False
qdfDeleteProducts.Execute dbFailOnError
DoCmd.SetWarnings True
Set qdfDeleteProducts = Nothing
Set dbsDeleteProducts = Nothing

Always be careful when designing/testing a DELETE query, as they are very powerful. Good luck.
Jay