FindNext with NoMatch

[kronar30]

When only one record matches the search criteria findfirst works fine, however the findNext does not return a nomatch condition. Then fatal error when try to delete non-existent record? NoMatch condition ifs never true??

Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)

'delete this job and all related records
delstrSql = "JB_Deletesw = true"
JBrec.FindFirst delstrSql

Do While Not JBrec.NoMatch
'Call DeleteAllChildRecords



'MsgBox "Found matching Job record"
JBrec.Delete
MsgBox "Deleted " & strQuote & JB_JobBidNo & strQuote & " Bid record"
JBrec.FindNext delstrSql
If JBrec.NoMatch Then MsgBox "findnext Bid is no match"

Loop
Exit Sub

How do I check for no more records match the search criteria?

 

[dhookom]

I would not open the entire table when all I wanted to reference were the records where JB_Deletesw = True.

   Dim strSQL as String
   strSQL = "SELECT * FROM JB_Jobs WHERE JB_Deletesw = true"
   Set db = CurrentDb()
   Set JBrec = db.OpenRecordset(strSQL, dbOpenDynaset)
  'do your other stuff here such as deleting the child records

Typically you would run a single call to a delete query and have referential integrity set so the child records would also be deleted.


Duane
Hook'D on Access
MS Access MVP

 

[Golom]

After you delete a record in a recordset, the cursor position is not necessarily determined and findnext depends on such a positioning.

Just continue to use findfirst since the "next" record will be the first one after you have deleted the one that was the first one.

You can use Duane's recommendation and use a cascaded delete with referential integrity turned on or you can use two SQL statements.

Delete Child Records

Delete * From ChildTable 

Where KeyField IN (Select Keyfield From JB_Jobs Where JB_Deletesw = TRUE)

Then delete Master Records

Delete * From JB_Jobs Where JB_Deletesw = TRUE

 

[kronar30]

Thanks for the help Golom and dhookom.