Updating fields for a subform automatically depending on the mainform

[nerd19]

I am using two tables, one is called tools and the other is called
changes. In the tools table i have the fields: serial (the s/n of a
tool), size (size of the tool), and status (whether it is IN stock or
OUT). In the changes table i have the fields: machine (a machines
number), new tool (serial of the tool that is goin to be used) and old
tool (the serial of the tool that is coming back). For the main form
i have the user select a machine, select a tool from a combo box, and
the old tool is listed automatically from the previous usage, this
form is bound to the "changes" table. What i would like to do is
automatically set the status for the new tool to "Out" and the status
of the old tool to "In", again the status field is from a different
table than what the mainform is bound to. I was wonderig what would
be the best way to update the status field for the two tools?

So at the moment i have two subforms, each of which bound to a status
field for the tools table and each subform is linked to the mainform
through new tool - serial and old tool - serial, so once a new tool
is selected the status of that tool is brought up in the subform, then
the same concept with old tool from the mainform. This is where i am
stuck for this method, im not sure how to get the subforms to save the
update to the status field ONLY after a the change has been made (so
only after the user has completed the change and either moved to a new
record entry or exited the mainform). I have been able to get it
where the subform changes status of the new tool right after it is
select, but the problem is that the user needs to be able to change
their selection in case they picked the wrong one. To wrap it up
nicely i guess, is how to i save edits in a subform upon new record
creations from a main form??

any help or ideas is appreciated.... ms access 2002

 

[nerd19]

I am using two tables, one is called tools and the other is called
changes. In the tools table i have the fields: serial (the s/n of a
tool), size (size of the tool), and status (whether it is IN stock or
OUT). In the changes table i have the fields: machine (a machines
number), new tool (serial of the tool that is goin to be used) and old
tool (the serial of the tool that is coming back). For the main form
i have the user select a machine, select a tool from a combo box, and
the old tool is listed automatically from the previous usage, this
form is bound to the "changes" table. What i would like to do is
automatically set the status for the new tool to "Out" and the status
of the old tool to "In", again the status field is from a different
table than what the mainform is bound to. I was wonderig what would
be the best way to update the status field for the two tools?

So at the moment i have two subforms, each of which bound to a status
field for the tools table and each subform is linked to the mainform
through new tool - serial and old tool - serial, so once a new tool
is selected the status of that tool is brought up in the subform, then
the same concept with old tool from the mainform. This is where i am
stuck for this method, im not sure how to get the subforms to save the
update to the status field ONLY after a the change has been made (so
only after the user has completed the change and either moved to a new
record entry or exited the mainform). I have been able to get it
where the subform changes status of the new tool right after it is
select, but the problem is that the user needs to be able to change
their selection in case they picked the wrong one. To wrap it up
nicely i guess, is how to i save edits in a subform upon new record
creations from a main form??

any help or ideas is appreciated.... ms access 2002

So, i have this working by using:
Private Sub Form_AfterInsert()
[Forms]![BM & Tool Change Form v6]![Tools status out].[Form]![Status]
= "Out"
End Sub

but now instead of updating or doing an edit to status, it will make a
new record. I need it to just change the status not make a new record