Button on a form that forces you to select a set of criteria before running a macro

[adray13]

Hi,



I am a complete novice in Access, but I will try to explain this the best way I can...

I have a table called afm_seat where one of the fields(columns) is named space budget. The space budget is the name of the scenario that the data belongs to. So, there can be many lines of data with the same space budget name because they are part of the same scenario. We will eventually be adding other scenarios with data as time goes on (they have not been created yet because it on a as needed basis).

In the event where we need to scrap all the data for a scenario in the table I need to have a delete query that will delete all lines of data for a specific space budget. I know how to make delete query although, I do not know how set the criteria to be specified by the user without going into Design View each time and changing the criteria. Is there a way to allow the user to type the name of the space budget they want to delete upon running the query?

After I create this query I need to have a button on a form(that I have already created) that runs a macro which runs the delete query(I know how to create a macro that runs a query) and the button will need to ask you to specify the space budget you would like to delete in the specific table(this is what I need help with). I don't know that a drop down box will be useful because we will be adding more space budget names over time (unless there is a way that it automatically populates new space budget names as you add them).

So, in other words I need a button on a form that will ask you what space budget you want to delete in the afm_seat table and then delete all data where the space budget equals it.

Thank you so much for any help!

[pbaldy]

I personally would use a form to have the user input the value. You can use a drop down, presuming the list can be queried from the database somewhere. At the very simplest you could use brackets:

DELETE * FROM TableName WHERE SpaceBudget = [Enter Space Budget]

But I don't like the lack of control with that method.

[adray13]

Thanks for your reply. How would I use a form to have them input the value?

[pbaldy]

You point the criteria in the query to the form (you can use the Build function). Then have a button on that form run your query, after testing for valid input or whatever is appropriate to your situation.

[June7]

If you want to control record deletion, need to also handle the action from keyboard Delete key. What I do:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
'Suppress default Delete Confirm dialog box.
Response = acDataErrContinue
'cancel the automatic delete operation
Cancel = True
MsgBox "Must click Remove Test button to delete test from sample." & vbCrLf & _
"If sample is in closed accounting period, Delete/Remove test not permitted.", , "Delete"
End Sub