BeforeUpdate problems (1 Viewer)

[ShaneMan]

I not sure what I'm doing wrong here. I have searched several forums and "think" I'm doing this the same way.

I'm trying to validate some fields before allowing the record to be saved, which if the user doesn't fill in the appropiate fields then the msgbox does fire, and the record is not saved but my problem is that I don't want the form to close. I would like to force the user back to the form and fill in the required fields. Any idea's as to why the forms closes?

Here's my code.

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
With ctl
If ctl.Tag = "Required" Then
If ctl.Value = "" Or IsNull(ctl.Value) Then
Me.PartNumber.SetFocus
MsgBox "Missing data"
Cancel = True
ctl.Undo
End If
End If
End With
End If

 

[RuralGuy]

To better explain your situation you should have probably posted *all* of the code in the event from "Private Sub... to End Sub". Is this the BeforeUpdate code? Does it also exist in the Unload event?

 

[ghudson]

Your posted code ONLY belongs in the "forms" BeforeUpdate event so that it can cancel the writing of the record if it fails your validation,

 

[ShaneMan]

Thanks for both for replying. I have the code in BeforeUpdate only and this is all the code. The code does loop throught the text boxes and does see the "Required" tagged text boxes and will fire the msgbox if the text box is "", however after the user clicks "OK" on the msgbox I would like the form to cancel the form from closing and go to a certain text box (Me.PartNumber.SetFocus). This part does not happen. The form closes, and does not save the changes.

Thanks,
Shane

 

[RuralGuy]

ShaneMan,

Here's some code I clipped out of one of my projects without editing it.

Private Sub Form_Unload(Cancel As Integer)
'-- Make sure at least the minimum fields are completed
On Error GoTo Err_Form_Unload

Dim MyMessage As String, ctlWithFocus As Control, Answer As Integer
MyMessage = ""

If Not Deleted Then
    If Nz(Me.optStorage, 0) = 0 Then
        MyMessage = "The Storage Requirements must be set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.optStorage
    End If
    If Me.txtUnitCase = 0 Then
        MyMessage = "The Units per case must be set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.txtUnitCase
    End If
    If (Me.cboUnits & "") = "" Then
        MyMessage = "The Measurement Unit must be set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.cboUnits
    End If
    If Me.cboCategory.Column(0) = 27 Then
        MyMessage = "The Expensing Category has not been set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.cboCategory
    End If
    If (Me.txtSupplierDesc & "") = "" Then
        MyMessage = "The Description has not been completed!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.txtSupplierDesc
    End If
    If (Me.txtProdName & "") = "" Then
        MyMessage = "The Product Name has not been completed!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.txtProdName
    End If
    If Len(MyMessage) <> 0 Then
        MyMessage = MyMessage & vbCrLf & vbCrLf & _
                    "Do you wish to DELETE this Product?"
        Answer = MsgBox(MyMessage, vbYesNo)
        If Answer = vbNo Then
            ctlWithFocus.SetFocus
            If (ctlWithFocus = Me.cboCategory) Or (ctlWithFocus = Me.cboUnits) Then
                ctlWithFocus.Dropdown
            End If
            Cancel = True
        Else
            Cancel = Not DeleteProduct()
        End If
    End If
Else
    Cancel = False
End If

If Not Cancel Then
    If Not IsNull(Me.OpenArgs) Then
        Forms(CallingForm).UpdateProductList
        Forms(CallingForm).Visible = True
    End If
End If

Exit_Form_Unload:
    On Error Resume Next
    Set ctlWithFocus = Nothing
    Exit Sub

Err_Form_Unload:
    Call LogError(Err.Number, Err.Description, "Form_Unload() in " & Me.Name)
    Resume Exit_Form_Unload

End Sub

I have nothing in the BeforeUpdate of the form or any of the controls. I find it works rather well. Maybe you can modify this code to suit your needs.

 

[ShaneMan]

Thanks Rural Guy,

My experience could be the same as yours. I have read post for several years and in that time frame I have seen where the advise was given to validate required fields in the BeforeUpdate event. I have never been able to get this to happen right. I can get it to validate whether a required field has been left blank but I haven't been able to stop the close part of the form and give the user a chance to go back and fill in the blank field, so what I have done on all of my forms, so far, is to create my own Close button, eliminate the "X" button and then validate all the required fields there cause I can control it.

Honestly, what got me to "try again" is I read an answered post here, by someone who's name I've seen forever, stating to not validate required fields anywhere but BeforeUpdate, so I thought I would try again and I'm still not having any luck. I did a search on several forums and can see code and advise given on how to do this and apparently it works for everyone else, which causes me to think, "what am I doing different (wrong)."

What is cancel = true really doing? Does it cancel saving the record or cancel the form closing or both?

Thanks again,
Shane

 

[RuralGuy]

It cancels the event to which it is attached. In the case of the code I posted it cancels the Unload event of the form but the record is already saved. In my case you will notice I've utilized a DeleteProduct() function. It certainly keeps the user on this form until all required controls are completed or they delete the record completely. If you use the BeforeUpdate event of the form then a Cancel will completely cancel any update of the record. That usually means unless you want to trap the user in this form, you need to provide some UnDo code so they can go to lunch and pick it up later.

 

[RuralGuy]

By the way, I don't use "Close" buttons on my forms. The user just uses the X to close the form. If I want to validate data in a control I use the BeforeUpdate event of the control, not the form. It all depends on whether you can afford to have bogus data in your record or not. In the case of the posted code, I could.

 

[ShaneMan]

RuralGuy,

Thanks for all of your replies and help. BTW, I didn't say in the post before, thanks for the code at the Unload event, too. My reason for using a close button instead of the X is just for looks reasons. Over time I started using it to validate required fields. Guess that's probably not a good idea. You have given me a different way of looking at things and a new direction to try.

Thanks again,
Shane

 

[RuralGuy]

You're welcome and lots of success with your project.

 

[Pat Hartman]

Rural Guy, your edit code doesn't really work. At the time the Unload event fires, the record has ALREADY been saved so cancelling the unload event just makes it look like you have prevented bad data from being added to your database. Just scroll to a new record. That will save the previous, possibly bad data without firing the Unload event. Or, in the case of a mainform/subform, moving control from one to the other will save the record in the form being left. I say yet again, the form's BeforeUpdate event is the ONLY place this type of edit belongs. It will work in the BeforeUpdate event no matter what causes the record to be saved. The Unload event and ALL other events allow records to be saved and attempt to trap exit but by that time it is too late. Just turn the PC off to see that I am correct.

ShaneMan, you can make your BeforeUpdate and Unload events work in conjunction by adding a global variable that you set in two places and check in one.

Current event:

NoErrors = True

BeforeUpdate event - any place an error is raised:

NoErrors = False

Unload event:

If NoErrors = False Then
Cancel = True ' prevent form unload due to unresolved error
End If

 

[RuralGuy]

Pat,

With all due respect, I believe I stated as much in prior posts in this thread. In the form from which I copied the code there is no way to "scroll" anywhere. The user either fills out the form completely or deletes the entire transaction. You have made some assumptions here that were not correct.
Your point about the BeforeUpdate event of the form has not gone un-noticed by me or the OP. There are simply other ways to accomplish the OP's task that I was presenting *if* you can allow bogus data in the record.

 

[Pat Hartman]

Sorry, Rural Guy but the implication of your post was that the Unload event was an adequate alternative to the BeforeUpdate event and it most certainly is not. Frustrated users push the power off button. In that case, your data will already have been saved. Data integrity is the heart of any system. If your application were to undergo rigid testing, it would fail. I offered a very simple alternative that protects the validity of the data and with three extra lines of code, prevents the form from closing. But, in my case, if the frustrated user were to push the power off button, the data would not have been saved so data integrity is intact.

ShaneMan, if you want to give your users a clean way to exit from a dirty form, offer them a cancel the update option where you use the Me.Undo to back out any changes they made rather than having them fix the errors before continuing.

Form events are not interchangeable. There is a "correct" form event for each task. That is the way Access was designed. There are some things that will work (or seem to work) in multiple events. This happens to be one of them. If you don't completely test your application (and what programmer actually does?), you can leave holes. The form's BeforeUpdate event won't leave a data hole the way other events can when used to edit data. So if your employer/client cares about his data, I suggest you use the form's BeforeUpdate event for all multi-field or null edits. The BeforeUpdate events of the individual fields can be used to edit the field in isolation but they cannot reliably edit the field for null and unless you are absolutely controlling cursor movement, they cannot be easily used to edit data that relies on other fields.

 

[Pat Hartman]

PS, if you learn how to do it right (use the BeforeUpdate event - always), you don't have to learn how to do it multiple ways for the times when the form allows scrolling and the times when it doesn't. The right way ALWAYS works

 

[ShaneMan]

Mr. Hartman,

Thanks for your replies and input. I have placed your suggestions in my form per your instructions (hopefully) and I believe I am much closer. I am having a few problems though.

1. you said to set a global variable for NoErrors. I'm not real sure how to do that but did set it at form level and as least the form recognizes it. I would like to have it as a global though cause if I can get this working I intend to go back to all my forms I've already created and change to this format.

2. The following is my code now:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

For Each ctl In Me.Controls
With ctl
If ctl.ControlType = acTextBox Then
If ctl.Tag = "Required" Then
If ctl.Value = "" Or IsNull(ctl.Value) Then
MsgBox "Missing data"
ctl.SetFocus
Cancel = True
NoErrors = False
MsgBox NoErrors
Exit Sub
End If
End If
End If
End With

Next

End Sub

My code in OnCurrent and Unload are exactly as you wrote. I'm getting an Access Error message stating You can't save this record at this time and then another Access error stating The Close Action was cancelled. Once it does this then I'm trapped in the form and can't get out. Any suggestions?

3. Shouldn't I be turning NoErrors to True, somewhere, once all required fields are filled in?

Thanks for your help and trouble,
Shane

 

[john471]

3. Shouldn't I be turning NoErrors to True, somewhere, once all required fields are filled in?

Current event:

NoErrors = True

I believe Pat is asserting that you you should set the NoErrors to True in the Form's OnCurrent Event.

Pat - ? if validation fails the first time - the global is set to false and the focus is set back to the (first) control in error, does the OnCurrent event fire again when the Update is Cancelled (to re-set the variable) ? Or should one also (I still understand the need to set it in the OnCurrent event) assume a good result at the outset of BeforeUpdate, and set the variable there, to be over-written later in the sub if validation fails ?

Shane - the point of using a "with ctl" structure is that inside the with structure you don't repeat the "ctl" - it is implied by the leading "." By explicitly typing out the full object you may well be negating the efficiency that a "with" structure provides - I'm sure Pat can (and almost equally sure she will ) answer that point definitively.

Private Sub Form_BeforeUpdate(Cancel As Integer)

   Dim ctl As Control

   NoErrors = True 'Assume Good Result

   For Each ctl In Me.Controls
      With ctl
         If .ControlType = acTextBox Then
            If .Tag = "Required" Then
               If .Value = "" Or IsNull(.Value) Then
                  MsgBox "Missing data"
                  .SetFocus
                  Cancel = True
                  NoErrors = False
                  MsgBox NoErrors
                  Exit Sub
               End If
            End If
         End If
      End With
   Next
End Sub

 

[ShaneMan]

Thanks for your reply John. I have made the changes as you suggested as far as ctl goes. The reason for asking if NoErrors needs to be reset is I was wondering the samething. I do have NoErrors = True at OnCurrent but was wondering if NoErrors = False (when it catches a offending required field ), needs to get changed to NoErrors = True when there are no more offences. I didn't know if that happened at OnCurrent or not.

I still have the problems of record can't be saved and close action being cancelled. Any thoughts?

Thanks again,
Shane

 

[ghudson]

Check this thread out for a working example of how to keep the user from closing your form and/or Access if they user has not jumped through all your hoops and satisfied your validation routine. Just add your validation routine to the samples BeforeUpdate event in your form with my method and you should be okay.

Enable/Disable The Control Box X Button

HTH

 

[ShaneMan]

Thank you GHudson,

I downloaded your disable control box db. All works are you designed. I'm probably a little more interested in the cancel form unload event due to I have a form, in the background that is maximized and modal, with no borders or toolbars, so the user can't get to it to close the db. The current form that I'm trying to get this BeforeUpdate to work for, I have my own Close button so their are no control boxes on it either. When I use yours or Pat's code, I keep getting a "Close Action was cancelled" message and then after that, there is no getting out of the form. Can't even change to design view. Any idea's how to trap for this? The following is the forms unload event code:

If NoErrors = False Then
Cancel = True ' prevent form unload due to unresolved error
Beep
MsgBox "You must fill in all fields before you can close this form."
End If


Thanks,
Shane

 

[ghudson]

Can you post the bare basics of your db so that I can see what is going on? Include the steps you are using and where the problem occurs and what you want to happen instead.