Error Code: The command or action 'DeleteRecord' isn't available now

[MHamid]

Hello,

I have the code below set up within a command button. I need to move the record to another tableand then delete it from the current table. I am able to get the code below towork one piece at a time, but it’s not working combined into a click of thebutton. I am getting the error message “Command or Action ‘DeleteRecord’ isn’tavailable now”. When I click the button, it will archive the record, but itwill not delete the record. Any ideas?

[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub Command577_Click()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Archive Record[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim strSQL As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]strSQL = "INSERT INTO archive_RET_Accumulated([REF_NUM], [ExamID], [Exam Name], [Exam Agency], [OCC SL No], [Exam Objective], [Primary ExamScope DSMT Managed Segment], [Field Work Start Date], [Field Work CompleteDate], [Exam Flag], [Exam Category], [Exam Subcategory], [Exam Owner], [ExamStatus], [Primary Exam Scope DSMT Managed Geography], [Senior Owner],[O&T_Impact], [Exam_Owner_Group_Name], [Exam_Owner_DSMT],[Senior_Owner_Group_Name], [Senior_Owner_DSMT], [RET_Accumulated_Flag], [RET_Accumulated_Added_Date])"& _[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    "VALUES ('" &Me.REF_NUM & "',  '" &Me.Exam_ID & "', '" & Me.Exam_Name & "',  '" & Me.Exam_Agency & "','" & Me.OCC_SL_No & "', '" & Me.Exam_Objective &"', '" & Me.Primary_Exam_Scope_DSMT_Managed_Geography &"', '" & Me.Field_Work_Start_Date & "', '" &Me.Field_Work_Complete_Date & "', '" & Me.Exam_Flag &"', '" & Me.Exam_Category & "', '" &Me.Exam_Subcategory & "', '" & Me.Exam_Owner &"','" & Me.Exam_Status & "', '" &Me.Primary_Exam_Scope_DSMT_Managed_Geography & "', '" & Me.Senior_Owner& "', '" & Me.OT_Impact & "', '" &Me.Exam_Owner_Group_Name & "', '" & Me.Exam_Owner_DSMT &"', '" & Me.Senior_Owner_Group_Name & "', '" &Me.Senior_Owner_DSMT & "', '" & Me.RET_Accumulated_Flag &"', '" & Me.RET_Accumulated_Added_Date & "');"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]DoCmd.SetWarnings False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]DoCmd.RunSQL strSQL[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]DoCmd.SetWarnings True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]MsgBox "Record has been archived"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Delete Record[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Const DELETE_CANCELLED = 2501[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     On Error Resume Next[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     RunCommand acCmdDeleteRecord[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     Select Case Err.Number[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         Case 0[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         ' no error[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         Case DELETE_CANCELLED[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         ' anticipated error, soignore[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         Case Else[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         ' unknown error, soinform user[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]         MsgBox Err.Description,vbExclamation, "Error"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     End Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub

Please note: there has been an issue when I type directly in the forum.So I have been typing these messages in word and copy/pasting into the forum.So some of my sentences/words may be missing spaces. That is not how it’s beingtyped it’s just how it’s being pasted for some reason.

Thank you

 

[stumac]

It looks like you are trying to move a record from one table to another table with exactly the same structure - if this is correct then you can avoid all of the mapping of fields with the example below:

Dim AppendSQL As String
Dim DelSQL As String

AppendSQL = "INSERT INTO archive_RET_Accumulated SELECT * FROM RET_Accumulated WHERE [COLOR=#ff0000]REF_NUM[/COLOR] = " & [COLOR=#ff0000]Me.REF_NUM[/COLOR]
DeleteSQL = "DELETE FROM RET_Accumulated WHERE WHERE [COLOR=#ff0000]REF_NUM[/COLOR] = " & [COLOR=#ff0000]Me.REF_NUM[/COLOR]
DoCmd.RunSQL AppendSQL
DoCmd.RunSQL DeleteSQL

MsgBox "Record Archived"
Me.Requery 'Requery the form

This requires a unique identifier for the record (I have assumed REF_NO?)
I would recommend you test this without the 'DoCmd.RunSQL DeleteSQL' line and if the record is copied add the delete in.

 

[MHamid]

Hello,

In you example, would I need to write out all of the fields or just the unique identifier field?

Thank you

 

[MHamid]

Hello,

I used the code and it works perfectly.
Would it be possible to use this same code to add the record to another table with different fields?

Thank you