Cancel save after BeforeUpdate code runs

[Zydeceltico]

Hi all -

I have a form that has three controls (2 cbos and 1 textbox) that are bound to 3 required fields. There are several other controls on this form but they are not required. I also have a save button for the form (and another underlying form).

The problem is that it is easy to click the "Save" button without filling in all three fields which of course throws an error and junks out the form maing it a real pain.

The button's save code is this:

    If CurrentProject.AllForms("frmInspectionEvent").IsLoaded Then
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close
        Forms!frmInspectionEvent.Requery
    Else
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close
    End If

This code is on the form's BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If (IsNull(Me.cboLine1Workstation)) And (IsNull(Me.cboLineStopReason.Value)) Then
    ' No action required
Else
    If (IsNull(Me.txtLineStopBegin)) Or (Me.txtLineStopBegin.Value = "") Then
        MsgBox "You must provide data for field 'Line Stop Start', " & _
            "if a value is entered in Line 1 Workstation", _
            vbOKOnly, "Required Field"
        Me.txtLineStopBegin.SetFocus
        Cancel = True
        Exit Sub
    End If
End If

End Sub

I already know the above code is not what I am after exactly......

What I really want is code to put on the BeforeUpdate of the form so that if the user accidentally clicks the save button before the required fields all have values the BeforeUpdate code tests to see if any of the 3 required controls are dirty; if any but not all are dirty a msgbox opens to to tell the user "hey - you still need to fill in x or y or x and y" - and most importantly does not crash the form into the save code from the button click which throws a Run-time error 2501 after it pops up my custom message. The run-time error occurs on this line of the Save code:

    If CurrentProject.AllForms("frmInspectionEvent").IsLoaded Then
        [COLOR="Red"][B]DoCmd.RunCommand acCmdSaveRecord[/B][/COLOR]
        DoCmd.Close

I'm pretty sure this is a common type of test to do but I'm having trouble finding an example that I can abscond with.

Any direction or insight would be appreciated and put to very good to use!

Thanks,

Tim

 

[theDBguy]

Hi Tim. Sometimes, some errors are expected and all we tend to do is handle them.For example, using the DoCmd object to save a record that gets cancelled at the BeforeUpdate event will produce an error. When this happens, you can either handle the error or avoid it. To avoid this particular error, you could try replacing the DoCmd line with If Me.Dirty Then Me.Dirty = False


There may be some demos on how to validate forms for required data. You might take a look at how someone tackled the same issue. Cheers!

 

[Zydeceltico]

There may be some demos on how to validate forms for required data. You might take a look at how someone tackled the same issue. Cheers!

Thanks! I just needed better google terms than I was using!

"how to validate forms for required data"

 

[theDBguy]

Thanks! I just needed better google terms than I was using!

"how to validate forms for required data"

Hey, please let us know what you find. I found a "class" for doing this, but I haven't tried it, so I'm not sure if it will work for you.

 

[Zydeceltico]

Hey, please let us know what you find. I found a "class" for doing this, but I haven't tried it, so I'm not sure if it will work for you.

What I found is that there appears to be less about validating within the context of the form itself but rather - knowing that some fields are required in their bound table - and then it becomes a process of checking each control on the form for a value and asking the user to make sure they place a value in that control.

I first found this which is the microsoft docs which gives an example using a single control which then led me to this (an old post on this forum) - Post #2 btw - that has a good example of using this approach for multiple controls. The following discussion is also quite good.

 

[theDBguy]

What I found is that there appears to be less about validating within the context of the form itself but rather - knowing that some fields are required in their bound table - and then it becomes a process of checking each control on the form for a value and asking the user to make sure they place a value in that control.

I first found this which is the microsoft docs which gives an example using a single control which then led me to this (an old post on this forum) - Post #2 btw - that has a good example of using this approach for multiple controls. The following discussion is also quite good.

Hi. The usual approach I use takes advantage of the controls' Tag property. Basically, the code loops through all the controls and checks this property, which you would indicate which controls needs validation at design time. I wrote an example routine in a book a few years back.

 

[Zydeceltico]

Hi. The usual approach I use takes advantage of the controls' Tag property. Basically, the code loops through all the controls and checks this property, which you would indicate which controls needs validation. I wrote an example routine in a book a few years back.

Whats the name of the book?

 

[theDBguy]

Whats the name of the book?

Hi. It was called "Professional Access 2013 Programming." The routine I wrote checks if the control is "read only" and sets its Enabled property based on the logged in user. It's on page 654. Hope it helps...


PS. Just to clarify, it wasn't "my" book. I just contributed a little bit into it. Actually, there were a few of us who contributed to it. Cheers!

 

[Zydeceltico]

And for other folks who might be reading this post, here is another good bit of code from this link.

If Len(Me.SomeControl & vbNullString) = 0 Then
  MsgBox "You need to fill out SomeControl"
  Cancel = True
  Me.SomeControl.SetFocus
End If

I haven't tried it yet but I would imagine that I can just repeat the If statement for the three controls I need to validate.

There is probably a more elegant/succinct way to do it for multiple controls. If I can figure it out, I'll post back.

 

[Zydeceltico]

Hi. It was called "Professional Access 2013 Programming." The routine I wrote checks if the control is "read only" and sets its Enabled property based on the logged in user. It's on page 654. Hope it helps...


PS. Just to clarify, it wasn't "my" book. I just contributed a little bit into it. Actually, there were a few of us who contributed to it. Cheers!

Looking forward to checking it out! Thanks!

 

[theDBguy]

Looking forward to checking it out! Thanks!

Hi. Thanks for the interest. Just to be clear, I wasn't suggesting you buy the book (unless you really want to) just to see the example code I was referring to. I'm pretty sure there are plenty of other examples available around. I just didn't have one handy. Cheers!

 

[Zydeceltico]

Hi. Thanks for the interest. Just to be clear, I wasn't suggesting you buy the book (unless you really want to) just to see the example code I was referring to. I'm pretty sure there are plenty of other examples available around. I just didn't have one handy. Cheers!

I didn't take it that way. Still looking forward to checking the book out. And - - - you have given me a ton of good direction so I would imagine there is likely more helpful stuff in the book!

 

[theDBguy]

I didn't take it that way. Still looking forward to checking the book out. And - - - you have given me a ton of good direction so I would imagine there is likely more helpful stuff in the book!

Okay, just wanted it to be clear. It's a good book; but unfortunately, the bulk of the information in it was focused on Web Apps, which is no longer available - unless you're on a corporate system.

 

[Zydeceltico]

Okay, just wanted it to be clear. It's a good book; but unfortunately, the bulk of the information in it was focused on Web Apps, which is no longer available - unless you're on a corporate system.

Is this kind of the same thing you mentioned:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String

'Loop through every control on the form
For Each ctr In Me.Controls
   'Look for a Particular Tag
   If ctr.Tag = "BlkChk" Then
      'Create a List of empty questions
      If IsNull(ctr) Then
         strMsg = strMsg & "_ " & ctr.Name & vbCrLf
      End If
   End If
Next ctr

'Did We Find Any Unanswered Questions?
If strMsg <> "" Then
   If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
   strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
   vbOKOnly) Then
   Me.Work_Order__.SetFocus
      'Stop record being committed
      Cancel = True
   End If
End If
End Sub

If it is, is the idea to make the tag value of the controls I want to check equal "BlkChk?"

 

[theDBguy]

Is this kind of the same thing you mentioned:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String

'Loop through every control on the form
For Each ctr In Me.Controls
   'Look for a Particular Tag
   If ctr.Tag = "BlkChk" Then
      'Create a List of empty questions
      If IsNull(ctr) Then
         strMsg = strMsg & "_ " & ctr.Name & vbCrLf
      End If
   End If
Next ctr

'Did We Find Any Unanswered Questions?
If strMsg <> "" Then
   If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
   strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
   vbOKOnly) Then
   Me.Work_Order__.SetFocus
      'Stop record being committed
      Cancel = True
   End If
End If
End Sub

If it is, is the idea to make the tag value of the controls I want to check equal "BlkChk?"

Hi Tim. Yes, that's similar to what I was saying. I use "required" in the Tag property for *required* fields.

 

[The_Doc_Man]

I had a similar situation but I had secured the form so that you could not navigate while it was dirty. (You turn off the form's navigation controls.) To accomplish this, I had a little bit of code (that I can't easily reproduce here because of non-disclosure and ownership issues). I had a subroutine that controlled the visibility of the form's SAVE (command) button, the navigation controls, and a couple of other things.

In the form's FORM_CURRENT routine, I would reset some flags in the top of the class module. On the form I had Lost_Focus routines that would do the validation of that one field, and, if it was acceptable, would set one of the flags. (If not, it would change the color of the border of that field's control to bright red.) If the code set the flag, then it tested all of the flags. If as a result, the form was ready to be saved, I enabled the SAVE button. Sounds tedious, but it really wasn't.

If SrvName_OK AND AdmName_OK AND UsrName_OK Then
    btnSAVE.Enabled=True
Else
    btnSAVE.Enabled=False
End If

In essence, I let each control validate itself and then check the other flags. When all of the flags were OK, I enabled the button that would SAVE the record. I had some other safeguards in place to block accidental saving through other mechanisms, so the only way to get past that form was to UNDO (a different command button) or to fill in the proper data to enable the SAVE button so you could do a SAVE.

 

[Zydeceltico]

If SrvName_OK AND AdmName_OK AND UsrName_OK Then
    btnSAVE.Enabled=True
Else
    btnSAVE.Enabled=False
End If

In essence, I let each control validate itself and then check the other flags. When all of the flags were OK, I enabled the button that would SAVE the record. I had some other safeguards in place to block accidental saving through other mechanisms, so the only way to get past that form was to UNDO (a different command button) or to fill in the proper data to enable the SAVE button so you could do a SAVE.

That's something that I had not thought of - - disable the save button until I want it enabled. I'll be doing that! Thanks

 

[Zydeceltico]

In the form's FORM_CURRENT routine, I would reset some flags in the top of the class module. On the form I had Lost_Focus routines that would do the validation of that one field, and, if it was acceptable, would set one of the flags. (If not, it would change the color of the border of that field's control to bright red.) If the code set the flag, then it tested all of the flags. If as a result, the form was ready to be saved, I enabled the SAVE button. Sounds tedious, but it really wasn't.

If SrvName_OK AND AdmName_OK AND UsrName_OK Then
    btnSAVE.Enabled=True
Else
    btnSAVE.Enabled=False
End If

In essence, I let each control validate itself and then check the other flags. When all of the flags were OK, I enabled the button that would SAVE the record. I had some other safeguards in place to block accidental saving through other mechanisms, so the only way to get past that form was to UNDO (a different command button) or to fill in the proper data to enable the SAVE button so you could do a SAVE.

I like this a lot. My Question: How did you tell the code WHEN? In other words, what basic form did the code take that would check the various flags and "know" WHEN "all of the flags were OK?"

I see you give me a clue in your first line of code:

If SrvName_OK AND AdmName_OK AND UsrName_OK Then

....but what are those pointing to?

Thanks!
Tim

 

[Zydeceltico]

I like this a lot. My Question: How did you tell the code WHEN? In other words, what basic form did the code take that would check the various flags and "know" WHEN "all of the flags were OK?"

I see you give me a clue in your first line of code:

If SrvName_OK AND AdmName_OK AND UsrName_OK Then

....but what are those pointing to?

Thanks!
Tim

Is there some type of boiler plate for checking if they are each Dirty?

 

[Zydeceltico]

Is there some type of boiler plate for checking if they are each Dirty?

OK - thinking through this.

I suppose I could go to each control's On Dirty event and set the value of variable there - or more correctly - reset it from whatever I initially set it when I declare it in the module.

So that get's all 3 of the controls set to a green-light value if Dirty - but I'm not yet seeing how to tell the Save button that - "hey - the gang's all here and ready to go. You can enable your self now............."