Delete Query

[JIT]

I would appreciate, some of you can help me in this regard
I have a Customer Complaint database. I want to delete specifice record based on parameters- Customer ID, Record entry begining and Ending date. However, before deleting records, I want to see which records are deleted.

To achieve this, I have created one Select query and one Delete query. I have also created a macro and put above queries one after another in action column. I have attached this macro to a command button in a form. However, this is not working.

Can somebody help me? Thanks in advance!

Jit

 

[Rob.Mills]

How'd you setup the macro?

 

[JIT]

Macro setup

Hello Rob,

Thanks for your reply!
I have first put Open Query-Select, and then Open Query-Delete, in action column. It is not working properly. For example, when macro runs, it asks for prompt and I enter values. When delete query starts, it again asks for prompt. I feel this is not the way to do. My intention is that when I click the delete button in the form, I enter values, and before deleting any records, I should be able to see what records are deleted. I do not know how to translate my intention into code/macro.

I would appreciate, your or anybody's help/suggestion in this regard.

Thanks in advance.

Jit

 

[Rob.Mills]

What I would do is setup a custom dialog box. Create a simple form that has as many unbound textboxes as you have parameters in your queries. Then setup the command button to open this dialog box. Put another button on this form to run your macro. Then in your queries set the criteria to reference each unbound textbox. That way as you open each query it will pull the info it needs from that dialog box.

My question to you is: How did you get the macro to pause. I mean I figured if you set it up to open the select query and then to run the delete query it would do that all at once and wouldn't give you a chance to preview before it runs.

 

[JIT]

Setup Macro

Hello Rob,

I have made a custom dialog form, with unbound textboxes for customerID, begining and ending date. In macro, I have first put Open form-dialog box, then Open select query, and then Open delete query. I have not put anywhere Stop-macro. Where should I put this stop-macro?

Thanks,

Jit

 

[Rob.Mills]

Don't put stopmacro in there. I'm guessing you didn't find a way to pause the macro. So here are my suggestions:

You're going to have to setup three different macros for this. They can be in the same macro but give them different names.

The first one opens the dialog box. The next one is tied to the button on the dialog box which you want to run after you fill in the textboxes. That macro will open the select query. Now you're probably going to have to setup a form to house that select query. Then on that new form another button is set to run the delete query. Confused yet?

Now, is the CustomerID a primary key? If so there's no reason to have dates because the query will always only delete one record. If you want to delete all records between certain dates then don't include the CustomerID field on the dialog box.

Let me know if this all makes sense.

 

[JIT]

Hello Rob,

I have complaint database, consisting of two tables, customer information and customer complaints, with corresponding forms. The tables have one to many relationship. CustomerID is the primary key. In customer complaints form, I enter customer complaint when I receive complaint. I want to add delete button in the customer complaints form and intend to delete records for a particular customer in a date range. I can do this with delete query and attaching this query to command buttom. But I want to see the records to be deleted and click Ok command before any deletion. How can I achieve this?

I really appreciate your spending time to help me in this regard.

Jit

 

[Rob.Mills]

Ok you've got your dialog box. In this form setup 2 buttons. One for opening the select query. Label that one Preview. The other button set to run the delete query and label that delete (or whatever you want).

I think this should take care of what you're trying to do.

 

[JIT]

Thank you very much!

Hello Rob,

I can do this. Could I use one buttom and attach macro to it, which will run two queries, select and delete?

Once again thank you very much for your time.

Jit

 

[Rob.Mills]

Not the way you want to. If you set one macro to run both queries then it will open the select query and then run the delete query before you have a chance to look things over.

However, check to see if in the macro you can open the delete query in view or datasheet mode. This acts like a select query before it runs the delete. BACKUP everything before you try it in a macro though! I've never done it this way.

 

[JIT]

Thanks Rob!

Thanks Rob very much!
At present, I will use two buttons one for select and one for delete.

Jit