Upon delete record, conditional delete record from another table

G

Guest

Hi
I have 3 tables:
Vehicles (PK VRM)
Drivers (PK Driver ID)
VehiclesDrivers (PK VRM and DriverID)

(As you can see, the vehicles - driver relationship is a many to many.)
I have a main form, Vehicles with a continuous subform, showing drivers
relating to the driver. On this main form, I also have a delete button to
remove the vehicle from the database. How can I set things up so that any
drivers who are not associated with any other vehicles in the database are
also deleted from the database? To clarify, when I delete the vehicle
record, I need to check the VehiclesDrivers table. Any drivers which appear
in the Drivers table but not the DriversVehicles table should be deleted from
the Drivers table.

TIA
rich
 
A

Allen Browne

Access cannot do this at the engine-level, as it has no triggers.

If the deletions are performed through a form, you could try using the
AfterDelConfirm event of the subform to DLookup() the DriverID from the
parent form in the VehiclesDrivers table. If none are found, execute a
DELETE query statement to delete the record from that table (or from the
parent form if you prefer.) I don't know if that will work, or if you could
hit timing issues with the events.
 
G

Guest

Thanks Allen for your reply.

There may be multiple drivers associated with each vehicle - so is it
possible to look up each DriverID in turn in the VehiclesDrivers table? You
wrote in your post, "If none are found...." Could I use a DCount to just
count any records in the VehiclesDrivers for this DriverID, and if it's 0 (or
would it need to be "1", since this VRM entry in the VehiclesDrivers table
hasn't been deleted yet [has it?]) then delete the driver from Drivers table?

I'm thinking this through step by step....

The drivers who are associated with the vehicle will all be listed in the
VehiclesDrivers table and will have the vehicle table key (VRM) value in
common. This VRM is the same as the VRM for the vehicle which is about to be
deleted.

For each of these drivers (ie records in the VehiclesDrivers table where VRM
= the same as the vehicle about to be deleted), I need to lookup the
VehiclesDrivers table to see whether the DriverID in this record has any
other entries in the table (ie is associated with any other vehicles.)
(What is the code for this?)
If yes, don't delete from Drivers table. If no, execute DELETE query to
remove the record (DriverID) from the Drivers table.
I've never written a DELETE query before.. what is the code for this?

Is this thinking correct? How do I put all of this into code/ SQL?
Can you advise Allen?
thanks
rich
 
A

Allen Browne

To delete all drivers who are not associated with any vehicles, execute this
query:

DELETE FROM Drivers
WHERE Not Exists
(SELECT VRM FROM VehiclesDrivers
WHERE VehiclesDrivers.DriverID = Drivers.DriverID);

Try it in the form's AfterDelConfirm event (assuming Access tables in an
MDB, not an ADP.)

The WHERE clause contains a subquery.
If you want to know more about those, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rich1234 said:
Thanks Allen for your reply.

There may be multiple drivers associated with each vehicle - so is it
possible to look up each DriverID in turn in the VehiclesDrivers table?
You
wrote in your post, "If none are found...." Could I use a DCount to just
count any records in the VehiclesDrivers for this DriverID, and if it's 0
(or
would it need to be "1", since this VRM entry in the VehiclesDrivers table
hasn't been deleted yet [has it?]) then delete the driver from Drivers
table?

I'm thinking this through step by step....

The drivers who are associated with the vehicle will all be listed in the
VehiclesDrivers table and will have the vehicle table key (VRM) value in
common. This VRM is the same as the VRM for the vehicle which is about to
be
deleted.

For each of these drivers (ie records in the VehiclesDrivers table where
VRM
= the same as the vehicle about to be deleted), I need to lookup the
VehiclesDrivers table to see whether the DriverID in this record has any
other entries in the table (ie is associated with any other vehicles.)
(What is the code for this?)
If yes, don't delete from Drivers table. If no, execute DELETE query to
remove the record (DriverID) from the Drivers table.
I've never written a DELETE query before.. what is the code for this?

Is this thinking correct? How do I put all of this into code/ SQL?
Can you advise Allen?
thanks
rich

Allen Browne said:
Access cannot do this at the engine-level, as it has no triggers.

If the deletions are performed through a form, you could try using the
AfterDelConfirm event of the subform to DLookup() the DriverID from the
parent form in the VehiclesDrivers table. If none are found, execute a
DELETE query statement to delete the record from that table (or from the
parent form if you prefer.) I don't know if that will work, or if you
could
hit timing issues with the events.
Click to expand...
Click to expand...
 
G

Guest

Thanks Allen for your post. How do I run the query (in VB?) I'm not sure
how to get the SQL to run, or where I have to put it (VB? Macro? I haven't
created a macro before so could you tell me exactly how to code it if I need
a macro?)



Allen Browne said:
To delete all drivers who are not associated with any vehicles, execute this
query:

DELETE FROM Drivers
WHERE Not Exists
(SELECT VRM FROM VehiclesDrivers
WHERE VehiclesDrivers.DriverID = Drivers.DriverID);

Try it in the form's AfterDelConfirm event (assuming Access tables in an
MDB, not an ADP.)

The WHERE clause contains a subquery.
If you want to know more about those, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rich1234 said:
Thanks Allen for your reply.

There may be multiple drivers associated with each vehicle - so is it
possible to look up each DriverID in turn in the VehiclesDrivers table?
You
wrote in your post, "If none are found...." Could I use a DCount to just
count any records in the VehiclesDrivers for this DriverID, and if it's 0
(or
would it need to be "1", since this VRM entry in the VehiclesDrivers table
hasn't been deleted yet [has it?]) then delete the driver from Drivers
table?

I'm thinking this through step by step....

The drivers who are associated with the vehicle will all be listed in the
VehiclesDrivers table and will have the vehicle table key (VRM) value in
common. This VRM is the same as the VRM for the vehicle which is about to
be
deleted.

For each of these drivers (ie records in the VehiclesDrivers table where
VRM
= the same as the vehicle about to be deleted), I need to lookup the
VehiclesDrivers table to see whether the DriverID in this record has any
other entries in the table (ie is associated with any other vehicles.)
(What is the code for this?)
If yes, don't delete from Drivers table. If no, execute DELETE query to
remove the record (DriverID) from the Drivers table.
I've never written a DELETE query before.. what is the code for this?

Is this thinking correct? How do I put all of this into code/ SQL?
Can you advise Allen?
thanks
rich

Allen Browne said:
Access cannot do this at the engine-level, as it has no triggers.

If the deletions are performed through a form, you could try using the
AfterDelConfirm event of the subform to DLookup() the DriverID from the
parent form in the VehiclesDrivers table. If none are found, execute a
DELETE query statement to delete the record from that table (or from the
parent form if you prefer.) I don't know if that will work, or if you
could
hit timing issues with the events.

Hi
I have 3 tables:
Vehicles (PK VRM)
Drivers (PK Driver ID)
VehiclesDrivers (PK VRM and DriverID)

(As you can see, the vehicles - driver relationship is a many to many.)
I have a main form, Vehicles with a continuous subform, showing drivers
relating to the driver. On this main form, I also have a delete
button
to
remove the vehicle from the database. How can I set things up so that
any
drivers who are not associated with any other vehicles in the database
are
also deleted from the database? To clarify, when I delete the vehicle
record, I need to check the VehiclesDrivers table. Any drivers which
appear
in the Drivers table but not the DriversVehicles table should be
deleted
from
the Drivers table.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

In a macro, use the RunSQL action.

In VBA code, use the Execute method, e.g.:
strSQL = "DELETE ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rich1234 said:
Thanks Allen for your post. How do I run the query (in VB?) I'm not sure
how to get the SQL to run, or where I have to put it (VB? Macro? I
haven't
created a macro before so could you tell me exactly how to code it if I
need
a macro?)



Allen Browne said:
To delete all drivers who are not associated with any vehicles, execute
this
query:

DELETE FROM Drivers
WHERE Not Exists
(SELECT VRM FROM VehiclesDrivers
WHERE VehiclesDrivers.DriverID = Drivers.DriverID);

Try it in the form's AfterDelConfirm event (assuming Access tables in an
MDB, not an ADP.)

The WHERE clause contains a subquery.
If you want to know more about those, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Rich1234 said:
Thanks Allen for your reply.

There may be multiple drivers associated with each vehicle - so is it
possible to look up each DriverID in turn in the VehiclesDrivers table?
You
wrote in your post, "If none are found...." Could I use a DCount to
just
count any records in the VehiclesDrivers for this DriverID, and if it's
0
(or
would it need to be "1", since this VRM entry in the VehiclesDrivers
table
hasn't been deleted yet [has it?]) then delete the driver from Drivers
table?

I'm thinking this through step by step....

The drivers who are associated with the vehicle will all be listed in
the
VehiclesDrivers table and will have the vehicle table key (VRM) value
in
common. This VRM is the same as the VRM for the vehicle which is about
to
be
deleted.

For each of these drivers (ie records in the VehiclesDrivers table
where
VRM
= the same as the vehicle about to be deleted), I need to lookup the
VehiclesDrivers table to see whether the DriverID in this record has
any
other entries in the table (ie is associated with any other vehicles.)
(What is the code for this?)
If yes, don't delete from Drivers table. If no, execute DELETE query
to
remove the record (DriverID) from the Drivers table.
I've never written a DELETE query before.. what is the code for this?

Is this thinking correct? How do I put all of this into code/ SQL?
Can you advise Allen?
thanks
rich

:

Access cannot do this at the engine-level, as it has no triggers.

If the deletions are performed through a form, you could try using the
AfterDelConfirm event of the subform to DLookup() the DriverID from
the
parent form in the VehiclesDrivers table. If none are found, execute a
DELETE query statement to delete the record from that table (or from
the
parent form if you prefer.) I don't know if that will work, or if you
could
hit timing issues with the events.

Hi
I have 3 tables:
Vehicles (PK VRM)
Drivers (PK Driver ID)
VehiclesDrivers (PK VRM and DriverID)

(As you can see, the vehicles - driver relationship is a many to
many.)
I have a main form, Vehicles with a continuous subform, showing
drivers
relating to the driver. On this main form, I also have a delete
button to
remove the vehicle from the database. How can I set things up so
that
any
drivers who are not associated with any other vehicles in the
database
are
also deleted from the database? To clarify, when I delete the
vehicle
record, I need to check the VehiclesDrivers table. Any drivers
which
appear
in the Drivers table but not the DriversVehicles table should be
deleted from the Drivers table.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you Allen.
I used the VB method.. works beautifully and keeps the DB clean!

Apprecciated,
rich

Allen Browne said:
In a macro, use the RunSQL action.

In VBA code, use the Execute method, e.g.:
strSQL = "DELETE ...
dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rich1234 said:
Thanks Allen for your post. How do I run the query (in VB?) I'm not sure
how to get the SQL to run, or where I have to put it (VB? Macro? I
haven't
created a macro before so could you tell me exactly how to code it if I
need
a macro?)



Allen Browne said:
To delete all drivers who are not associated with any vehicles, execute
this
query:

DELETE FROM Drivers
WHERE Not Exists
(SELECT VRM FROM VehiclesDrivers
WHERE VehiclesDrivers.DriverID = Drivers.DriverID);

Try it in the form's AfterDelConfirm event (assuming Access tables in an
MDB, not an ADP.)

The WHERE clause contains a subquery.
If you want to know more about those, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Thanks Allen for your reply.

There may be multiple drivers associated with each vehicle - so is it
possible to look up each DriverID in turn in the VehiclesDrivers table?
You
wrote in your post, "If none are found...." Could I use a DCount to
just
count any records in the VehiclesDrivers for this DriverID, and if it's
0
(or
would it need to be "1", since this VRM entry in the VehiclesDrivers
table
hasn't been deleted yet [has it?]) then delete the driver from Drivers
table?

I'm thinking this through step by step....

The drivers who are associated with the vehicle will all be listed in
the
VehiclesDrivers table and will have the vehicle table key (VRM) value
in
common. This VRM is the same as the VRM for the vehicle which is about
to
be
deleted.

For each of these drivers (ie records in the VehiclesDrivers table
where
VRM
= the same as the vehicle about to be deleted), I need to lookup the
VehiclesDrivers table to see whether the DriverID in this record has
any
other entries in the table (ie is associated with any other vehicles.)
(What is the code for this?)
If yes, don't delete from Drivers table. If no, execute DELETE query
to
remove the record (DriverID) from the Drivers table.
I've never written a DELETE query before.. what is the code for this?

Is this thinking correct? How do I put all of this into code/ SQL?
Can you advise Allen?
thanks
rich

:

Access cannot do this at the engine-level, as it has no triggers.

If the deletions are performed through a form, you could try using the
AfterDelConfirm event of the subform to DLookup() the DriverID from
the
parent form in the VehiclesDrivers table. If none are found, execute a
DELETE query statement to delete the record from that table (or from
the
parent form if you prefer.) I don't know if that will work, or if you
could
hit timing issues with the events.

Hi
I have 3 tables:
Vehicles (PK VRM)
Drivers (PK Driver ID)
VehiclesDrivers (PK VRM and DriverID)

(As you can see, the vehicles - driver relationship is a many to
many.)
I have a main form, Vehicles with a continuous subform, showing
drivers
relating to the driver. On this main form, I also have a delete
button to
remove the vehicle from the database. How can I set things up so
that
any
drivers who are not associated with any other vehicles in the
database
are
also deleted from the database? To clarify, when I delete the
vehicle
record, I need to check the VehiclesDrivers table. Any drivers
which
appear
in the Drivers table but not the DriversVehicles table should be
deleted from the Drivers table.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SOS! How to add new record to many to many relationship using a fo 22
Refresh original form 1
if textbox entry is blank, add "unknown" automatically 1
Show matching records as new record is added 1
Count Records in Subform to determine SubForm mode 3
Unique Count in Query 17
How to set this up? 9
Goto record in Parent form by searching criteria in Subform 16

Top