How to prevent closing a form after the form’s before update had kicked in? (1 Viewer)

[spenz]

Scenario: I have an exit button named cmdClose in the upper right corner of all my forms. I don’t have a separate save and undo button for all of my forms; I just knew that if I can exit from the form without violating any of my validation rule then that means it’s all good and the form is automatically saved after closing it.

I put all of my validation rules on the before update event of all of my forms and it all works terrific at the moment with only one flaw (The form closes after the user had clicked the close button even though the before update event had already kicked in.). See my example code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
        
        If IsNull(Me.ClientID) Then
            MsgBox "Name is empty."
            Me.Undo
            Cancel = True
            Me.ClientID.SetFocus
            Exit Sub
            
        ElseIf IsNull(Me.TransactionNameID) Then
            MsgBox "No transaction name."
            Me.Undo
            Cancel = True
            Me.TransactionNameID.SetFocus
            Exit Sub
        
        ElseIf IsNull(Me.DateTransacted) Then
            MsgBox "No transaction date."
            Me.Undo
            Cancel = True
            Me.DateTransacted.SetFocus
            Exit Sub
                                            
        End If

End Sub

What I want is that after the before update event had kicked in, I want to prevent the form from closing even though the client had already clicked the cmdClose button. Instead of closing, I want to redirect the focus to the particular field so that the user will know which is the offended field.

Any suggestions and opinions are welcome. Hopefully, it's a simple but very effective solution. Thank you very much.

 

[wazz]

this is a tricky area and there are many ways to go about it. for simplicity, i would suggest putting all the validation code you have into the close button's event. if all tests pass, then close the form. (you won't need Cancel = True). also, i would probably not use Me.Undo as that will undo *everything* that was added/edited on the form; be more specific with what you undo (if anything).

 

[spenz]

Thank you very much wazz, I've tried your suggestion and it worked very nice as of the moment.

I need that me.undo because the form I am referring to is an editing form. What i don't need right now is probably cancel=true as what you've suggested as well.

Well, if i ask for your own preference on solving this kind of situation is there a more effective way in handling this? Because in some of my forms i have subforms in them and the validation won't work because it is now placed on the cmdclose button instead in before update event of main form?

 

[wazz]

you can duplicate the validation code on your subform's Enter event. if any tests fail, don't allow the user to enter the subform (because leaving the main form will update the main form); again set focus to a control on the main form.

 

[spenz]

Gracias again wazz! Will try this as soon as i get back from work. By the way, I really like the way you give advices it's very straightforward and simple aimed to solve the particular inquired problem. No more added jargons that "may probably" confuse a novices like me. Again I appreciate it. ^_^

 

[spenz]

The way i see it now; to be sure that your validation rules won't be bypassed in a form even if you have sub form on it is to put it on before update event. But because it does not prevent the form from closing after the user had clicked the close button on the main form you also have to put the validation rule to the cmdClose button.

Then there are these validation rules from the sub form itself. if I would like the user to remain on the sub form until he had addressed the violation committed i had to put the validation rule on before update event of the sub form. But then again there are times when it is not appropriate to put a particular rule on the before update event of the sub form itself.

If i have to repaste and repaste a lot of my validation rules on different events wouldn't it bloat my database fast or somehow affect it's performance later on. Is this really the way it's usually done (scatter the validation rules to different events of a form)? Maybe there is a way to put all the validation rules in one event of a particular form to make it neat somehow and surely won't bloat the database as a result. Thanks again

 

[wazz]

thanks for the previous comments.
now, with the current questions we might start getting into extra "jargon".

The way i see it now; to be sure that your validation rules won't be bypassed in a form even if you have sub form on it is to put it on before update event. But because it does not prevent the form from closing after the user had clicked the close button on the main form you also have to put the validation rule to the cmdClose button.

actually, what i meant was, you don't need it in both places - just the command button (and the Enter event of the subform).

Then there are these validation rules from the sub form itself. if I would like the user to remain on the sub form until he had addressed the violation committed i had to put the validation rule on before update event of the sub form. But then again there are times when it is not appropriate to put a particular rule on the before update event of the sub form itself.

if you have validation rules on the subform you can use the Exit event to stop people from leaving the subform (in addition to the Enter event to stop people from leaving the main form).

If i have to repaste and repaste a lot of my validation rules on different events wouldn't it bloat my database fast or somehow affect it's performance later on.

adding a bit of code here and there won't bloat the db much at all; storing images will create bloat, as will going into and out of design-mode of forms (compacting will fix this).

Is this really the way it's usually done (scatter the validation rules to different events of a form)? Maybe there is a way to put all the validation rules in one event of a particular form to make it neat somehow and surely won't bloat the database as a result. Thanks again

there are many ways to approach this. i find enabling and disabling command buttons very effective. other approaches include adding validation to the fields in the table; adding validation to individual controls on forms and more. validation in the table can result in adding a bunch of extra code to the forms anyway - the amount of code will be the same, or more. validation in the table could mean adding code to the form's Before Update event, the form's Error event and other events. it could involve adding module level variables and/or global variables to track errors, etc. - all good, useful and valid approaches.

very simply put, to avoid saving a record when it should not be saved you have to stop the user from leaving the record or the form.

There are a lot of good threads on the forum that discuss these approaches. if you're interested, google this site for the terms i just mentioned.

 

[spenz]

Great advice again mate. Thank you very much! Actually, i was really worried with my database bloating due to excessive number of codes because i heard that access has a minimum capacity before it gets to act weird on you. About 2Gb right?..but now upon hearing from you, I'm glad and confident that adding as many codes as needed will not be a concern with regards to performance later on.

I've done the table validation earlier on my database and you are right it's more work and gets a little tricky with that access standard error popping up from time to time. So at the moment
I would favor and more comfortable with adding validation rules on forms alone and so far with your help it's working so good.^_^

I also want to try that enabling and disabling of controls technique because you've mentioned that it's very effective and i figure it must be as it can save you a little coding effort because after all a disabled buttons will not be touched by the user anymore. As a result it, the form will be a lot efficient and user friendly.

With that regard, i have attached my sample code for enabling and disabling the controls of my form when the on current event had determined that the transaction is already closed or not. But my problem with this is that because it disables the whole controls after the on current event has detected that the transaction is already closed. I cannot even access my cmdClose button for exiting the form and the chkClosed check box used to close/open again the transaction because it's all already been disabled. So i had to re-enable it in the code itself. In effect, my loop code has become redundant or sort of being overwritten just to specify that the cmdClose and chkClosed must be exempted from being disabled. =/

Please check out my sample code below and maybe you can suggest a better alternative/solution where i can only disable the specific button that i want to using a similar looping technique.

Private Sub Form_Current()
On Error GoTo ErrHandler

    Dim ctl As Control
    
        If Me.Closed = -1 Then
            For Each ctl In Me.Controls
                With ctl
                    Select Case .ControlType
                        Case acTextBox
                            .Enabled = False
                            
                        Case acComboBox
                            .Enabled = False
                        
                        Case acCommandButton
                            .Enabled = False
                        
                        Case acCheckBox
                            .Enabled = False
                    End Select
                End With
            Next
                  [COLOR="Red"]Me.Closed.Enabled = True
                  Me.cmdClosed.Enabled = True[/COLOR]
               
        ElseIf Me.Closed = 0 Then
                    For Each ctl In Me.Controls
                With ctl
                    Select Case .ControlType
                        Case acTextBox
                            .Enabled = True
                            
                        Case acComboBox
                            .Enabled = True
                        
                        Case acCommandButton
                            .Enabled = True
                        
                        Case acCheckBox
                            .Enabled = True
                    End Select
                End With
            Next
        End If
        
Exit_Now:
Exit Sub

ErrHandler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Now
Resume

End Sub

 

[wazz]

i didn't realize you were thinking about disabling every control. did you know you can use a property of the form: Allow Edits (No) to stop editing?

- for your code above, i think you could alter it slightly

Case acCommandButton
If ctl.Name <> "cmdClose" Then
.Enabled = False

- if you want to try table validation, experiment with the form's Error event. let the error happen and make a note of the error number then handle the error yourself in the form's Error event (If Err.Number = n Then...)

 

[spenz]

Sweet and simple thanks man you're the best! ^_^

I was not really into disabling the button earlier mainly because i still don't have the idea yet. But since you've mentioned that in one of your earlier posts. I then experimented on it and found it very effective just like what you've said as well.

Now, I will also try that form's allow edits property but i think it will be in conflict with my chkClosed check box. What if i want to re-open the transaction again but since the me.allowedits is set to false i can't open it anymore right?.. So basically, I just have to choose between these 2 schemes (disabling or allow edits) and I am thinking disabling control is more favorable since the user will actually see the button disabled so they won't bother to attempt hitting it anymore.

With regards to trying table validation, I think I won't bother trying to experiment on that again as my form level validation is working nicely right now. If i remember it correctly I've also consulted bob larson,banana and i think missinglinq with that regard earlier when i've posted a thread inquiring on how to go about validation rules and they are unanimously in favor with putting validation rules on form level as sufficient enough for their users.

 

[wazz]

So basically, I just have to choose between these 2 schemes (disabling or allow edits)

that's what it usually comes down to. depending on the type of form i usually have to go in one direction or the other. sometimes it's good to make separate forms for adding, editing, deleting, etc. and other times one form to do everything. the type of form can change the design approach.

...and I am thinking disabling control is more favorable since the user will actually see the button disabled so they won't bother to attempt hitting it anymore.

once i started getting into disabling buttons i realized it is very clear to the user what is possible. thanks for getting back about all of this.

 

[spenz]

Alright, thanks again mate for such a wonderful discussion and great ideas. It really is a life saver. Tomorrow I'll try experimenting on appending records to other database that will be stored as backup for every 3 years of transactions. I'm not so sure what to do yet, but I am hoping i could do that smoothly or else I'll have to bother the veterans again. ^_^