Solved Second SQl delete query raises runtime error 424

[Switcher]

I have troubles deleting records using the DoCmnd.RunSQL function.
Part of the application consist out of a form with a subform based on a 'parent' table called "kar" and a 'child' table called "lijst". The tables have a one to many relation.

The parent form contains a command button that appends the content of the current record (master and child) to other tables (this works), next the current record from 'kar' and records from 'lijst' should be deleted. Peace of cake I thought.

This is the code used:

karWeg = Me.ID
sqlzin = "DELETE kar.* FROM kar WHERE ((kar.ID)= " & karWeg & ")"
Debug.Print sqlzin
DoCmd.RunSQL sqlzin
sqlzin = "DELETE lijst.* FROM lijst WHERE ((lijst_id)=" & karWeg & ")"
Debug.Print sqlzin
DoCmnd.RunSQL sqlzin 'Error on this line

The last DoCMD. RunSQL raises a runtime error 424 : object required.
This is the debug output:

DELETE kar.* FROM kar WHERE ((kar.ID)= 22)
DELETE lijst.* FROM lijst WHERE ((lijst_id)=22)''

If I copy the output from the last debug into a query SQL the record is deleted correctly (after resetting the code, Form still in the same condition)
Inversing the order of the deletes gives the same error for the last:

'line 2 and 5 swaped
karWeg = Me.ID
sqlzin = "DELETE lijst.* FROM lijst WHERE ((lijst_id)=" & karWeg & ")"
Debug.Print sqlzin
DoCmd.RunSQL sqlzin
sqlzin = "DELETE kar.* FROM kar WHERE ((kar.ID)= " & karWeg & ")"
Debug.Print sqlzin
DoCmnd.RunSQL sqlzin 'Error on this line

Same error message.
And the debug:

DELETE lijst.* FROM lijst WHERE ((lijst_id)=23)
DELETE kar.* FROM kar WHERE ((kar.ID)= 23)

Again copying to an query as above works.
I inserted a 'gotorecord next' instruction before the first sqlzin= phrase but it makes no difference.
Clearly this is not a syntaxis issue because its always the second SQL operation that fails and the construction is ok : they run from the query view.
I do not want to use Enforce Referential Integrity with the Cascade Delete option (if possible) because it might limit manual interaction on the records.
So any idea what causes this behavior and ow to solve it?
Grateful for your effort!

 

[Gasman]

You do not have a lijst_id?

 

[Switcher]

@ Gasman
Sorry, Typo from me. In the code lijst_id should be replaced by lijst.kar_id. This happened in editing the code fragments for this Post.
for clarity:

'kar en lijst records wissen
karWeg = Me.ID
DoCmd.GoToRecord , , acNext
sqlzin = "DELETE lijst.* FROM lijst WHERE ((lijst.kar_id)=" & karWeg & ")"
Debug.Print sqlzin
DoCmd.RunSQL sqlzin
sqlzin = "DELETE kar.* FROM kar WHERE ((kar.ID)= " & karWeg & ")"
Debug.Print sqlzin
DoCmnd.RunSQL sqlzin

with the DoCmd.goToRecord as illustration

 

[arnelgp]

see your Last DoCmd, it is spelled DoCmnd..

 

[Gasman]

@ Gasman
Sorry, Typo from me. In the code lijst_id should be replaced by lijst.kar_id. This happened in editing the code fragments for this Post.
for clarity:

with the DoCmd.goToRecord as illustration

You copy and paste what you have.
Do not alter anything. @arnelgp seems to have spotted yet another typo?

 

[Switcher]

@arnelgp
Indeed, that's the issue. I've been overlooking this for three day's.
After correcting the last line all works well
Thanks both off you for the time

 

[Gasman]

Do you have Option Explicit on all your modules, forms and reports?

That syntax does not even compile for me?

 

[Switcher]

@Gasman
I didn't notice option explicit was of in this project.
Would have saved me a lot of time.

 

[Pat Hartman]

Why would you not define a relationship and specify Cascade Delete? If the lijst rows have no meaning without the parent ID in the Kar table, you can run one delete and not worry about always running two. This ensures that both actions always complete or neither completes. Otherwise, you really should be enclosing the two deletes in a transaction so that they both complete or neither completes.

Also, naming all your PK's "ID" is poor practice and not helpful at all when others are trying to make sense of relationships. Having PK and FK names that match (with or without an "_FK" suffix helps to identify relationships. If you're going to live forever and never leave the company than who cares what standards you follow. But I prefer to make it as easy as possible for my successor (there will always be one sooner or later) to pick up the logic of an app.