Change field content for each related record on subform when one record is changed

In Access 2003 I have a form that the user can change the status of a field on (Ready For Test Field), this then changes the Status field, there may be more than one row that needs to have the Status field changed at the same time as the item may have a number of tests to be carried out, but, it will always be ready for the tests at the same time. Can anyone help me do this? I hope I am explaining what I mean ok, below is a basic idea of how things are laid out.

The tables I have are:

tblJobs - which contains, ID, Customer etc and tblTests which contains the other fields you see below.

In the example below when I tick that either of item ID 1 (the ID relates to a higher level overall job number) is ready I want the status on both items to change. I thought it would somehow be possible to do this by getting the ID of the item that has had the Ready For Test field changed then writing the status into each of the related records in the tblTests, so for example if I made a change to ID 1, all related records in tblTests would be changed to the same status

ID  Test No  Part Name        Test Type          Status          Ready For Test
1    306         Rubber Hose    Fill Test             Not Ready   Yes/No tickbox
1    307         Rubber Hose    Pressure Test  Not Ready
2    409          Chain Bolt        Pull Test           Not Ready
3    etc. etc.

Is this possible? And if so any help is greatly appreciated.
Ian LeasonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
in the after update event of the Ready For test checkbox, run an update query to update field Status for records of the same ID

if me.readyfortest=true then
    currentdb.execute "update tblTest set status='Ready' where ID=" &
   currentdb.execute "update tblTest set status='Not Ready' where ID=" &

end if

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
and..  requery the form

if me.readyfortest=true then
     currentdb.execute "update tblTest set status='Ready' where ID=" &
    currentdb.execute "update tblTest set status='Not Ready' where ID=" &

 end if
Ian LeasonAuthor Commented:
That is absolutely perfect. Thank you so much.
If ready for test applies to ALL tests for a job then, the flag does not belong in the test table at all.  It belongs in the Job table.  That way, when you  change the status, no batch update needs to happen.  Only if during the process will individual tests have a different value in their ready for test field should the ready for test field be in the test table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.