Update "locked" field in record when final inspection is passed

[Ruth]

I have a database for a manufacturing process that tracks where a part
is during the build. I have a table (tblParts) with the PartID and
other information related to the part. The table also contains a field
named "Locked."

There is another table for part tasks (tblTasks). I have a main form
that shows the overall part information in the top, and then has a
continuous subform with task details below that. Fields are: TaskID,
Date, cboTask, cboSequence, By, Pass (checkbox) and cboToLoc.

When I exit a task record, I want to check if cboSequence = "Final"
and Pass = "Yes" then a message box displays that tells the user that
marking a final inspection as passed will lock the record to further
edits.Yes to continue, no to go back to the record. If "Final" and
"No" (or anything but "Final" and "Yes") the cursor will just tab
normally to create the new task record. If the answer to the message
box is "yes" it will run a query to update the Part record's Locked
value to Yes, and refresh the form to show that it is now locked.

I have the code written to disable the form controls when locked is
set to yes, but I've been doing that through making the entry in the
Part table.

This is the code I've got so far (attached to the subform Before
Update event) and none of it is working. It's not giving me the
message box and it's not running the update query. I'm definitely a
novice at this but can usually work the code out if given a little
direction. I remmed out the Me.Dirty lines because I don't know if
that's the right way to go. Anyone have a push in the right direction
for me?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stUpdate As String
stUpdate = "UPDATE tblPart SET tblPart.Locked = ""Yes"" WHERE
(((tblPart.Part_AN)=[Forms]![frmPart]![Part_AN]));"

If Sequence = "Final" And Pass = "Yes" Then
If MsgBox(("Marking the Final Inspection as Passed will")
& vbLf&("lock this part to further edits. Do you want to continue?"),
vbQuestion + vbYesNo) = vbYes Then
DoCmd.Echo EchoOff
DoCmd.RunSQL stUpdate
DoCmd.RepaintObject
DoCmd.Echo EchoOff
Else
'If Me.Dirty = True Then ?????
End If
End If

End Sub

Thanks,
Ruth

 

[Jeanette Cunningham]

Hi Ruth,
You have controls called cboSequence, Pass (checkbox)

Replace this line-->
If Sequence = "Final" And Pass = "Yes" Then

with-->
If Me.cboSequence = "Final" And Me.Pass = "Yes" Then

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ruth]

Hi Jeanette,
Sorry for the delay, but I am just now getting back to this project.

Thanks for your suggestion-- I tried it, but got the same result. :-/
I know that the code is at least being "inspected" now (for lack of a
better term) because I had a misplaced ampersand in the message box
text. Before, I was not getting a debug message because of it, and
this time I did. But, alas, no message box when I marked an inspection
as final and passed.

I'm trying to think of another way to "skin this cat," and am
certainly open to suggestions!

Could the problem be that this action is on a sub form and that I need
to further specify it as such? The code is attached to the "before
update" event on the subform... I'm just trying to think of reasons it
is not working.

Any other ideas? Anyone?

Thanks,
Ruth H.