Show message box Before Update based on two values in a form

[Ruth]

I'm posting a link to my earlier question about this problem as it is
now on the 9th page of questions and not likely to get any responses.
I made code changes suggested by Jeanette, but I'm still not getting
the message box or even a debug message.

Help please?

http://groups.google.com/group/micr...2f57a/bb3146eb5be25f88?hl=en#bb3146eb5be25f88

Thanks!
Ruth H.

 

[Beetle]

Hello Ruth,

In your previous post you indicated you have a control named cboSequence
and another named Pass, which is a checkbox.

The name cboSequence would imply that it is a combo box. Combo boxes
can store something other than what they actually display, so I'm wondering
if this might be the case here. Do you have a table of Sequence values that
cboSequunce uses as a row source? If so, do you have a SequenceID and
a Sequence description, and perhaps your combo box is storing the ID
but displaying the description?

If that is the case, corrected code might look like;

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim stUpdate As String
Dim stMessage As String

stMessage = "Marking the Final Inspection as Passed will" & vbCrLf
stMessage = stMessage & "lock this part to further edits. Do you want to
continue?"

stUpdate = "UPDATE tblPart SET tblPart.Locked = ""Yes"" WHERE
(((tblPart.Part_AN)=[Forms]![frmPart]![Part_AN]));"

If Me!cboSequence = 1 And Me!Pass = True Then
If MsgBox(stMessage, vbQuestion + vbYesNo) = vbNo Then
Cancel = True
Else
CurrentDb.Execute stUpdate, dbFailOnError
Me.Requery
End If
End If

End Sub



Assuming that 1 is the SequenceID for "Final"

HTH

_________

Sean Bailey

 

[Arvin Meyer MVP]

Your code reads:

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

Replace that with:

Dim x

x = MsgBox("Marking the Final Inspection as Passed will" & vbCrLf & "lock
this part to further edits. Do you want to continue?", vbYesNo + vbQuestion)

If x = vbYes Then
' Do you thing
Else
' Do your other thing
End If

 

[Ruth]

Hi Sean and Arvin,
I modified my code using both of your suggestions and am still not
getting any reaction! I also tried changing the If value of the check
box to IsNotNull, thinking that maybe "Yes" was not registering.

Any other ideas?

Many, many thanks for your time and suggestions,
Ruth

 

[Arvin Meyer MVP]

There is no check box in a MsgBox function.

Also, the syntax would be Not IsNull(Me.txtControlName)

I prefer to use:

If Len(Me.txtControlName & vbNullString) > 0 Then

which covers both conditions (Null and empty string)

 

[Ruth]

Hi Arvin,
Thanks for your reply! The checkbox I was referring to was not the one
in the message box, but the checkbox on the form where one marks the
inspection as passed. I changed it to "True" as Sean suggested (I
missed that earlier) and now I am getting the message box! And
clicking OK does kick off the sql string to do the update. I had to
change my code a little bit (Yes for the locked checkbox was in quotes
and it didn't like that), but it is updating fine.

I wanted the subform to lock after the locked property is updated, so
I added the code to disable the controls.

Here's what the final code looks like:
======================================================
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stUpdate As String
stUpdate = "UPDATE tblPart SET tblPart.Locked = Yes WHERE
(((tblPart.Part_AN)=[Forms]![frmPart]![frmPartTasks].[Form]!
[Part_AN]));"


If Me.Sequence = "Final" And Me.Pass = True Then
Dim x

x = MsgBox("Marking the Final Inspection as Passed will" & vbCrLf &
"lock this part to further edits. Do you want to continue?", vbYesNo +
vbQuestion)

If x = vbYes Then
DoCmd.RunSQL stUpdate
Me.Date.Enabled = False
Me.Task.Enabled = False
Me.cboSequence.Enabled = False
Me.By.Enabled = False
Me.Pass.Enabled = False
Me.ToLoc.Enabled = False
Me.Notes.Enabled = False
Else
Cancel = True
Me.Undo

End If
End If

End Sub
====================================================
It works beautifully, the Part Tasks subform is locked and all is good
with the world. There is just one little "bug" I would like to fix if
you have an idea on this. If I click "No" and go back to the record
(this gives the operator the chance to change to another inspection
type or uncheck the inspection as passed), it seems like once Access
has executed the code on that record, it will allow it to save without
running the update query. The Cancel and Undo lines up there in the
Else statement leave Passed checked and return to the cursor to the
top record in the subform. Even if I tab through the record with
"Final" selected and "Passed" checked, it doesn't run the code again.
What would be the best way to approach this? Thank you for your
responses. You've helped me so much! I've been working on this off and
on for at least a couple of months!

Regards,
Ruth

 

[Arvin Meyer MVP]

To make your coding easier try using my generic locking code at:

http://www.datastrat.com/Code/LockIt.txt

Then set the form to Dirty, which will save it before running the code. So:

If Me.Dirty = True Then Me.Dirty = False

will force a save of a dirty record or:

DoCmd.RunCommand acCmdSaveRecord

will force a save under any condition that won't violate validation.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hi Arvin,
Thanks for your reply! The checkbox I was referring to was not the one
in the message box, but the checkbox on the form where one marks the
inspection as passed. I changed it to "True" as Sean suggested (I
missed that earlier) and now I am getting the message box! And
clicking OK does kick off the sql string to do the update. I had to
change my code a little bit (Yes for the locked checkbox was in quotes
and it didn't like that), but it is updating fine.

I wanted the subform to lock after the locked property is updated, so
I added the code to disable the controls.

Here's what the final code looks like:
======================================================
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stUpdate As String
stUpdate = "UPDATE tblPart SET tblPart.Locked = Yes WHERE
(((tblPart.Part_AN)=[Forms]![frmPart]![frmPartTasks].[Form]!
[Part_AN]));"


If Me.Sequence = "Final" And Me.Pass = True Then
Dim x

x = MsgBox("Marking the Final Inspection as Passed will" & vbCrLf &
"lock this part to further edits. Do you want to continue?", vbYesNo +
vbQuestion)

If x = vbYes Then
DoCmd.RunSQL stUpdate
Me.Date.Enabled = False
Me.Task.Enabled = False
Me.cboSequence.Enabled = False
Me.By.Enabled = False
Me.Pass.Enabled = False
Me.ToLoc.Enabled = False
Me.Notes.Enabled = False
Else
Cancel = True
Me.Undo

End If
End If

End Sub
====================================================
It works beautifully, the Part Tasks subform is locked and all is good
with the world. There is just one little "bug" I would like to fix if
you have an idea on this. If I click "No" and go back to the record
(this gives the operator the chance to change to another inspection
type or uncheck the inspection as passed), it seems like once Access
has executed the code on that record, it will allow it to save without
running the update query. The Cancel and Undo lines up there in the
Else statement leave Passed checked and return to the cursor to the
top record in the subform. Even if I tab through the record with
"Final" selected and "Passed" checked, it doesn't run the code again.
What would be the best way to approach this? Thank you for your
responses. You've helped me so much! I've been working on this off and
on for at least a couple of months!

Regards,
Ruth

 

[Ruth]

I'm afraid I'm not following you.
=============================================
Quote:
Then set the form to Dirty, which will save it before running the
code. So:

If Me.Dirty = True Then Me.Dirty = False

will force a save of a dirty record or:

DoCmd.RunCommand acCmdSaveRecord

will force a save under any condition that won't violate validation.
=============================================

I don't want it to save. I thought about it last night after I
replied, and I'm sure what is happening is that the code is attached
to the "Before Update" event. Once it has run before the record
updated, it does not run again. So "Before Update" is the only place
validation is taking place. If I place the If Me Dirty statement
before the code runs, ??? Again, I'm sorry I'm not following the logic
here. Am I correct that you want me to put that before the start of If
Me.Sequence="Final".... part of the code? Sorry to be slow on this!

Regards and thanks!
Ruth

To make your coding easier try using my generic locking code at:

http://www.datastrat.com/Code/LockIt.txt

Then set the form to Dirty, which will save it before running the code. So:

If Me.Dirty = True Then Me.Dirty = False

will force a save of a dirty record or:

DoCmd.RunCommand acCmdSaveRecord

will force a save under any condition that won't violate validation.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com


Hi Arvin,
Thanks for your reply! The checkbox I was referring to was not the one
in the message box, but the checkbox on the form where one marks the
inspection as passed. I changed it to "True" as Sean suggested (I
missed that earlier) and now I am getting the message box! And
clicking OK does kick off the sql string to do the update. I had to
change my code a little bit (Yes for the locked checkbox was in quotes
and it didn't like that), but it is updating fine.

I wanted the subform to lock after the locked property is updated, so
I added the code to disable the controls.

Here's what the final code looks like:
======================================================
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stUpdate As String
stUpdate = "UPDATE tblPart SET tblPart.Locked = Yes WHERE
(((tblPart.Part_AN)=[Forms]![frmPart]![frmPartTasks].[Form]!
[Part_AN]));"

    If Me.Sequence = "Final" And Me.Pass = True Then
        Dim x

x = MsgBox("Marking the Final Inspection as Passed will" & vbCrLf &
"lock this part to further edits. Do you want to continue?", vbYesNo +
vbQuestion)

    If x = vbYes Then
                DoCmd.RunSQL stUpdate
           Me.Date.Enabled = False
                Me.Task.Enabled = False
                Me.cboSequence.Enabled = False
                Me.By.Enabled = False
                Me.Pass.Enabled = False
                Me.ToLoc.Enabled = False
                Me.Notes.Enabled = False
            Else
                Cancel = True
                Me.Undo

    End If
    End If

End Sub
====================================================
It works beautifully, the Part Tasks subform is locked and all is good
with the world. There is just one little "bug" I would like to fix if
you have an idea on this. If I click "No" and go back to the record
(this gives the operator the chance to change to another inspection
type or uncheck the inspection as passed), it seems like once Access
has executed the code on that record, it will allow it to save without
running the update query. The Cancel and Undo lines up there in the
Else statement leave Passed checked and return to the cursor to the
top record in the subform. Even if I tab through the record with
"Final" selected and "Passed" checked, it doesn't run the code again.
What would be the best way to approach this? Thank you for your
responses. You've helped me so much! I've been working on this off and
on for at least a couple of months!

Regards,
Ruth

There is no check box in a MsgBox function.

Also, the syntax would be Not IsNull(Me.txtControlName)

I prefer to use:

If Len(Me.txtControlName & vbNullString) > 0 Then

which covers both conditions (Null and empty string)

 

[Arvin Meyer MVP]

I think I may have misunderstood. If you cancel the before update event, it
should run again if you try to save the record again. Normally, I also add a
SetFocus statement right after I do Me.Undo so that I can direct the user to
the control that (s)he needs to change.

One other thing: A subform is a control on the form. Once you enter the
subform, the main form automatically saves and validation code occurs, the
same is true when you click out of a subform into a mainform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I'm afraid I'm not following you.
=============================================
Quote:
Then set the form to Dirty, which will save it before running the
code. So:

If Me.Dirty = True Then Me.Dirty = False

will force a save of a dirty record or:

DoCmd.RunCommand acCmdSaveRecord

will force a save under any condition that won't violate validation.
=============================================

I don't want it to save. I thought about it last night after I
replied, and I'm sure what is happening is that the code is attached
to the "Before Update" event. Once it has run before the record
updated, it does not run again. So "Before Update" is the only place
validation is taking place. If I place the If Me Dirty statement
before the code runs, ??? Again, I'm sorry I'm not following the logic
here. Am I correct that you want me to put that before the start of If
Me.Sequence="Final".... part of the code? Sorry to be slow on this!

Regards and thanks!
Ruth

To make your coding easier try using my generic locking code at:

http://www.datastrat.com/Code/LockIt.txt

Then set the form to Dirty, which will save it before running the code.
So:

If Me.Dirty = True Then Me.Dirty = False

will force a save of a dirty record or:

DoCmd.RunCommand acCmdSaveRecord

will force a save under any condition that won't violate validation.
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com


Hi Arvin,
Thanks for your reply! The checkbox I was referring to was not the one
in the message box, but the checkbox on the form where one marks the
inspection as passed. I changed it to "True" as Sean suggested (I
missed that earlier) and now I am getting the message box! And
clicking OK does kick off the sql string to do the update. I had to
change my code a little bit (Yes for the locked checkbox was in quotes
and it didn't like that), but it is updating fine.

I wanted the subform to lock after the locked property is updated, so
I added the code to disable the controls.

Here's what the final code looks like:
======================================================
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stUpdate As String
stUpdate = "UPDATE tblPart SET tblPart.Locked = Yes WHERE
(((tblPart.Part_AN)=[Forms]![frmPart]![frmPartTasks].[Form]!
[Part_AN]));"

If Me.Sequence = "Final" And Me.Pass = True Then
Dim x

x = MsgBox("Marking the Final Inspection as Passed will" & vbCrLf &
"lock this part to further edits. Do you want to continue?", vbYesNo +
vbQuestion)

If x = vbYes Then
DoCmd.RunSQL stUpdate
Me.Date.Enabled = False
Me.Task.Enabled = False
Me.cboSequence.Enabled = False
Me.By.Enabled = False
Me.Pass.Enabled = False
Me.ToLoc.Enabled = False
Me.Notes.Enabled = False
Else
Cancel = True
Me.Undo

End If
End If

End Sub
====================================================
It works beautifully, the Part Tasks subform is locked and all is good
with the world. There is just one little "bug" I would like to fix if
you have an idea on this. If I click "No" and go back to the record
(this gives the operator the chance to change to another inspection
type or uncheck the inspection as passed), it seems like once Access
has executed the code on that record, it will allow it to save without
running the update query. The Cancel and Undo lines up there in the
Else statement leave Passed checked and return to the cursor to the
top record in the subform. Even if I tab through the record with
"Final" selected and "Passed" checked, it doesn't run the code again.
What would be the best way to approach this? Thank you for your
responses. You've helped me so much! I've been working on this off and
on for at least a couple of months!

Regards,
Ruth

There is no check box in a MsgBox function.

Also, the syntax would be Not IsNull(Me.txtControlName)

I prefer to use:

If Len(Me.txtControlName & vbNullString) > 0 Then

which covers both conditions (Null and empty string)