AfterUpdate to change value in table

[karatelung]

i have a main table: tblFoster
and a linked table: tblIndFoster
they are linked as such (one to many): tblFoster.Identification ---> tblIndFoster .FosterID

there's a yes/no field called "Active" on tblFoster where if it is changed to "Inactive" or 0, i want the field "Active" on tblIndFoster for related records (where tblFoster.Identification = tblIndFoster.FosterID) to be changed to 0, as well.

i'm not exactly sure how to accomplish this. so far i have:

Private Sub Active_AfterUpdate()
If Me.Active = 0 Then

that's it. any help is greatly appreciated.

thanks,

richard

 

[pono1]

Richard,

Normally you wouldn't do this because your tables are already related and therefore what's in tblFoster is also, in a sense, in tblIndFoster.

To get a feel for it, go to the Access query grid and create a new query, showing both of your tables and adding fields from each to the grid, including the Active field, by double-clicking on the fields in the tables.

Run the query and you will see your tables brought together, with fields from each forming a single record.

Regards,
Tim

 

[karatelung]

sorry, i may not have explained it exactly right. there are times when a home (main) is "Active," but one of the individuals within a home (sub) is not. but when the home is deemed "Inactive," all of the individuals within the home should be automatically marked "Inactive," as well.

thanks.

 

[pono1]

Oh. I see.

A possible approach: If you don't already have a textbox on your form bound to the 1-table's primary key, add one (you can hide this textbox, of course, if it's meaningless to your users) and then fiddle with code like this...

'This is pseudo code.

If ChkActive.Value = 0 Then

   CurrentProject.connection.Execute _
   UPDATE TblMany
   SET TblMany.Active = 0
   WHERE TblMany.ForeignKey = TextBoxPrimaryKey

End If

Or rather than struggle with the SQL string, create and save the update query in the Access query maker grid, using the criteria row to reference your primary key textbox on the form. Then call the query from code. You will have two parts for the one operation but the code is clean and easy to read.

If Me.ChkActive.Value = 0 Then
     DoCmd.OpenQuery "UpdateQueryName"
End if

Regards,
Tim

 

[karatelung]

thank you. that helps a lot.

richie