How to continue working on current Form after Ok was clicked on msgBox (1 Viewer)

[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)>

 

[Pat Hartman]

dump the BeforeUpdate event...it is a pain.

I'm not looking to start a fight but I can't in good concience let this pass. That is just about the most dangerous pices of advice I have ever seen offered here.

The BeforeUpdate event is your FRIEND. It is the last event that runs prior to a record being saved and it does not matter what caused the record to be saved. Think of it as the flapper at the bottom of a funnel. If your validation finds an error, you keep that flapper closed tight with
Cancel = True
Otherwise, you let the event complete and Access will save the data.

You don't need to keep all the code in the BeforeUpdate event. You can call other subs or functions. Some data can be validated in the control's BeforeUpdate event. You just have to understand that if a control never got the focus, NO control level event code will ever run. Or specifically if the control was not changed then the BeforeUpdate event That implies that code that checks for presence will never work reliably in a control level event and therefore must be validated in the Form level event.

I once removed over 5,000 lines of validation code from a form. The "programmer" did not understand events and had ultimately duplicated ALL validation code for ALL controls on the form in at least 5 control level events for every control plus a couple of form level events - but NOT ever in the control's BeforeUpdate event or in the Form's BeforeUpdate event and he never understood that just because he was displaying error messages, his validation code was not preventing records from being saved.

@Srussom,
Your code is probably not working correctly because one or more of the controls contains a ZLS (Zero Length String). If a user uses the backspace key or space key to empty out a field which previously held data, the result is ZLS rather than null. Therefore, when validating a form, it is best to take that into account. You should also do actual validation on dates. You have no idea how many years like "204" or "248" you end up if you don't validate for reasonableness.

If Me.PPMemebershipID & "" = "" Then
    Me.PPMemebershipID.SetFocus
    Cancel = True
    MsgBox "PPMembershipID is required.",vbOKOnly
    Exit Sub
End If
If Me.PFirstName & "" = "" Then
    Me.PFirstName.SetFocus
    Cancel = True
    MsgBox "PFirstName is required.", vbOKOnly
    Exit Sub
End If
...
If IsDate(Me.PDateOfBirth) Then
    If Me.PDateOfBirth > Date() Then
        Me.PDateOfBirth.SetFocus
        Cancel = True
        MsgBox "DOB is invalid.  It may not be in the future.", vbOKOnly
        Exit Sub
    Else
        If DateDiff("yyyy", PDateOfBirth, Date()) > 100 Then
            Msgbox "This person is > 100 years old?", vbYesNo Then
                Me.PDateOfBirth.SetFocus
                Cancel = True
                Exit Sub
        End If
        If DateDiff("yyyy", PDateOfBirth, Date()) > 85 Then
            If Msgbox("This person is > 85 years old?  Is that correct?", vbYesNo) = vbNo Then
                Me.PDateOfBirth.SetFocus
                Cancel = True
                Exit Sub
            End If
        Else        
            If DateDiff("yyyy", PDateOfBirth, Date()) > 65 Then
                If Msgbox("This person is > 65 years old?  Is that correct?", vbYesNo) = vbNo Then
                    Me.PDateOfBirth.SetFocus
                    Cancel = True
                    Exit Sub
                End If
            End If
        End If
Else
    Msgbox "DOB is required.", vbOKOnly
    Me.PDateOfBirth.SetFocus
    Cancel = True
    Exit Sub
End If

.....