Auto Updating Fields

[allstar45]

here's what I have, table and field names changed:
tableA, which contains multiple tableB's, which contain multiple tableC's. table A, B, and C all have this true/false box called isCool.

A ---->multiple B


B ---->multiple C

if C is changed to true, then the B it's part of is true, which means A that is part of that B is true.

if B is changed to true, then A it's part of is true, this doesn't mean any C is changed.

if A is changed to true, then this doesn't change B or C

AND AT THE SAME TIME

if C is changed to false, then this doesn't change B or A

if B is changed to false, then all C's change to false (if not already false)

if A is changed to false, all B's and C's under that A are changed to false (if not already false)


What's the best way to do this?
Let me know if I explained it poorly and I'll try again.
Thanks

[Robeen]

One way you might consider is to use VBA code to create an SQL string [Eg: strSQL] that contains an update statement.
Then use the Docmd.RunSQL strSQL to execute the update statement.
Or - this simple snippet might help.

Private Sub cmdUpdateRecord_Click()Dim db As Database
Set db = CurrentDb
db.Execute "UPDATE [Copy Of 12-09-2011] SET [Chosen] = True WHERE ID = " & Combo35
End Sub

In the db.Execute statement - the value is taken from the Combo35 field of the Form.

You might also read up here.
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Let us know if you have any questions.

[allstar45]

I was more specificly wondering what the querie would look like, to look through all tables (tableB, tableC) and check to see if the parent values of all the child valuesmatch up.

I'm not worried about making sure that the child values are no if the parent value is, since it contradicts the first part.

something that looks like first:

UPDATE tableB SET tableB.isCool = True WHERE tableC.isCool = True AND tableC.ID = tableB.ID;

and then:

UPDATE tableA SET tableA.isCool = True WHERE tableB.isCool = True AND tableB.ID = tableA.ID;

is there a way to make changes to all tableA in one querie or will I have to run a querie per row in tableB and loop trough tableB using VBA code?

[Robeen]

If your 'Where' clause inculdes criteria that isolates all the rows that you need to update - then it can be done in one swoop.
Try creating a 'Select' query that returns all the rows you need to update.
If you can do that, then I would say that you should be able to update all records at once without having to run an update query for every row of data.