Form closes before Mandatory fields filled

[kirsco]

I'm back again!

I have three mandatory fields. I have some code against the Before_Update part of the form, and while the msgboxes are appearing, when I click on the OK, rather than making the user go back to complete the field, the form closes.

Can someone tell me what I'm doing wrong?

I want them to have to fill all three fields out before the form can be saved, or else leave the form without saving.

I thought I had this one sussed!

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Check mandatory field Summary
If IsNull(Summary_Cause) Or Summary_Cause = "" Then
MsgBox "Please fill the Summary Field", vbOKOnly + vbDefaultButton1, "Missing Data"
Me.Summary_Cause.SetFocus
Cancel = True
Exit Sub
End If
' Check mandatory field Details
If IsNull(Cause_of_Problem) Or Cause_of_Problem = "" Then
MsgBox "Please fill the Details Field", vbOKOnly + vbDefaultButton1, "Missing Data"
Me.Cause_of_Problem.SetFocus
Cancel = True
Exit Sub
End If
' Check mandatory field Action/Ideas
If IsNull(Action_Comments) Or Action_Comments = "" Then
MsgBox "Please fill the Action/Ideas Field", vbOKOnly + vbDefaultButton1, "Missing Data"
Me.Action_Comments.SetFocus
Cancel = True
Exit Sub
End If
End Sub

 

[Trevor G]

Take a look at the below, I am using the Tag element of field properties to
Check Fields Validation

Create a form and select design view. Select the Fields you need to be completed. Open the properties and select Tag (Check the other Tab). Add a word in this case “Vital”.

Private Sub cmdButton_Click()
Dim ctl as Control
Dim Flag as Boolean

For Each ctl in Form_Name
If ctl.tag=”vital” Then
If isNull(ctl.Value) Then
Ctl.BackColor=vbRed
Flag=True
Else
Ctl.BackColor=vbWhite
End If
End IF
Next
If Flag = True Then
MsgBox”Please Enter data in the fields indicated with a red background"
Else
DoCmd.RunCommand acCmdSaveRecord
End If

 

[dragofly]

To be honest I would setup the form based on a table where I would set the fields to required and let access do the work for me. In this case if you try to go on without filling in the data a msgbox will appear and then the focus will be brought back to the frirst control with missing data and if you try to close the msgbox will appear gtiving you the error and then another will say you cannot save this entry do you want to close the form anyway.

Alternately you may need to use the form_close sub and set cancel to true to prevent it from closing. Not sure how without setting the field property to required in the table def to prevent saving unless the before_update is doing that for you and then you only need to give a msgbox with vbYesNo option asking if they want to close the form without saving and look for vbYes to allow closure.

 

[ghudson]

I would put the code to verify Nulls in required fields into a function and call it when needed. Below is something I use to do what you want. I have typed "Required" in the tag property of each field I required the user to complete before I will allow the user to save the record.

'Used to determine if current record can be saved
Dim booProperSave As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    Call VerifyRequiredFieldsAreCompleted

    If booProperSave = False Then
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
        DoCmd.CancelEvent
        Exit Sub
    End If

Exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Form_BeforeUpdate()"
    Resume Exit_Form_BeforeUpdate

End Sub

Private Function VerifyRequiredFieldsAreCompleted()
On Error GoTo Err_VerifyRequiredFieldsAreCompleted
    
    Dim ctl As Control
    
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If ctl.Tag = "Required" And IsNull(ctl) Or ctl.Tag = "Required" And ctl = "" Then
                ctl.SetFocus
                MsgBox "A required field is null.  You must complete the ''" & ctl.ControlTipText & "'' field.", vbCritical, "Missing Required Data"
                booProperSave = False
                Exit For
            Else
                booProperSave = True
            End If
        End If
    Next

Exit_VerifyRequiredFieldsAreCompleted:
    Exit Function

Err_VerifyRequiredFieldsAreCompleted:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "VerifyRequiredFieldsAreCompleted()"
    Resume Exit_VerifyRequiredFieldsAreCompleted

End Function

 

[kirsco]

Hi Everyone

Thanks for all of the suggestions - they all work, but no matter what I do, once the 'warning' appears and I press the 'OK' button, it just closes out of the form.

I would like it to go back to the empty field - I understand this is to do with the setfocus feature.

 

[dbDamo]

You are missing the "Me." identifiers in front of your controls. You should also use the Len(Nz function to test for null or empty string. Here is the amended code:-

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Check mandatory field Summary
If Len(Nz(Me.Summary_Cause,"")) = 0 Then
    MsgBox "Please fill the Summary Field", vbOKOnly + vbDefaultButton1, "Missing Data"
    Me.Summary_Cause.SetFocus
    Cancel = True
    Exit Sub
End If
' Check mandatory field Details
If Len(Nz(Me.Cause_of_Problem,"")) = 0 Then
    MsgBox "Please fill the Details Field", vbOKOnly + vbDefaultButton1, "Missing Data"
    Me.Cause_of_Problem.SetFocus
    Cancel = True
    Exit Sub
End If
' Check mandatory field Action/Ideas
If Len(Nz(Me.Action_Comments,"")) = 0 Then
    MsgBox "Please fill the Action/Ideas Field", vbOKOnly + vbDefaultButton1, "Missing Data"
    Me.Action_Comments.SetFocus
    Cancel = True
    Exit Sub
End If
End Sub

 

[kirsco]

Hi DBDamo

The 'Please fill the details field' comes up as expected, but as soon as I click the OK button, the form closes with no warnings at at all. It doesn't focus back to the field.

 

[dbDamo]

With my code?

 

[kirsco]

Yes - with your code. I guess I must have something else that is making it close.

 

[dbDamo]

Have you also placed the code in your save button's OnClick event?

 

[kirsco]

No - I'll give that a go.

 

[dbDamo]

That should solve it. If not, please also show me the code behind the save button.

 

[kirsco]

Hi

There isn't really a save button. There is an exit button, which has a pile of code against it.

I'm re-working someone elses DB, and there are no save buttons throughout the entire thing.

I'm guessing they would be useful.

Private Sub cmdExit_Click()
Dim dbTemp As Database
Dim rsetEmail As Recordset
Dim strSQL As String
Dim strEmailTo As String
Dim strEmailSubject As String
Dim strEmailText As String
Select Case Me.Subject
Case "Environment", "Health & Safety"
' Send email Now. Retrieve email recipients from table
Set dbTemp = CurrentDb
With dbTemp
' Retrieve email recipients for this subject category and build email list
strSQL = "SELECT LoginName.Email FROM tblSubjectEmailRecipient1 " & _
"INNER JOIN LoginName ON tblSubjectEmailRecipient1.Fullname = LoginName.Fullname " & _
"WHERE tblSubjectEmailRecipient1.Subject ='" & Me.Subject & "' " & _
"AND tblSubjectEmailRecipient1.AreaCode='" & Me.Area & "' "

Set rsetEmail = .OpenRecordset(strSQL, dbOpenDynaset)
If rsetEmail.EOF Then
MsgBox "No Email Recipients set up for " & Me.Subject & ", " & Me.Area & " Site." & vbCrLf & vbCrLf & _
"Please advise your CIF Adminstrator."
Else
strEmailTo = ""
With rsetEmail
.MoveFirst
While Not .EOF
strEmailTo = strEmailTo & !email & ";"
.MoveNext
Wend
End With

strEmailSubject = "CIF - " & Me.Subject
strEmailText = "Number : = " & Number & vbCrLf
strEmailText = strEmailText + "Area : = " & Area & vbCrLf
strEmailText = strEmailText + "Date : " & Date & vbCrLf
strEmailText = strEmailText + "Initiated By : " & Initiated_By & vbCrLf
strEmailText = strEmailText + "Cause of Problem :" & vbCrLf & Cause_of_Problem & vbCrLf & vbCrLf
strEmailText = strEmailText + "Resin : " & Resin & vbCrLf
strEmailText = strEmailText + "Batch : " & Batch & vbCrLf
strEmailText = strEmailText + "Delivery :" & Delivery & vbCrLf
strEmailText = strEmailText + "Action Comments : " & Action_Comments & vbCrLf
strEmailText = strEmailText + "Suggestion : " & Suggestion
'Debug.Print "strEmailTo= "; strEmailTo
'Debug.Print "strEmailSubject= "; strEmailSubject
'Debug.Print "strEmailText= "; strEmailText
DoCmd.SendObject acSendNoObject, , , strEmailTo, , , strEmailSubject, strEmailText, False
End If
Set rsetEmail = Nothing
.Close
End With
Case Else
DoCmd.Close
End Select

End Sub

 

[dbDamo]

So you don't force a custom save?

All the Exit button does is send an email then close the form, you don't force a save in there anywhere, instead rely on Access to do it for you - not a good idea. I would force a custom save BEFORE the email is sent and include my code and any other validation as part of that custom save.

 

[kirsco]

Thank you so much for all your help dbDamo.

Once I put your code at the top, and removed the cancel as integer, it worked exactly as expected. Now I can add my other bits and pieces for make it a custom job.

 

[dbDamo]

Just make sure you add the following line of code after the validation and before the email is sent:-

DoCmd.RunCommand acCmdSaveRecord