Full Version: Can You Use The Afterupdate Event To Delete Records In A Different Table?
UtterAccess Forums > Microsoft® Access > Access Forms
JRS2345
I have a combo-box on a form that updates a table (tblA). tblA is in a one-to-many relationship with tblB. I would like to use the AfterUpdate event to delete the records in tblB. They don't auto-delete because a record still exists in tblA, but upon update, the records in tblB are no longer accurate even though the relationship between tables is still valid. Any suggestions? Thanks!
theDBguy
Hi,
Not sure I follow you but yes, you should be able to use the AfterUpdate event to delete uneeded records in another table.
Just my 2 cents... 2cents.gif
JRS2345
Any suggestions on how to delete the records? I'm new at this...thanks!
Jerry Whittle
Create a query something like this:
ELETE tblB.* FROM tblB WHERE (tblB.Serial_Number)=[forms]![frmtblA]![txtSerial_Number] ;
The part after the = sign should have the form name and the name of the text box on the form that has the field which joins the two tables together.
Save the query with a name something like: qryDeletetblB
Now in the After Update event of the form, create an event procedure that should look something like this when you are done:
Private Sub Form_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeletetblB"
DoCmd.SetWarnings True
End Sub
Make sure to compile this procedure by going up to Debug, Compile. Fix any problems that it finds then save it.
However there might be something wrong with your table setup if you need to delete records in one table based on changes in another. I would need to see a practical example to really pass judgement and hear the business rule behind it.
JRS2345
Thanks Jerry. I was putting the delete query directly into the After Update event procedure & it wasn't working!
There may be a better way to set up the tables...basically what is happening is that I've got one parent record with 2 cascading categories of information, looking like this
Parent Record 1: Bonnie's Apple Orchard
First Child (Color): red, green
Second Child (Type): Macintosh, Fuji, Granny Smith
Parent Record 2: Bill's Apple Orchard
Color: red
Type: Macintosh
If the user changes Bill's Apple Orchard to have green apples, then Macintosh needs to be deleted; however, the tables need to allow multiple selections for colors and types because Bonnie's Orchard has more than one color and more than one type of apple per color etc.
If you have any further thoughts about this, I'd love to hear them. Looking through other posts, I can't find a lot of folks trying to do the same things that I am, which probably indicates that I'm not taking the best approach. <
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.