Having trouble with the delete code!

[cfd33]

Once again I'm having trouble.

I used the code from Beacon's tutorial :

Private Sub cmdDelete_Click()    
If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      
'check if you really want to delete this record        
Exit Sub 'exit the command    
Else        
    If Not (rs.BOF = True Or rs.EOF = True) Then            
        rs.Delete 'delete the current record            
            If Not (rs.BOF = True Or rs.EOF = True) Then                
               rs.MoveNext 'move next            
                  If rs.EOF Then rs.MoveLast                
                  fillfields            
           End If        
    End If    
End If
End Sub

but everytime I try to delete the record I get the 3021 error.

"Either BOF or EOF is True, or the current record has been deleted.

[Hack]

On what line does it occur?

Does the record get deleted?

[cfd33]

Sorry about forgetting to include the line that gets the error.

Its the .MoveLast line and yes the record gets deleted.

[Hack]

Ok. I sort of tossed a mental coin between MoveNext and MoveLast and picked MoveNext. I was close.

However, I just knew it had to be one of those two.

Question number 1: Why are you using either of them?

[cfd33]

2 reason's:

1. It was in the tutorial

2. I would like the gui to reflect that the record is gone. Right now, if I get the error and close the program and restart the program the record is gone.

I would like it so that when the user deletes the record it will disappear from the gui and shows another record instead.

I hope I made sense

[Hack]

Ok....what is your GUI? How are the records being displayed now?

Typically, I will load a ListView or ListBox or something via a Sub routine. When I delete a record or update a record, I simply clear out the control, and call that Sub again.

[cfd33]

My gui consist of txtboxes which are populated by the recordset.

I thought maybe the fillfields would repopulate the boxes or refresh the txtboxes, but I guess not.

So, you are saying that I should clear out the txtboxes and then recall the recordset?

I'll give that a try

[Hack]

So, you are saying that I should clear out the txtboxes and then recall the recordset?

Thats what I would do....

[cfd33]

Ok, this is what I have done, but it is not working, I get another error.

Row handle referred to a deleted row or a row marked for deletion.

Private Sub cmdDelete_Click()    
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      
'check if you really want to delete this record        

         Exit Sub 'exit the command    
   Else            
          If Not (rs.BOF = True Or rs.EOF = True) Then             
                rs.Delete 'delete the current record                        
                                       
          End If            
   End If 
   Clearboxes
   Fillfields   
End Sub

The error points to the first part of the Fillfields code

[Hack]

The error points to the first part of the Fillfields code

What is that code?

[cfd33]

Hack,

Here is the entire project.

Woops, forgot to save the changes, here is the saved project.

[cfd33]

I think I have got it Hack,

I didnt think about opening the recordset again. I thought I could just call it with the "Fillfields" sub.

I opened a new rs and now it appears to be working.

Yep, its working great now!! Thanks Hack. This is what I have done:

Private Sub cmdDelete_Click()
    If MsgBox("Deleting this record will completely remove it from the database!" & _
    vbCrLf & "Are you sure you wish to delete this record?", vbQuestion + vbYesNo, "Delete Record") = vbNo Then
        Exit Sub
    Else
        If Not (rs.BOF = True Or rs.EOF = True) Then
            rs.Delete
        End If
    End If
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    strSQL = "Select * From MT Order By ID ASC"
    
    rs.Open strSQL, strConnection, adOpenDynamic, adLockOptimistic
    
    ClearBoxes
    If Not (rs.BOF = True Or rs.EOF = True) Then
        rs.MoveNext <----I'm not sure, but I think I dont need this since my sql takes care of the order
        Fillfields
    End If
End Sub

[Fishy]

Hi, Tou Could try this

If Not (rs.BOF = True Or rs.EOF = True) Then
            rs.Delete
            rs.Requery 'Add This
        End If

Fishy

[cfd33]

Fishy,

Yes I tried that early on and it didnt work.

[Fishy]

Strange, It does the same thing and works OK for me.

Fishy

[cfd33]

Hmmm,

I'll try it again and see what happens.

Are you using my project?

[Fishy]

Yes.

[cfd33]

Did you keep the code as is with the exception of adding rs.requery?

Would you post what you have done. I tried it and it wil delete ok and clear the boxes, but wont repopulate.

[Fishy]

All I added was the rs.requery and in the properties of the project
I had to remove the Exel reference but I had to add the Active X
Data Objects 2.8 Library

Fishy.

[cfd33]

But if I remove the Excel reference, then I wont be able to send my datagrid to the excel worksheet.

[Fishy]

Sorry, I don't have excel and it wont compile.

[cfd33]

Ok, thanks anyway.

It's working with the rs.open; so I think I'll go with that for now.

[Fishy]

Just one thing in your zip file you had a .exe of the program
(Bit naughty should not include exe files) that works OK deletes
the records and refreshes the form with next record. I think you
have some corruption in your project somewhere.

Fishy

[cfd33]

Oh yeah, I forgot to remove that exe. It was from compiling the program the first time to see if it would work ok.

That's funny that it works fine for you. How can I fix the corruption?