Delete table record in code

[kronar30]

I have a master table 'JB_Jobs' which is linked to several child tables. I have simplified my code to delete the parent record while there are no child records. I keep getting an error condition.

CODE:
Private Sub cmdDeleteRecords_Click()
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
'MsgBox "Found matching Job record"
JBrec.Delete
MsgBox "Deleted " & strQuote & delBid & strQuote & " Bid record"
JBrec.FindFirst delstrSql
If JBrec.NoMatch Then
MsgBox "findnextfirst Bid is no match"
Requery
End If
Loop
If JBrec.NoMatch Then
MsgBox "There are no Bid records to delete"
Call Form_Close
End If

Exit Sub

deleteRecordsError:
MsgBox "deleteRecordsError"
End Sub

Questions:
1 - Now it seems as if it can't find the first matching record (there is only one record that meets criteria)?

2 - can I use JBrec.Delete after JBrec.FindFirst?

3 - How can I capture the error number?

Thanks for any help

 

[dhookom]

Do you really need all the msgbox()s or are they for troubleshooting only?

Why not create a query like:

Dim strSQLDelete as String
strSQLDelete ="DELETE FROM JB_Jobs WHERE JB_Deletesw = -1 and JB_JoborBid = 0"
Currentdb.Execute strSQLDelete, dbFailOnError

If you want to display these first, you can show them in a form or subform.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Duane's suggestion is probably the way to do it rather than messing around with recordsets.

To Answer your questions ...

1 - Now it seems as if it can't find the first matching record (there is only one record that meets criteria)?

If you've already run this then you may not have a matching record any more ... thus nothing to find.

2 - can I use JBrec.Delete after JBrec.FindFirst?

Yes ... Assuming that the FindFirst was successful (i.e. Not JBrec.NoMatch is True) which it should be with your code.

3 - How can I capture the error number?

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

There may be an issue with this

Do While Not JBrec.NoMatch
    [blue]... Your Code ...[/blue]
    JBrec.FindFirst delstrSql
    If JBrec.NoMatch Then
        MsgBox "findnextfirst Bid is no match"
        Requery
    End If
Loop

The Loop termination is when [blue]Not JBrec.NoMatch[/blue] is TRUE. However, you are doing a Requery inside the loop and that will reset the NoMatch flag so loop termination probably won't happen correctly. Insert an Exit Do after the requery to force leaving the Loop.

 

[dhookom]

Actually, if the recordset is needed for some reason, just

Dim strSQL as String
strSQL = "SELECT * FROM JB_JOBS " & _
    "WHERE JB_Deletesw = true and JB_JoborBid = false"
Set JBrec = db.OpenRecordset(strSQL, dbOpenDynaset)

Then just loop through the recordset without worrying about matching.

Duane
Hook'D on Access
MS Access MVP