multiple checks before an action

[Chief]

Hello,
I have some code which works perfectly for what I want. (This is it below)

Private Sub LblPShop_Complete_Click()
        If ChkPShop_Complete = True Then
            Dim iResponse As String
            'Ask if they are sure they want to change it and store their response in iResponse.
            iResponse = MsgBox("Do you wish to change the status of this job for Paint Shop Complete?  ", vbYesNo + vbExclamation + vbApplicationModal + vbDefaultButton1, "Change Manufacturing.")
            
            Select Case iResponse
                Case vbYes: 'They answered Yes
                    Dim strPassword As String
                    strPassword = InputBox("Please enter the admin password", "Change Status of Manufacturing") 'get password with input box
                    If strPassword = "Haus" Then
                        ChkPShop_Complete = False 'sets the check box to false.
                        Form_JobDetailF.TxtPShop_Complete = ""
                        LblPShop_Complete.Caption = ""
                    Else
                        Call MsgBox("The password you entered is incorrect.  ", vbOKOnly + vbCritical + vbApplicationModal + vbDefaultButton1, "Incorrect Password")
                    End If
                Case vbNo: 'If they select no to changing it it does nothing.
        
            End Select
            
        Else ' it is false
            ChkPShop_Complete = True
            Form_JobDetailF.TxtPShop_Complete = Date
            LblPShop_Complete.Caption = Chr(252)
        End If

End Sub

However on some items I need to implement further checks. The way I have written it isn't clean and not doing exactly what I want.
It sets the focus, changes the colors etc., however not giving me the message box, and when I make the check true, it doesnt then go on to check it off.
I will have a few checks to look at, not just this one. (Code I Have is)

Private Sub LblMShop_Complete_Click()
        If ChkMShop_Complete = True Then
            Dim iResponse As String
            'Ask if they are sure they want to change it and store their response in iResponse.
            iResponse = MsgBox("Do you wish to change the status of this job for Machine Shop Complete?  ", vbYesNo + vbExclamation + vbApplicationModal + vbDefaultButton1, "Change Manufacturing.")
            
            Select Case iResponse
                Case vbYes: 'They answered Yes
                    Dim strPassword As String
                    strPassword = InputBox("Please enter the admin password", "Change Status of Machine Shop") 'get password with input box
                    If strPassword = "Haus" Then
                        ChkMShop_Complete = False 'sets the check box to false.
                        Form_JobDetailF.TxtMShop_Complete = ""
                        LblMShop_Complete.Caption = ""
                    Else
                        Call MsgBox("The password you entered is incorrect.  ", vbOKOnly + vbCritical + vbApplicationModal + vbDefaultButton1, "Incorrect Password")
                    End If
                Case vbNo: 'If they select no to changing it it does nothing.
        
            End Select
            
        Else ' it is false
            'check bid
            Dim txtmessage As String
            txtmessage = ""
                If ChkBIDs = True Then
                    If Len(Nz(Me.TxtHingeDrill)) = 0 Then
                        txtmessage = "Hinge Drill is not complete for bought in doors." & vbCrLf & txtmessage
                        Me.TxtHingeDrill.BackColor = vbRed
                        Me.TxtHingeDrill.ForeColor = vbWhite
                        Me.TxtHingeDrill.SetFocus
                    Else
                        Me.TxtHingeDrill.BackColor = vbWhite
                        Me.TxtHingeDrill.ForeColor = vbBlack
                End If
        Else
            ChkMShop_Complete = True
            Form_JobDetailF.TxtMShop_Complete = Date
            LblMShop_Complete.Caption = Chr(252)
        End If
    End If
End Sub

 

[theDBguy]

Just curious, have you tried stepping through the code to see what it's actually doing? You might be able to see if it's skipping any steps.

 

[Chief]

Just curious, have you tried stepping through the code to see what it's actually doing? You might be able to see if it's skipping any steps.

Stepping thru, txtmessage is saying 0 instead of the message
then skips over the check complete value at the end.
if hingedrill is complete, the function stops working all together.

 

[arnelgp]

you don't have MsgBox to display the message.
also add code to the Change event of TxtHingeDrill.
If there is value, check the adjacent checkbox.

 

[Chief]

you don't have MsgBox to display the message.
also add code to the Change event of TxtHingeDrill.
If there is value, check the adjacent checkbox.

yes, bugga.. ok getting message box now, thank you..

but not getting any action on the complete now equals true if txthinge is not 0

Have I written this correctly? with the else then the other ifs, with another else?
Doesn't look right or clean, especially as I will be adding more checks.

thanks

 

[arnelgp]

you need a Code on the Change or BeforeUpdate or AfterUpdate of the txtHinge.
check the length again and remove the red color if there is date and check the adjacent checkbox.

 

[Pat Hartman]

If you want to validate a field, the event you should use is the FORM's BeforeUpdate event. For something like ensuring uniqueness where you want the user to know immediately that there is an error, you could use the control's BeforeUpdate event but you would still need validation code in the Form's BeforeUpdate event.

Validating in the AfterUpdate event is akin to closing the barn door AFTR the horses have escaped.

Validating in the Change is pretty special purpose since the code runs multiple times. Each time a character is typed, the code runs again soo this event is usually used if you want precise control over length and/or format as you are typing but you will probably still need code in the form's BeforeUpdate event to stop the save if there is any invalid or missing data. Missing can ONLY be determined with certainty in the FORM's BeforeUpdate event. No control event can do this since if the control is never entered/changed, the control level events don't fire.

As the code currently exists in LblMShop_Complete_Click(), you may get error messages but nothing will stop the bad data from being saved.

It is important to understand what form events are designed for and to use the correct event for your purpose. The single important event is the Form's BeforeUpdate event. You might get lucky if you put code in enough other events to stop a record from saving but doing the validation in the correct event is far less effort and is certain to prevent the record from being saved as long as you remember to sett he Cancel argument to True whenever you find an error.

If Me.SomeField & "" = "" Then
    Msgbox "Some Field is required.",vbONOnly
    Me.SomeField.SetFocus 
    Cancel = True
    Exit Sub
End IF

Cancel doesn't undo the changes. It just tells Access to not save the record but it leaves the record dirty so the user either has to fix the error or back out his update to close the form.
You can use:
Me.Undo
to back out the changes but I never do this unless I am not going to let the user save any changes because he doesn't have the authorization to change anything. Otherwise, Me.Undo is just to punitive so I only use it in that one case. Or if I give the user an option by asking him directly if he wants to cancel the update.