473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

mseo
181 100+
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
Jul 9 '10 #1
9 1853
nico5038
3,080 Expert 2GB
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)
Jul 9 '10 #2
mseo
181 100+
@nico5038
thank you very much for your reply
ok, I will check the help file
thank you
Jul 9 '10 #3
mseo
181 100+
hi,
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tbl_production
  3. 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
Jul 9 '10 #4
nico5038
3,080 Expert 2GB
Well done :-)

Use the docmd.execute like:

Expand|Select|Wrap|Line Numbers
  1. docmd.execute ("<your query string>")
  2.  
Nic;o)
Jul 9 '10 #5
mseo
181 100+
@nico5038
thank you nico5038
it works great
thank you again
Jul 10 '10 #6
NeoPa
32,556 Expert Mod 16PB
For the potentially irritating status messages informing how many records have been deleted, use :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
  2. Call DoCmd.Execute(YourSQLString)
  3. Call DoCmd.SetWarnings(True)
Jul 10 '10 #7
nico5038
3,080 Expert 2GB
Hi NeoPa,

The Docmd.execute won't produce these annoying status messages, unless you add the option dbFailOnError like:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Execute("YourSQLString",dbFailOnError )
  2.  
This is one of the reasons why I prefer this method.
The other is that a .SetWarnings(False) 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)
Jul 10 '10 #8
mseo
181 100+
@nico5038
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
Jul 10 '10 #9
NeoPa
32,556 Expert Mod 16PB
I didn't realise that Nico. Another good tip to learn from :)
Jul 11 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: nassim.bouayad.agha | last post by:
Unique table version record Hello, I am using a table record to store informations about database dump,I use this table : CREATE TABLE dump_version( count BIGINT unsigned NOT NULL DEFAULT 0,...
1
by: gaurkamal | last post by:
I want to delete record in table B when i delete record in Table A both table have a common column. I want to do it using trigger .Can any body give some idea. Table details are. Table A: ...
18
by: shinyo21 | last post by:
Anyone know how to create a SQL or way in Access for deleting few tables' record. I means is delete all the records in specify few tables. I tried to use query to delete the records' table but...
0
by: marcoseraphin | last post by:
How to delete a relation in access database using .Net 2.0 C# ? My problem: I have a table which has a foreign key contraint to another table. I want to delete this relation using a ALTER Table...
1
by: mathewgk80 | last post by:
Hi all, I would like to know whether i can insert a record as the first record to a table which is not empty using sqlserver(that is i need to display that record as the first record)..... ...
1
by: MLH | last post by:
Put 2 tables in a qbe grid & link 'em so as to list only records in tblA that have related records in tblB. Run the query. HiLite a row. Press delete key. A record is deleted. How can you...
2
by: tanuja5 | last post by:
hi.. how to delete the selected row in datagridview using ContextMenuStrip.When i right-click and press "delete" the seleted row in the dataGridView has to be deleted and as well in my access...
6
by: kstevens | last post by:
I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete...
0
by: Neven Huynh | last post by:
Hi Everyone, Here i my LINQ query to get record in Table Menu with condition are parentID == 0(get root menu) and ID != (parentID list) (which is parent ID list is are id of menu record that have...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.