How to continue working on current Form after Ok was clicked on msgBox

[Srussom]

Hi guys, I need your help.
I have created Before Update msg box on my data entry Form to show that if all the mandatory fields are completed. when the msg box showns and Ok was clicked I want the form to remain open so that I can continue to woke on the form. However the okay button is closing the data entry form. How can I continue working on the data entry form and prevent the ok button from closing the entry Form.

my code currently is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (IsNull(Me.PPMemebershipID)) Or (IsNull(Me.PFirstName)) Or (IsNull(Me.PLastName)) Or (IsNull(Me.PGender)) Or (IsNull(Me.PDateOfBirth)) Or (IsNull(Me.PDateJoined)) Or (IsNull(Me.PHomePhone)) Or (IsNull(Me.PMobilePhone)) Or (IsNull(Me.PKinFirstName)) Or (IsNull(Me.PKinLastName)) Or (IsNull(Me.PKinTelephone)) Or (IsNull(Me.PKinRelationship)) Then
If MsgBox("Membership ID, First Name, Last Name, Gender, Date of Birth, Date Joined, Home Phone, Mobile Phone, Next of Kin First Name, Next of Kin Last Name, Next of Kin Telephone, Next of Relationship ARE ALL MANDATORY. PLEASE ENTER VALUES IN ALL OF THESE FILEDS", vbOKOnly + VBA.VbMsgBoxStyle.vbExclamation) = vbOK Then

Me.PPMemebershipID.SetFocus
Cancel = True
End If
End If
End Sub

I must tell you that I also have Update and Exit button. in the Update button the code is:
Private Sub Command37_Click()
DoCmd.Close acForm, "InputMain"

and Exit button is:
Private Sub Command38_Click()
Me.Undo

Regards
Solomon

 

[Ranman256]

dump the BeforeUpdate event...it is a pain.
Instead, use code to validate the form
I do my checks via code (not in the field property)
All in 1 place, easier to change...say a SAVE_click event,
dont allow the form to close until all is well...

usage:
if IsValidForm() then SaveData

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(cboWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "User name is missing"
   Case IsNull(txtEmail)
      vMsg = "Email field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

 

[isladogs]

Ranman
Can you explain the last line of your function
You seem to be setting a Boolean function to a ZLS.
Am I missing something?

 

[Srussom]

Hi Ranman256,
Thank you for your quick reply. I tried your code but it is not working. it saved the form with the missing fields.

Thanks again for your help

Regards

Solomon

 

[Gasman]

Ranman
Can you explain the last line of your function
You seem to be setting a Boolean function to a ZLS.
Am I missing something?

Isn't just a test to see if the string has something?
If blank all is good, if not, and error has occurred?

 

[Gasman]

I also thought the Form AfterUpdate event was the place to check all is good on the form?

 

[isladogs]

Hi Gasman

That's how I interpreted it but that line just looks odd - at least it does to me.
One drawback of this code is it will only indicate the first blank field.

If that's an issue then here is some alternative code which the OP could adapt.
It highlights empty fields & builds a message listing those that need attention.
Its not as succinct however!

See attached screenshot

In this example, empty fields are checked when the Send Email button is clicked:

Private Sub cmdEmail_Click()

    CheckComplete
    If CheckFlag = False Then Exit Sub

  'other code here to send email if CheckComplete is true

End Sub

Code to check the controls:

Sub CheckComplete()

'build up a string of controls that have not been entered
'colour coding added to make unfilled controls 'stand out' clearly

On Error GoTo Err_Handler

    CheckFlag = True
    
    strSelection = ""
    
    'check required controls have been populated
    If Nz(Me.txtSendTo, "") = "" Then
        strSelection = strSelection & vbCrLf & vbTab & " - Email Recipient"
        Call FormatEmptyControls("txtSendTo", "LblSendTo")
    Else
        Call FormatFilledControls("txtSendTo", "LblSendTo")
    End If
    
    If Nz(Me.txtSubject, "") = "" Then
        strSelection = strSelection & vbCrLf & vbTab & " - Email Subject"
        Call FormatEmptyControls("txtSubject", "LblSubject")
    Else
       Call FormatFilledControls("txtSubject", "LblSubject")
        
    End If
    
    If Nz(Me.txtMessageText, "") = "" Then
       strSelection = strSelection & vbCrLf & vbTab & " - Email Message"
       Call FormatEmptyControls("txtMessageText", "LblMessageText")
    Else
       Call FormatFilledControls("txtMessageText", "LblMessageText")
    End If
    
    If strSelection <> "" Then
        CheckFlag = False
        Dim strMsg As String
        strMsg = "You have not completed all the information required" & _
                vbCrLf & vbCrLf & "Click 'OK' then enter the following:" & _
                strSelection & vbCrLf & vbCrLf & _
                "OR click 'Cancel' to close this form without sending an email."
        If MsgBox(strMsg, vbCritical + vbOKCancel, "Required data missing") = vbCancel Then
            cmdClose_Click
            'Exit Sub
        End If
    End If
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
   dMsgBox "Error " & Err.Number & " in CheckComplete procedure :             " & Err.description, vbCritical, "Program error"
    Resume Exit_Handler
    
End Sub

The empty controls are highlighted using

Sub FormatEmptyControls(str1 As String, str2 As String)

On Error GoTo Err_Handler

    'used to change appearance of 'required controls' which are empty
    Me(str1).BackColor = vbCyan
    Me(str2).FontBold = True
    Me(str2).ForeColor = 128 'dark red
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
   MsgBox "Error " & Err.Number & " in FormatEmptyControls procedure :             " & Err.description, vbCritical, "Program error"
    Resume Exit_Handler

End Sub

Sub FormatFilledControls(str1 As String, str2 As String)

'This clears highlighting if the control has now been filled

On Error GoTo Err_Handler

    'used to restore appearance of 'required controls' which are filled
    Me(str1).BackColor = vbWhite '10092543 'pale yellow
    Me(str2).FontBold = False
    Me(str2).ForeColor = 6697728 'dark blue
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
   MsgBox "Error " & Err.Number & " in FormatFilledControls procedure :             " & Err.description, vbCritical, "Program error"
    Resume Exit_Handler

End Sub

 

[isladogs]

I also thought the Form AfterUpdate event was the place to check all is good on the form?

Some of the most experienced developers here use before update for validation checks as 'prevention is better than cure'.

Whilst I accept the principle, personally I tend not to use it that much.

 

[missinglinq]

I also thought the Form AfterUpdate event was the place to check all is good on the form?

The AfterUpdate event is too late...the Record has already been saved, at that point!

As ridders said, Validation is normally done in the Form_BeforeUpdate event, the last event before a Record is saved...if a Validation test fails, you use

Cancel = True

and the Record isn't saved, giving the user time to correct their mistake.

Linq ;0)>