Deleting a record with VB

[Courtman]

Hi

I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.

The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings True
End If
but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?

 

[ghudson]

I have seen where users needed to select the current record. See if this helps...

If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
    DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
Else 'user clicked no
    MsgBox "Deletion was aborted.", vbInformation
End If

 

[Courtman]

Nope, still comes up with the same error and the debug comes back on the DoCmd.RunCommand acCmdDeleteRecord line...

And in answer to your second question, I guess undo. The controls on the form are bound to the table though (this is probably messy but its the way its always been), so I kinda thought I'd have to delete the record. Undo might be easier?

 

[ghudson]

Do you want to UNDO the current record changes or DELETE the current record?

If MsgBox("Are you sure you want to abandon [undo] changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
    DoCmd.RunCommand acCmdUndo
Else 'user clicked no
    MsgBox "Undo was aborted.", vbInformation
End If

 

[Courtman]

Thanks for that, worked a treat. Funny how you can stare at a problem for ages, and the solution is soooo simple...

I did the AcUndo command, then close the form and re-open it to refresh it. Is there a way of clearing all the fields in the form without the close and re-open line?

Also, I use the same for the "Close Log" button - i.e. a pop-up which says Changes will be lost if you close now. If there haven't been any inputs made into the form, I get an error that the Undo command cannot be used at the moment. Is there a way of checking to see if there have been any inputs, and if not just close without the warning pop-up?

 

[ghudson]

To refresh the record set you need to requery the form.

Me.Requery

Here is the code I use to Undo a record...

    'Resets the record if it has been modified by the user.
    If Me.Dirty Then
        DoCmd.RunCommand acCmdUndo
    Else
        MsgBox "There were no modifications made to the current record.", vbInformation, "Invalid Undo"
    End If

You can move to a new record if you want to "clear" all the fields.

DoCmd.GoToRecord , , acNewRec

 

[Courtman]

Thanks, all works fine - much appreciated and much tidier than anything I've tried before!

 

[misscrf]

I saw this post, and tried some of the recommendations to solve the same issue. I added the code to 'DoCmd.RunCommand acCmdSelectRecord', but I am still having this odd problem.

I have delete code, but when I click it, the record is not really deleted. For a momentit does seem to work. I can cycle records - it is gone. If I close the main form (go to the main menu) and reopen the form, the record is back there again. Why is it 'coming back'? I just deleted it lol.

I will post my code here. I don't know why the delete isnt sticking. BTW this main form/record has 5 subforms/records(on a tab control) and the relationships are all 1 - many with cascade update and delete.

Here is what I have:

Private Sub cmdDeleteCand_Click()
On Error GoTo Err_cmdDeleteCand_Click

DoCmd.SetWarnings False
Dim Msg, Style, Title, Response
Msg = "Are you sure that you want to delete this candidate?  You will not be able to undo this action."    ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Warning! About to Delete Record"    ' Define title.
Response = msgbox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes.
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Me.Requery
DoCmd.GoToRecord acDataForm, frmCandidateEntry, acNewRec
'    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
'    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70   ' Perform some action.
Else    ' User chose No.
    DoCmd.CancelEvent    ' Perform some action.
End If

Exit_cmdDeleteCand_Click:
    Exit Sub

Err_cmdDeleteCand_Click:
 If Err.Number = 3021 Then
    DoCmd.SetWarnings False
End If
    Resume Exit_cmdDeleteCand_Click
 End Sub

as you can see, with my testing to fix this, I had:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

But thenI tried this:

DoCmd.RunCommand acCmdDeleteRecord

the same behavior happens. If anyone can help, I would appreciate the help.

Thanks!

p.s. I checked the form(s) properties and allow deletions is allowed

 

[accessman2]

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord


How can I delete the current record without asking?

But, I don't have any field value to specify.

Just delete current record as same as the above commands, but it needs to ask whether or not you delete the record.

How can I skip asking. Thanks.

 

[ghudson]

Accessboy,

The answer is right in front of you. I have exactly what you need in my first posting in this thread.

Once again, taking the time to search the forum would help you discover and learn the answers to your Access programming questions [instead of wasting our time].