How to delete record from the main table if the subtable has no record using VBA

[mseo]

hi,
I have a form for delete fitered records, i need to delete the main table's record if the child has no records
thank you in advance for any help you may provide me

[nico5038]

You can remove Main table rows having no child records using a WHERE clause with the NOT EXISTS clause.

Just checkout the SQL helpfile for using the EXISTS clause.

Nic;o)

[mseo]

You can remove Main table rows having no child records using a WHERE clause with the NOT EXISTS clause.

Just checkout the SQL helpfile for using the EXISTS clause.

Nic;o)

thank you very much for your reply
ok, I will check the help file
thank you

[mseo]

hi,

SELECT *
FROM tbl_production
WHERE not exists (select * from tbl_productiondetail Where tbl_production.batch_No = tbl_productiondetail.batch_no);

but i need to use the above statement in vba behind cmdbutton
thank you

[nico5038]

Well done :-)

Use the docmd.execute like:

docmd.execute ("<your query string>")

Nic;o)

[mseo]

Well done :-)

Use the docmd.execute like:

docmd.execute ("<your query string>")

Nic;o)

thank you nico5038
it works great
thank you again

[NeoPa]

For the potentially irritating status messages informing how many records have been deleted, use :

Call DoCmd.SetWarnings(False)
Call DoCmd.Execute(YourSQLString)
Call DoCmd.SetWarnings(True)

[nico5038]

Hi NeoPa,

The Docmd.execute won't produce these annoying status messages, unless you add the option dbFailOnError like:

DoCmd.Execute("YourSQLString",dbFailOnError )

This is one of the reasons why I prefer this method.
The other is that a .SetWarnings(Fa lse) will also suppress a form save pop-up in design, even when out of debugging mode, that's the other reason for me to minimize the use of .SetWarnings.

Nic;o)

[mseo]

Hi NeoPa,

The Docmd.execute won't produce these annoying status messages, unless you add the option dbFailOnError like:

DoCmd.Execute("YourSQLString",dbFailOnError )

This is one of the reasons why I prefer this method.
The other is that a .SetWarnings(Fa lse) will also suppress a form save pop-up in design, even when out of debugging mode, that's the other reason for me to minimize the use of .SetWarnings.

Nic;o)

thank you for the very helpful information that I can gain from here
now I can differentiate between
Docmd.Execute
DoCmd.RunSQL

Docmd.Execute: doesn't return any warnings except with dbFailOnError
DoCmd.RunSQL: entails using the code that Neopa posted
in the case I want to get rid of the built-in Access warnings
thank you very much

[NeoPa]

I didn't realise that Nico. Another good tip to learn from :)