Command button issue

[sloppysly]

I am trying to set up a command button in my form that when clicked will delete the record from my form and append it to an archive table. I have tried doing this via VBA and it works but it takes forever to run. So I have tried to set up a macro to do this, which also works but it requires you to click the button, go to the next record, go back to the desired record and click the button again (works much faster than the VBA code). The macro does the following once clicked it first sets the value of the delete field to delete and then runs the append query then runs the delete query (for records with a value of delete in the delete field). I am not sure why I have to change records and click the button again. Does anyone know of a way to do accomplish this task without changing records. Any help you can provide would be greatly appreciated.



Thanks,
Tim

[sloppysly]

Here is the VBA code that I had set up if it helps.

Private Sub Delete_Account_Click()
On Error GoTo Err_Delete_Account_Click
Me.delete.Value = "Delete"

Dim db As DAO.Database
CurrentDb.Execute "AppendFromUnixUserID", dbFailOnError
CurrentDb.Execute "DeleteFromUnixUserID", dbFailOnError

Exit_Delete_Account_Click:
Exit Sub
Err_Delete_Account_Click:
MsgBox Err.Description
Resume Exit_Delete_Account_Click
End Sub

[RuralGuy]

I'm more curious why the "VBA" takes so long. How about sharing the SQL for each query with us?

[sloppysly]

Here is the append query SQL

INSERT INTO DeleteTable ( [ACR Number], [Date Requested], [Generic Account], [account location], [Request Type], [resource id], [resource name], [Account Owner Name], [Account Owner Corp ID], [Business Justification], Comments, [Password Length], [Password Composition], [Frequency of Change], Interactivity, [Generic Account Category], [Users of Non-Corp Id], [Last Updated by], [Last Updated], [Delete] )
SELECT Triage.[ACR Number], Triage.[Date Requested], Triage.[Generic Account], Triage.[account location], Triage.[Request Type], Triage.[Resource ID], Triage.[Resource Name], Triage.[Account Owner Name], Triage.[Account Owner Corp ID], Triage.[Business Justification], Triage.Comments, Triage.[Password Length], Triage.[Password Composition], Triage.[Password Frequency of Change], Triage.Interactivity, Triage.[Generic Account Category], Triage.[Users of Non-Corp Id], Triage.[Last Updated by], Triage.[Last Updated Date], Triage.Delete
FROM Triage
WHERE (((Triage.Delete)="Delete"));

Delete query SQL

DELETE Triage.[ACR Number], Triage.[Date Requested], Triage.[Generic Account], Triage.[account location], Triage.[Request Type], Triage.[Resource ID], Triage.[Resource Name], Triage.[Account Owner Name], Triage.[Account Owner Corp ID], Triage.[Business Justification], Triage.Comments, Triage.[Password Length], Triage.[Password Composition], Triage.[Password Frequency of Change], Triage.Interactivity, Triage.[Generic Account Category], Triage.[Users of Non-Corp Id], Triage.[Last Updated Date], Triage.Delete
FROM Triage
WHERE (((Triage.Delete)="Delete"));

[RuralGuy]

I assume you know the Delete query can look like:
DELETE *
FROM Triage
WHERE (((Triage.Delete)="Delete"));
...I see nothing in the Append query out of the normal. Have you tried running it by itself directly? Using VBA to run it should have *no* effect on the speed.

[sloppysly]

i have run the VBA and the speed is ok. But i have the same issue with the VBA as i do with the macro. For the delete to be completed I need to click the button, go to the next record, go back to the record I want deleted and click the button again. I don't understand why I need to go the next record and back, am I missing something in my VBA code.

[RuralGuy]

Aside from the fact that your field/control name is a Reserved Word, the problem you are having is because the current record has not been saved yet. Moving off of the current record saves it.

Private Sub Delete_Account_Click()
   On Error GoTo Err_Delete_Account_Click
   Me.Delete = "Delete"
   DoCmd.RunCommand acCmdSaveRecord
   CurrentDb.Execute "AppendFromUnixUserID", dbFailOnError
   CurrentDb.Execute "DeleteFromUnixUserID", dbFailOnError
Exit_Delete_Account_Click:
   Exit Sub
Err_Delete_Account_Click:
   MsgBox Err.Description
   Resume Exit_Delete_Account_Click
End Sub

[sloppysly]

Thanks RuralGuy once i put in the save command it worked instantanously with no issues I appreciate the help.

Tim

[RuralGuy]

Glad I could help. So this thread is solved? Marking SOLVED is one of the thread tools.