Re: Deleting Records from recordset

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks Alex.
Tried that but still same result.

--
Bob Darlington
Brisbane
"Alex Dybenko" <alexdyb@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:46A28C61-0EE7-445F-B2B7-416FA69E3025@xxxxxxxxxxxxxxxx
Hi,
try to put table tBudgetNew as first one in join expression:

FROM tBudgetNew INNER JOIN qBOMACodesUnfiltered

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Bob Darlington" <bob@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:eMz2DhyDJHA.3392@xxxxxxxxxxxxxxxxxxxxxxx
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.
--
Bob Darlington
Brisbane



.

Relevant Pages

  • Re: Deleting Records from recordset
    ... Bob Darlington ... FROM tBudgetNew INNER JOIN qBOMACodesUnfiltered ... There is a one to many relationship between a table in qBOMACodes and ...
    (microsoft.public.access.modulesdaovba)
  • Re: Deleting Records from recordset
    ... "Alex Dybenko" wrote in message ... FROM tBudgetNew INNER JOIN qBOMACodesUnfiltered ... "Bob Darlington" wrote in message ...
    (microsoft.public.access.modulesdaovba)
  • Re: Deleting Records from recordset
    ... FROM tBudgetNew INNER JOIN qBOMACodesUnfiltered ... "Bob Darlington" wrote in message ... There is a one to many relationship between a table in qBOMACodes and tBudgetNew ...
    (microsoft.public.access.modulesdaovba)
  • Re: Deleting Records from recordset
    ... Still curious to know why it didn't work through the recordset though. ... Bob Darlington ... FROM tBudgetNew INNER JOIN qBOMACodesUnfiltered ...
    (microsoft.public.access.modulesdaovba)