Delete query not working in Macro

[Cynthia]

This is my first post and I'm not sure whether this should be in the Queries section.

Using XP and Access 2007
I want to transfer information from one table to another, then delete it from the first table.
I have written a macro which does these actions
1. Hourglass On
2. Open Query (Append query to write to the second table)
3. Close that Query (Don't know whether I need to do that but I'm getting desperate!)
4. Open Query (Delete query to remove data from the first table)
5. Close that Query
6. Close a form
7. Open a different form
8. Go to record (Next)
9. Close another form
10. Hourglass off.
Everything works except the Delete Query. What am I doing wrong?

 

[PNGBill]

Copy the delete query and give it a test name.

Turn it into a select query and confirm it selects the record/s you want to delete.

If it doesn't and the data is still in the table. ie it should, then the problem is in your query.

If it does select the record/s then there may be something in the Macro steps but your explanation of your steps appears to be straight forward and simple.
May wish to add turning off the warnings at the beginning and then back on at the end, when it works properly.

 

[vbaInet]

Welcome to AWF

Why are you needing to delete a query? Or do you mean delete the records FROM the query after appending it to a table?

 

[SteveSchapel]

Cynthia,

Firstly, a comment not directly related to the main question... Steps 3 and 5 achieve nothing, and can be removed from your macro, since the queries in this case being action queries, are not actually open after they have run, so already "closed" and no need for you to explicitly do that.

I can see no reason for the failure of the Delete query. Can you test the Delete query (on a backup of your database!) by running it manually, and check that it actually deleted the required records? When the macro runs, do you get an action query notification message to say "You are about to delete (x) records from the table..."? In which case, what exactly do you mean by the delete query doesn't work... that step in the macro doesn't seem to run at all, or it does run but doesn't delete the required records?

Can you post the SQL view of the Delete Query in your reply - that may give us a clue to the problem?

 

[Cynthia]

Thank you all. I added steps 3 and 5 in case the queries were not resetting but I didn't think I needed them. Cheers.

How do I get into the SQL part in Access 97? I'm new to that and haven't got used to it yet.

The macro does seem to run right through, it does the bits after the Delete query line so I quess the query opens but the data remains in the table.

I have also tried to run it manually, but the data remains.

The delete query has 2 columns. The 1st is the ID field setting the criteria for the data. The 2nd is the * field. I thought I did try adding all the fields individually but it didn't work. I am sceptical about the ID field, it would be included twice so might that be my prob?

I have just replicated the Delete query as a select query and it doesn't pull up my data so I have something wrong at a very basic level.

I have a 'Check' form to ask for confirmation of intention to delete. The 'yes' button then runs the macro originally described. The field in the delete query specifies [Forms]![F_Check]![ID] That should work, shouldn't it?

Sorry if I ramble, it's 10.30p.m.

 

[Cynthia]

Found it! The 'Check' form is based on a query that looks at another form for specific data. For some reason the query has called the ID field 'Table1.ID'
I do believe that a bit of a mod there will fix it.
Yes, I think that was it. Will test it properly in the morning.
Thank you for prompt replies.

 

[vbaInet]

Ah, you meant a Delete Query. I have no idea why I thought you wanted to delete a query

Looks like Steve sorted you out.

 

[Vergy39]

This is my first post and I'm not sure whether this should be in the Queries section.

Using XP and Access 2007
I want to transfer information from one table to another, then delete it from the first table.
I have written a macro which does these actions
1. Hourglass On
2. Open Query (Append query to write to the second table)
3. Close that Query (Don't know whether I need to do that but I'm getting desperate!)
4. Open Query (Delete query to remove data from the first table)
5. Close that Query
6. Close a form
7. Open a different form
8. Go to record (Next)
9. Close another form
10. Hourglass off.
Everything works except the Delete Query. What am I doing wrong?

Hi Cynthia, I am trying to create something exactly what you describe here. Unfortunately, I am a novice and do not quite understand your steps. Basically, I just want to be able to click on a delete box, and have it remove the item and place it on another table named "Deleted Items". Having lots of trouble though. Any assistance is greatly appreciated.

Thanks
David V.

 

[Cynthia]

Hi David

I don't profess to be an expert either but this is what I have done, which works now! (I had made a basic error)

Begin by making a table to exactly mirror the table you are deleting from, except..

do not have a 'key' field
do not have a field that automatically generates IDs or other numbers, just set it to number

Then create an append query to copy the information across. If you use the * field, add another field to identify which record to delete. Clear the 'tick' box so that you don't duplicate the data.

Create a delete query. (As above) Check it out in datasheet view to make sure you've got it right, then click the Delete query icon to turn it into a delete query.

Then you need to write a macro which will run when you click your 'delete' button .

Just open the append query before the delete one.

I'm sure some of the real experts on here will explain this better than I, not least it's after dinner here and I do enjoy a glass or 3 with mine.

Hope it goes well, let me know if not and I'll try again!!!

 

[Vergy39]

Hi David

I don't profess to be an expert either but this is what I have done, which works now! (I had made a basic error)

Begin by making a table to exactly mirror the table you are deleting from, except..

do not have a 'key' field
do not have a field that automatically generates IDs or other numbers, just set it to number

Then create an append query to copy the information across. If you use the * field, add another field to identify which record to delete. Clear the 'tick' box so that you don't duplicate the data.

Create a delete query. (As above) Check it out in datasheet view to make sure you've got it right, then click the Delete query icon to turn it into a delete query.

Then you need to write a macro which will run when you click your 'delete' button .

Just open the append query before the delete one.

I'm sure some of the real experts on here will explain this better than I, not least it's after dinner here and I do enjoy a glass or 3 with mine.

Hope it goes well, let me know if not and I'll try again!!!

Thanks Cynthia, Your directions helped me get the tables and queries set up. I now have an append and delte query. My problem now creating a button that will run both queries. Unfortunately, my knowledge of access forces me to use the wizard when creating a button. The wizard will only allow me to run one query. I tried playing around with the code to see if I could get it to run the delete query as well, but to no avail. Her is what I have. The BOLD part is what I added to run the delete query.

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click
Dim stDocName As String
stDocName = "Move Record"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Delete Record"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command36_Click:
Exit Sub
Err_Command36_Click:
MsgBox Err.Description
Resume Exit_Command36_Click

End Sub


Any asistance is greatly appreciated.

Thanks
David V.

 

[SteveSchapel]

David,

If "Delete Record" is indeed the valid name of an existing query that is supposed to delete the required record, then the code you have should work. If it doesn't, you may need to more fully explain what "to no avail" means.

 

[Vergy39]

David,

If "Delete Record" is indeed the valid name of an existing query that is supposed to delete the required record, then the code you have should work. If it doesn't, you may need to more fully explain what "to no avail" means.

Thanks Steve, you are right, when I ran it again, it worked. Well, That was my test method, now I would like to add this to the real database, but want to use it on a submit button that is already built. I tried copying the code into this button, but get this error.

"Duplicate output destination 'Delte'."

Here is the code for this button.

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String
stDocName = "Move Record"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Delete Record"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Close
DoCmd.OpenForm "TblIssues by Writer", acNormal, "", "", acFormEdit, acWindowNormal

Exit_Command14_Click:
Exit Sub
Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub


I really do appreciate your assistance.

Thanks
David V.

 

[Cynthia]

I just use the macro design, not using a wizard. (I have found that it can give problems when moving a db to another computer but that maybe just me?)

Add a line for each process you want. First the Append query, then the delete one.

Add a command button to your form and use the 'on Click' line of the property box to select the macro.