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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Thanks Alex.
Tried that but still same result.

--
Bob Darlington
Brisbane
"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>

Hi,
then you can run delete query instead of .Delete

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


"Bob Darlington" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks Alex.
> Tried that but still same result.
>
> --
> Bob Darlington
> Brisbane
> "Alex Dybenko" <(E-Mail Removed)> wrote in message
> news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>
>

Alex,
Correct. But I want to run a series of conditional tests on each record
before deleting.

--
Bob Darlington
Brisbane
"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:uMJSDB$(E-Mail Removed)...
> Hi,
> then you can run delete query instead of .Delete
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
>
> "Bob Darlington" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks Alex.
>> Tried that but still same result.
>>
>> --
>> Bob Darlington
>> Brisbane
>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>> news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>
>>

Hi,
but you can still run tests, just run delete query, which deletes current
record instead of .Delete

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


"Bob Darlington" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Alex,
> Correct. But I want to run a series of conditional tests on each record
> before deleting.
>
> --
> Bob Darlington
> Brisbane
> "Alex Dybenko" <(E-Mail Removed)> wrote in message
> news:uMJSDB$(E-Mail Removed)...
>> Hi,
>> then you can run delete query instead of .Delete
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>>
>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Thanks Alex.
>>> Tried that but still same result.
>>>
>>> --
>>> Bob Darlington
>>> Brisbane
>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>> news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>>
>>>
>>>
>
>

Thanks Alex, I'll do that.
Still curious to know why it didn't work through the recordset though.

--
Bob Darlington
Brisbane
"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
> Hi,
> but you can still run tests, just run delete query, which deletes current
> record instead of .Delete
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
>
> "Bob Darlington" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Alex,
>> Correct. But I want to run a series of conditional tests on each record
>> before deleting.
>>
>> --
>> Bob Darlington
>> Brisbane
>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>> news:uMJSDB$(E-Mail Removed)...
>>> Hi,
>>> then you can run delete query instead of .Delete
>>>
>>> --
>>> Best regards,
>>> ___________
>>> Alex Dybenko (MVP)
>>> http://accessblog.net
>>> http://www.PointLtd.com
>>>
>>>
>>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Thanks Alex.
>>>> Tried that but still same result.
>>>>
>>>> --
>>>> Bob Darlington
>>>> Brisbane
>>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>>> news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> 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
>>>>>>
>>>>
>>>>
>>
>>

Hi,
have a look here, perhaps same issue:
http://accessblog.net/2004/10/access...ql-in-jet.html

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


"Bob Darlington" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks Alex, I'll do that.
> Still curious to know why it didn't work through the recordset though.
>
> --
> Bob Darlington
> Brisbane
> "Alex Dybenko" <(E-Mail Removed)> wrote in message
> news:e%(E-Mail Removed)...
>> Hi,
>> but you can still run tests, just run delete query, which deletes current
>> record instead of .Delete
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>>
>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Alex,
>>> Correct. But I want to run a series of conditional tests on each record
>>> before deleting.
>>>
>>> --
>>> Bob Darlington
>>> Brisbane
>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>> news:uMJSDB$(E-Mail Removed)...
>>>> Hi,
>>>> then you can run delete query instead of .Delete
>>>>
>>>> --
>>>> Best regards,
>>>> ___________
>>>> Alex Dybenko (MVP)
>>>> http://accessblog.net
>>>> http://www.PointLtd.com
>>>>
>>>>
>>>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> Thanks Alex.
>>>>> Tried that but still same result.
>>>>>
>>>>> --
>>>>> Bob Darlington
>>>>> Brisbane
>>>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>>>> news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>>> news:(E-Mail Removed)...
>>>>>>> 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
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>

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.

--
Bob Darlington
Brisbane
"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:en$Aa%(E-Mail Removed)...
> Hi,
> have a look here, perhaps same issue:
> http://accessblog.net/2004/10/access...ql-in-jet.html
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
>
> "Bob Darlington" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks Alex, I'll do that.
>> Still curious to know why it didn't work through the recordset though.
>>
>> --
>> Bob Darlington
>> Brisbane
>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>> news:e%(E-Mail Removed)...
>>> Hi,
>>> but you can still run tests, just run delete query, which deletes
>>> current record instead of .Delete
>>>
>>> --
>>> Best regards,
>>> ___________
>>> Alex Dybenko (MVP)
>>> http://accessblog.net
>>> http://www.PointLtd.com
>>>
>>>
>>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Alex,
>>>> Correct. But I want to run a series of conditional tests on each record
>>>> before deleting.
>>>>
>>>> --
>>>> Bob Darlington
>>>> Brisbane
>>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>>> news:uMJSDB$(E-Mail Removed)...
>>>>> Hi,
>>>>> then you can run delete query instead of .Delete
>>>>>
>>>>> --
>>>>> Best regards,
>>>>> ___________
>>>>> Alex Dybenko (MVP)
>>>>> http://accessblog.net
>>>>> http://www.PointLtd.com
>>>>>
>>>>>
>>>>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> Thanks Alex.
>>>>>> Tried that but still same result.
>>>>>>
>>>>>> --
>>>>>> Bob Darlington
>>>>>> Brisbane
>>>>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>>>>> news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>>>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>>>> news:(E-Mail Removed)...
>>>>>>>> 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
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>

yes, sorry, I already forgot about original question, you have another issue

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


"Bob Darlington" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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.
>
> --
> Bob Darlington
> Brisbane
> "Alex Dybenko" <(E-Mail Removed)> wrote in message
> news:en$Aa%(E-Mail Removed)...
>> Hi,
>> have a look here, perhaps same issue:
>> http://accessblog.net/2004/10/access...ql-in-jet.html
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>>
>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Thanks Alex, I'll do that.
>>> Still curious to know why it didn't work through the recordset though.
>>>
>>> --
>>> Bob Darlington
>>> Brisbane
>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>> news:e%(E-Mail Removed)...
>>>> Hi,
>>>> but you can still run tests, just run delete query, which deletes
>>>> current record instead of .Delete
>>>>
>>>> --
>>>> Best regards,
>>>> ___________
>>>> Alex Dybenko (MVP)
>>>> http://accessblog.net
>>>> http://www.PointLtd.com
>>>>
>>>>
>>>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> Alex,
>>>>> Correct. But I want to run a series of conditional tests on each
>>>>> record before deleting.
>>>>>
>>>>> --
>>>>> Bob Darlington
>>>>> Brisbane
>>>>> "Alex Dybenko" <(E-Mail Removed)> wrote in message
>>>>> news:uMJSDB$(E-Mail Removed)...
>>>>>> Hi,
>>>>>> then you can run delete query instead of .Delete
>>>>>>
>>>>>> --
>>>>>> Best regards,
>>>>>> ___________
>>>>>> Alex Dybenko (MVP)
>>>>>> http://accessblog.net
>>>>>> http://www.PointLtd.com
>>>>>>
>>>>>>
>>>>>> "Bob Darlington" <(E-Mail Removed)> wrote in message
>>>>>> news:(E-Mail Removed)...
>>>>>>> Thanks Alex.
>>>>>>> Tried that but still same result.
>>>>>>>
>>>>>>> --
>>>>>>> Bob Darlington
>>>>>>> Brisbane
>>>>>>> "Alex Dybenko" <(E-Mail Removed)> wrote in
>>>>>>> message news:46A28C61-0EE7-445F-B2B7-(E-Mail Removed)...
>>>>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>>>>> news:(E-Mail Removed)...
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>