Message Box Before Delete 1

[MickJ]

Hi, I have the following code to delete a record from my database, but I want a Message box with yes no options before the record is deleted. I keep coming up with run time errors because I'm putting something in the place. (Can you tell I'm a newbie yet?) Can someone help?
Thanks
MickJ

Private Sub cmdDelete_Click()
On Error GoTo DeleteErr
With datPrimaryRS.Recordset
.Delete
.MoveNext
If .EOF Then .MoveLast
End With
Exit Sub
DeleteErr:
MsgBox Err.Description
End Sub

 

[CajunCenturion]

You might want to try something like the following

Private Sub cmdDelete_Click()

On Error GoTo DeleteErr

Dim Response as Integer

Response = MsgBox("Verify Delete", vbYesNo)
if (Response = vbYes) then
With datPrimaryRS.Recordset
.Delete
If Not .EOF Then
.MoveNext
If .EOF Then .MoveLast
Else
.MoveLast
End If
End With
End If

ImDone:

Exit Sub

DeleteErr:

MsgBox Err.Description
Resume ImDone

End Sub

You are also missing a Resume statement in the error handler. The Resume statement is recommended as it clears the exception event which triggered the event.

You may also be running into a problem with the move next, if you just deleted the very last record in the recordset.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MickJ]

That worked like a charm, Thanks. But If I try to delete two records in succsession I get a multi step error that refers me to this code:

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
datPrimaryRS.Caption = "Record: " & CStr(datPrimaryRS.Recordset.AbsolutePosition)
End Sub

Do I need to refresh the database? Sorry to be a pain...
Thanks MickJ

 

[CajunCenturion]

I don't understand what you mean by multi step error that refers me to this code. Did you write the sub datPrimaryRS_MoveComplete, and if so, how and when is it being called. If its being called from an error handler, then we need to step back to the line of code which originally triggered the error.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MickJ]

Record 1 deletes fine. Then, if I try to delete record 2 a MsgBox says "Multiple-Step Operation Generated Errors. Check Each Status Value" I click OK to close this box then try to move to the next record and get "Run time error 2147217885 (800040e23) Row Handle Referred To A Deleted Row Or A Row Marked For Deletion" I click Debug and it takes me to this code

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record position for this recordset
datPrimaryRS.Caption = "Record: " & CStr(datPrimaryRS.Recordset.AbsolutePosition)
End Sub

I used the VB Application Wizard to create the form with Add and Delete Buttons so this code is part of that. I appreciate your patience.
Thanks
MickJ

 

[CajunCenturion]

The Multi-Step error is most likely caused by an attempt to delete a record that has already been deleted.

I would check for this condition with the following

With datPrimaryRS.Recordset
If (.Status <> adRecDBDeleted) then
.Delete
End If


In your code, you seem to want to MoveLast after delete a record. If the last record has been deleted, then you will always be moving to a deleted record.

Your application may require you to movelast, but if it doesn't, then I would consider doing a movenext until you get to a non-deleted record, and of course, not eof.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Trying to Delete a record which has already been deleted will cause a Multi-Step error, but please disregard my comments about the .movelast. It appears that .MoveLast will move to the last valid record, which make sense.

Private Sub cmdDelete_Click()

On Error GoTo DeleteErr

Dim Response as Integer

Response = MsgBox(&quot;Verify Delete&quot;, vbYesNo)
if (Response = vbYes) then
With datPrimaryRS.Recordset
If (Not .EOF) Then
If (.Status <> adRecDBDeleted) then
.Delete
End If
End If
.MoveLast
End With
End If

ImDone:

Exit Sub

DeleteErr:

MsgBox Err.Description
Resume ImDone

End Sub
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MickJ]

I think we're getting there!!! When I run with your latest code the record deletes then moves to the last record. If I try to delete the last record I get the same old errors but if I move to record 1 I can delete that just fine. So it seems all I need is to add a GoTo FirstRecord as the final line of code (allowing it to go to last then back to first). This is probably not very professional, but I was once told if it works I'm a programmer. I have tried adding .MoveFirst in various places but it doesn't seem to work (therefore I'm not a programmer). Your original code (which didn't move to last record) won't allow me to move to any other record so the latest code is probably what I need to play with, any clues where to put the .MoveFirst?
You have the patience of a saint and I'm very grateful.
MickJ

 

[CajunCenturion]

I wouldn't try to solve this problem with a movefirst. I would find what are the different status codes that which cause errors while trying to delete. out what is causing the problem in deleting the record.

The following list are the Record Status possibilities. We're already checking for adRecDBDeleted, but you may also need to check for adRecDeleted. Or perhaps you may want to delete on if the status is asRecOK.

adRecOK - The record was successfully updated.
adRecNew - The record is new.
adRecModified - The record was modified.
adRecDeleted - The record was deleted.
adRecUnmodified - The record was not modified.
adRecInvalid - The record was not saved because its bookmark is invalid.
adRecMultipleChanges - The record was not saved because it would have affected multiple records.
adRecPendingChanges - The record was not saved because it refers to a pending insert.
adRecCanceled - The record was not saved because the operation was canceled.
adRecCantRelease - The new record was not saved because of existing record locks.
adRecConcurrencyViolation - The record was not saved because optimistic concurrency was in use.
adRecIntegrityViolation - The record was not saved because the user violated integrity constraints.
adRecMaxChangesExceeded - The record was not saved because there were too many pending changes.
adRecObjectOpen - The record was not saved because of a conflict with an open storage object.
adRecOutOfMemory - The record was not saved because the computer has run out of memory.
adRecPermissionDenied - The record was not saved because the user has insufficient permissions.
adRecSchemaViolation - The record was not saved because it violates the structure of the underlying database.
adRecDBDeleted - The record has already been deleted from the data source.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MickJ]

I'm going to have to give up on this one. I'm trying to create a program (and am actually pleased with the progress) but there are a few things that are beyond me, I've gotton some great tips from this forum and that has allowed me to progress as far as I have. But I realize how difficult it is to help by e mail sometimes, what I want/need to do is find someone locally who can check a few things out and assist me one on one (if you know of anyone close to Maine please let me know). I'm almost done with the program now and ready to move onto the reports part, just a few niggly little problems such as this one. I'm really gratefull to you for your help, it works better than it did and I learned a few things in the process.
Best Regards
MickJ