Cancel Record Entry if fields are incomplete

[raziel3]

I have a continuous form, if the record has incomplete fields and I set the focus to a record before, how to cancel the current record entry and clear all the fields that were filled?

 

[GPGeorge]

One approach would be to use the Before Update event of the FORM.

In the Form's Before Update event, you can display a MsgBox asking the user if they want to cancel the update/entry of the current record. This will only fire if the record is dirty, i.e. has been started and not saved, or edited and not saved. There is another complication that I'll address below.

If the user wants to cancel the update, you can cancel it there in the Form's Before Update event.

Now, this will always fire in the Form's Before Update event, so if your goal is only to do so if the user wants to move to an existing record, that gets more tricky because I'mnot sure how you can determine where the user has tried to move focus at this point.

The other complication is what you mean by "incomplete fields". Are all fields required, some fields required? What constitutes an incomplete field in this scenario? Is it ever permissible to leave a field unchanged at this point? Does this requirement only apply sometimes, i.e. when the user wants to select an existing record before saving the current record?

Thank you for illuminating the process and rules.

 

[CJ_London]

Use the form before update to validate each of the controls and cancel and undo if any are invalid

 

[raziel3]

The other complication is what you mean by "incomplete fields". Are all fields required, some fields required? What constitutes an incomplete field in this scenario? Is it ever permissible to leave a field unchanged at this point? Does this requirement only apply sometimes, i.e. when the user wants to select an existing record before saving the current record?

There are 4 fields.

PDate - Required
Item - Required
Quantity - Required
Cost - Required
Vatable (check box) - Can be checked or not

So if I start a new record entry and PDate is filled but I scroll up and set focus to a prior record. The New record should be cancelled and undo the data entry for PDate or whatever fields were filled.

 

[raziel3]

Added db.
On the form frmPurchases if I try to close the form I get the error in the pic.
I want a way to undo the record entry if I close the from because the other fields are not filled suppressing the error.

 

[MajP]

Private Sub Form_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
'PDate -Required
'Item -Required
'QUANTITY -Required and not zero
'Cost -Required and not zero
Dim strMsg
Dim invalid As Boolean

strMsg = "The Following Field/s Need to be entered:" & vbCrLf

If IsNull(Me.UPC) Then
  strMsg = strMsg & "UPC" & vbCrLf
  invalid = True
End If

If Not IsDate(Me.PurDate) Then
  strMsg = strMsg & "Purchase Date" & vbCrLf
  invalid = True
End If

If Me.QUANTITY <= 0 Then
  strMsg = strMsg & "Quantity" & vbCrLf
  invalid = True
End If

If Me.Cost <= 0 Then
  strMsg = strMsg & "Cost"
  invalid = True
End If
  
If invalid Then
   strMsg = strMsg & vbCrLf & vbCrLf & "Please fix or hit escape to clear."
   MsgBox strMsg, vbInformation, "Required"
   Cancel = True
   'Me.Undo
End If

End Sub

 

[MajP]

I am going to assume a quantity of 0 and cost of 0 are invalid. If they are valid choices then do not default these to 0. The reason is there is a difference between a default value of 0 symbolizing no entry and a purposeful entry of 0. You would not be able to tell which is intended.

 

[raziel3]

I'm still getting the "You can't save this at this time" notification. How to suppress this?

If I press Esc to undo the record entry, it closes no problem, but as long as a field is populated I get the error before close. Can the form automatically clear the partial record entry then close without prompting "You can't save this at this time"?

 

[Pat Hartman]

So if I start a new record entry and PDate is filled but I scroll up and set focus to a prior record. The New record should be cancelled and undo the data entry for PDate or whatever fields were filled.

The BeforeUpdate event of the form is the LAST event to run before a record gets saved. The others have been telling you that this is the event you need to use in order to keep Access from saving bad data.

If I press Esc to undo the record entry, it closes no problem, but as long as a field is populated I get the error before close. Can the form automatically clear the partial record entry then close without prompting "You can't save this at this time"?

Did you implement the code suggested by @MajP ?

We cannot possibly guess at what code you are running so if you want someone to help, you will need to post YOUR VERSION of the code.

PS, I do my validation differently from the way Maj has in the posted code. Technically it is correct so if you are OK with it, then use it. I set focus to the error field and exit the sub at that point so the user only ever has one error at a time to deal with. If I were going to flag all errors at once, I would change the color of the text of the label or change the background color of the control so the errors stand out. It's a couple more lines of code for each control but is easier for the users to see.

 

[MajP]

You have code that moves to a new record. This code will error unless the required fields are selected. So see this version. You have to validate before that could too.

 

[raziel3]

The code works at it should. There is no problem there.
What I wanted to know is if this message box (see attachment) can be bypassed or suppressed?
After escaping the message box from @MajP code this one pops up when I try to close the form (clicking "x" on the form control box). I think it's an Access default for saving the record.

What I did so far was added Me.Undo

Private Sub Form_BeforeUpdate(Cancel As Integer)
     If Not IsValid Then
       Cancel = True
       Me.Undo
    End If
End Sub

On closing the form, this clears the incomplete record but then the "You can't save this at this time" notification pops up.

 

[tvanstiphout]

Private Sub Form_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
'PDate -Required
'Item -Required
'QUANTITY -Required and not zero
'Cost -Required and not zero
Dim strMsg
Dim invalid As Boolean

strMsg = "The Following Field/s Need to be entered:" & vbCrLf

If IsNull(Me.UPC) Then
  strMsg = strMsg & "UPC" & vbCrLf
  invalid = True
End If

If Not IsDate(Me.PurDate) Then
  strMsg = strMsg & "Purchase Date" & vbCrLf
  invalid = True
End If

If Me.QUANTITY <= 0 Then
  strMsg = strMsg & "Quantity" & vbCrLf
  invalid = True
End If

If Me.Cost <= 0 Then
  strMsg = strMsg & "Cost"
  invalid = True
End If
 
If invalid Then
   strMsg = strMsg & vbCrLf & vbCrLf & "Please fix or hit escape to clear."
   MsgBox strMsg, vbInformation, "Required"
   Cancel = True
   'Me.Undo
End If

End Sub

View attachment 115789

A more elegant (if I may say so) implementation of field level validation of required fields, including optionally required fields (if status=X then the field is required) can be found in the Northwind 2 Dev edition template. Per the documentation, it takes only 3 lines of code to validate a form.

 

[KitaYama]

(if status=X then the field is required).............it takes only 3 lines of code to validate a form.

Not OP, but I wish when experts here refer us to northwind2, they could be a little bit more specific. May I ask in which form should I see these 3 lines for validation and see how it's done?
I searched the whole project for "status", 188 lines were found. I's hard to go through all and check what is what.
I searched for "If status=", the result was 0.

In which form's validation I can see how it's done.
Thanks.

 

[tvanstiphout]

Not OP, but I wish when experts here refer us to northwind2, they could be a little bit more specific. May I ask in which form should I see these 3 lines for validation and see how it's done?
I searched the whole project for "status", 188 lines were found. I's hard to go through all and check what is what.
I searched for "If status=", the result was 0.

In which form's validation I can see how it's done.
Thanks.

Did you follow the link? If so, you would have seen it's about the Orders form, specifically frmOrderDetails.
There is a vldeo where I discuss validation as well.

-Tom.

 

[KitaYama]

Did you follow the link?

Yes, and No. I clicked it, but didn't read the article. I was in my 15 min break time and your link title (Documentation) gave me the idea of a long article.
I jumped to Northwind. I had a feeling searching "Status", may be faster.

Anyway, thanks.

 

[Pat Hartman]

Validation goes far beyond simple presence. While a simple loop can ensure specific fields are not null, that is not everything that needs to be validated.

Sometimes you have specific business rules about relationships etc. But even just plain date fields should always have what I call sanity checks at least. How often have you seen a date like 2/15/224? It is perfectly valid but is obviously a typo. You can easily identify these by simple sanity checks like the date has to be within 7 days of today + or -. Birth dates cannot be in the future. Delivery dates cannot be in the future. Employees must be 16 + years old and probably less than 80 although you can allow an override because we're all living longer and you might have someone on the payroll who is older. Just make the user confirm that the DOB is valid.

Here's two variations on the Required field validation. You call the routine by passing in a form object. The first variation uses the tag property of the control AND it exits as soon as the first error is found. the second variation validates ALL fields and highlights the errors. If you like the highlight, but you want to validate only specific controls, merge the two routines.

To call the code I use:
Call EnsureNotEmpty(Me)

"Me" is a reference to the current form so the code works on mainforms or subforms as long as you call the function from within the class module of the form whose controls you want to check. As long as you use the form or subform's BeforeUpdate event, you will get the desired results.

Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If Instr(ctl.Tag, "Required") > 0 Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
            Case Else
        End Select
    Next ctl
    
    EnsureNotEmpty = True
End Function

Public Function EnsureNotEmptyAll(frm As Form) As Boolean   '''' not tested
Dim ctl As Control
Dim strMsg As String

    For Each ctl In frm.Controls
       Select Case ctl.ConitrolType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl & "" = "" Then
                    ctl.BorderColor = RGB(186, 20, 25)
                    strMsg = strMsg & ctl.Name & " is required." & vbCrLf
                Else
                    ctl.BorderColor = RGB(192, 192, 192)
                End If
            Case Else
        End Select
    Next ctl
    
    If strMsg = "" Then
        EnsureNotEmptyAll = True
    Else
        EnsureNotEmptyAll = False
        MsgBox strMsg, vbOKOnly
    End If
End Function