Help with deleting records in access

[kellywebber]

I'm sorry if this has already been posted before, I'm sure it has but
I've searched for an hour or so and haven't found anything that works
yet.

I need to delete records from one table which match the record that is
loaded on my current form when that record is deleted. Basically, when
the user presses the "delete" button on my form, I want the action to
also delete matching records in another table.

I would like to use SQL to do it, and just code it directly into the
On_Delete part of the program.

I guess I am just looking for code examples. For the sake of the
examples my main table is called TableA and the other table which I
would like to loop through and delete matching records is called
TableB. I have two fields that link, TableA.Field1 = TableB.Field1 AND
TableA.Field2 = TableB.Field2.

Any help is MUCH appreciated....

Thanks,
Kelly

 

[Douglas J. Steele]

Can you create a relationship between the two tables, and set Cascade Delete
on?

 

[kellywebber]

That would work perfect. I tried it though and I get the following
error message....

No unique index found for the referenced field of the primary table.

I don't have a primary key set in the table, could this be causing this
problem? The reason I don't have one is that my primary key actually
consists of two fields.

Thanks again for your help...

 

[Pieter Wijnen]

Try
CurrentDb.Execute "DELETE FROM TableB WHERE theKey=" & Me.TheKey.Value

HTH
--
Pieter Wijnen

My feeble Access pages (good links though)
http://www.thuleeng.com/access
When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca/
http://allenbrowne.com/
http://www.lebans.com/

 

[kellywebber]

This is working for me, thank you. However I need to compare two
fields, not just one. How would I add an AND statement to compare
another field after the first one you have here?

CurrentDb.Execute "DELETE FROM TableB WHERE theKey=" & Me.TheKey.Value

Basically I just want it to compare a second field as well as the first
key.

Thanks again....

 

[Douglas J. Steele]

Yes. You must have primary keys defined to create relationships.

A primary key can consist of up to 10 separate fields. In the GUI, select
those fields you want, and then click on the "key" icon to create a
multi-field primary key.

 

[Douglas J. Steele]

CurrentDb.Execute "DELETE FROM TableB WHERE theKey=" & Me.TheKey.Value & _
" And Field2 = " & Me.Field2Value

Note that assumes both fields are numeric. If, say, Field2 was text, you'd
need

CurrentDb.Execute "DELETE FROM TableB WHERE theKey=" & Me.TheKey.Value & _
" And Field2 = " & Chr$(34) & Me.Field2Value & Chr$(34)

 

[kellywebber]

Thank you very much for all of your help. I will try these tomorrow and
see where I get.

Thanks.

Kelly Webber

 

[kellywebber]

Thanks all of you for your help. I ended up setting the files up with
referential integrity and cascade deletes and it works great.

Thanks again.