after I delete a record, requery fails?

[kronar30]

When I delete a record and hit the 'Me.Requery' statement, it goes straight to the error routine and displays 'Recodr is deleted' (no err.number displays).
how do I show the table with the deleted records no longer showing?


Private Sub cmdDeleteRecords_Click()
'CAN'T GET THE REQUERY FORM TO WORK
Dim cnt As Integer
Dim delBid As String
On Error GoTo deleteRecordsError
Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)

'delete this job
delstrSql = "JB_Deletesw = true and JB_JoborBid = false"
JBrec.FindFirst delstrSql

Do While Not JBrec.NoMatch
delBid = JBrec(0)
MsgBox " Ready to delete bid " & strQuote & delBid & strQuote
JBrec.Delete
JBrec.FindFirst delstrSql
Loop

MsgBox "There are no Bid records to delete"
txtDeletesw.SetFocus

cmdDeleteRecords.Visible = False
Me.Requery
'get error here after requerry "record already d4eleted"
'Call Form_Close


Exit Sub

deleteRecordsError:
MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number
End Sub

 

[seaport]

I have doubt about the location where the error occurred. Because this is a jet error (not a VBA run-time error), it is hard to pin-point where the error is raised.

I think the problem is somewhere in the code

'delete this job
    delstrSql = "JB_Deletesw = true and JB_JoborBid = false"
    JBrec.FindFirst delstrSql
    
    Do While Not JBrec.NoMatch
        delBid = JBrec(0)
        MsgBox " Ready to delete bid " & strQuote & delBid & strQuote
        JBrec.Delete
        JBrec.FindFirst delstrSql
    Loop

Instead using loop to delete, my suggestion is to just run a sql delete.

db.execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false"
[code]

Hope this helps.

 

[kronar30]

tried using your sugestion. however it still goes to the error routine displays "deleteRecordsError: -0"


code:
Private Sub cmdDeleteRecords_Click()
On Error GoTo deleteRecordsError

Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)
'Stop
db.Execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false"

Requery

deleteRecordsError:
MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number

End Sub

 

[TheAceMan1]

How are ya kronar30 . . .

Curious why your looping thru a reordset instead of running a delete query or SQL ... aka a one-shot deal!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[seaport]

Your error-handling is not right. It displays error message regardless there is an error or not.

The code should be

Private Sub cmdDeleteRecords_Click()    
On Error GoTo deleteRecordsError        
Set db = CurrentDb()    
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset) 'Stop    
db.Execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false"        
Requery    

ex:
exit sub

deleteRecordsError:    
MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number
resume ex

End Sub