Please help a noobie with some VB code in MS Access 2007

[cryonisis3668]

I have done a lot of reading up and found out that a problem that I am
currently experiencing can be solved with an AfterUpdate Event. Now my
problem is that I am completely clueless when it comes to Visual Basic
and I was hoping someone might be able to give a me a hand with this.
Here is the background first.

We have a warehouse with tools (each with their own alphanumeric
serial number, stored in table Tool_List) that are issued on a daily
basis using a form (Tool_Issue_Form which links to the Tool_Issue_DB
table).

Sometimes a tool is returned broken and we use another form
(Faulty_Items_Form) to capture the details of the broken tools tool in
a separate table (Faulty_Tools_DB).

A field in Faulty_Tools_DB (called "Broken") has a Boolean value
(defaulting to TRUE). A separate form is used to set it to FALSE when
it has been repaired.

Now for my question. I need help with the code to do either one or
both of the following:

1) When a serial number is entered into the Serial_No field in the
Tool_Issue_Form, the AfterUpdate Event must immediately check to see
if this item a in Faulty_Tools_DB AND see if the Boolean value is set
to TRUE. If it is, then it returns a message box stating that the item
is faulty and not allowed to be issued. I'm not sure how the code
logic works and if it actually searches out ALL instances of that
serial number in the Faulty_Tools_DB. If not, then my second way would
work better due to only one instance of the serial number being
present in the search.

2) Move the Broken field from the Faulty_Tools_DB table to the
Tool_List table and when the Faulty_Items_Form is filled out, it uses
an AfterUpdate Event to update the Boolean field in the Tool_List
table. Then this field is checked instead (as described in option 1.)
when the Tool_Issue_Form is filled out.

Thanking you in advance.

 

[Klatuu]

It would be better if you did not use a separate table, but had the Broken
field in the Tool_List table.
On the form where tools are returned, you would want to be able to mark the
record as being returned Broken and set the Broken field to True.
On the form where Tools are checked out, I would suggest one of two things.
The easiest cure would be to use a query on the Tool_List table instead of
the table itself as the form's record source and only include records for
tools that are not broken.

If it is necessary to show the tool in the list, even if it is broken, I
would use the form Current event to display a mesage box saying the tool is
broken and disable whatever control is used to check out the tool. For
example, let's say you have a Check Out command button named cmdCheckOut and
a check box name chkBroken that tells you the tool is broken. Code in the
form Current event would look something like:

If me.chkBroken Then
MsgBox "This Tool is Broken and Unavailable for Checkout",
vbInformation
Me.cmdCheckOut.Enabled = False
Else
Me.cmdCheckOut.Enabled = True
End If