Deleting Records from recordset

  • Thread starter Thread starter Bob Darlington
  • Start date Start date
B

Bob Darlington

I want to delete certain records from a recordset using:
Set rsBudNew = db.OpenRecordset("SELECT tBudgetNew.* FROM
qBOMACodesUnfiltered INNER JOIN tBudgetNew" _
& " ON qBOMACodesUnfiltered.BOMAID =
tBudgetNew.BOMACounter WHERE PropNum = " & gblPropNum)
With rsBudNew
Do Until .EOF
if ...... then .Delete
.MoveNext
Loop
End With
The recordset is updatable.
But it generates an error saying that there are related records in another
table.
tBudgetNew has no relationship with the table in the error message.
There is a one to many relationship between a table in qBOMACodes and
tBudgetNew

If I run:
db.Execute "DELETE tBudgetNew.* " _
& " FROM qBOMACodesUnfiltered " _
& " INNER JOIN tBudgetNew ON qBOMACodesUnfiltered.BOMAID =
tBudgetNew.BOMACounter " _
& " WHERE qBOMACodesUnfiltered.PropNum= " & gblPropNum,
dbFailOnError
all the records are deleted without a problem.

Can anyone tell me what I'm missing here.
 
Thanks Alex, I'll do that.
Still curious to know why it didn't work through the recordset though.
 
Thanks Alex, but was that the correct reference? It says nothing about
problems deleting from recordsets, which is my problem. I can get the delete
to work through a delete query without a problem.
 
Back
Top