Cancel in the BeforeUpdate event

S

Stoss

Registered User.
Local time
Today, 12:31
Joined
Nov 5, 2010
Messages
107
I have a form that users enter in data on. I am running validation on those controls using an "event procedure". I placed the code in the BeforeUpdate of the form. I have a customized menu that if the user clicks "Home", for example, it takes them to the main form (not the form that the data is getting validated on).

Here is the problem. If I use the Cancel = true in the beforeupate it seems to work just fine but it still closes out the form and goes to the main form. What I want is that if someone leaves a control blank and they get a message box saying which control they need to correct (code that I have works fine for that part) then have it cancel the update and stay on the current form. i.e. ignore my customized code to take them back to "home".

By way of comparing, if I do an incomplete form and click the "X" to close out of access it gives me a message saying something to the effect that the record can't be saved and if I hit no, it stays on the form and doesn't do anything.

-Stoss
 
All in the logic of the procedure. Remember the Cancel only cancels the update not the procedure itself.
 
Hmm,

Then I guess I should ask the question: What is the best way most people handle this? I mean, this seems to be a pretty basic and what I would think a common task. Don't most people code in validation and if the validation fails, it brings them back to the form?

-Stoss
 
I do it by running the validation in a function. The return value of the function is a boolean which indicates whether the validation test has succeeed.

It can be called from both the BeforeUpdate Event and the custom menu procedures. The return value is used to decide if the the rest of the procedure should run. The BeforeUpdate instance triggers Cancel = True while in the menu procedures it skips the usual actions and stays on the form.
 
Thanks for the input!

I am still a little confused about how to get around this. I have a custom menu and it can be launched from just about every form in my access database. I am having difficulty figuring out how to call this and have it apply to just this form (i.e. special validation that only applies to this form).

For Example, if a user is inputting data in this form but they have not yet completed it and then they click "Home" (or "Exit" or "Reports", etc.) I want it to cancel. But, some other forms that don't need validation they may click "Home" and I just want it to close the current form and take them Home.

Not sure if helps what I am trying to accomplish and where my confusion is at.

Any further help would be greatly appreciated!

-Stoss
 
Use the Screen.ActiveForm property to determine the context of the menu selection. This can be used to determine if the validation should be performed and applied to the execution using an If Then structure.

I would probably pass the form object as an argument of the validation function and encapsulate the logic there. This will also make a useful reference to the calling form available inside the function for refering to its controls or directing actions such as SetFocus.

This keeps the function general because you can refer by the argument rather than explicitly naming the form in the code. While this might not mean much difference when validating a single form it is invaluable when you extend the functionality to multiple contexts.

Of course the Screen.ActiveForm property can instead be read from inside the function without using the argument. However I prefer to use the argument approach so an inactive form can be referenced if necessary.
 
I think I understand what you are saying, just having a few issues. Basically, it doesn't run :confused: I can't get it to run the function (I believe that is where the problem is). Not sure where the problem is in my code...

Here is the code from my customized menu
Code: 
If Screen.ActiveForm.name = "CallsQuery" Then
    If TCValidation(Screen.ActiveForm.name) = False Then
        Exit Function
    End If
''''Rest of the menu code taken out.....
End If

Here is the Function that I am calling
Code: 
Function TCValidation(frmName As Form) As Boolean
    Dim strDescription As String
        
    If IsNull(frmName.Controls("Car")) Then
        strDescription = "Car"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Date")) Then
        strDescription = "Date"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Call_Start_Time")) Then
        strDescription = "Call Start Time"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Category")) Then
        strDescription = "Category"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Estimated_Maintenance_Delay")) Then
        strDescription = "Delay"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Text110")) Then
        strDescription = "Interruption"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Combo47")) Then
        strDescription = "Technician"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Problem")) Then
        strDescription = "Problem"
        GoSub messagebox
    ElseIf IsNull(frmName.Controls("Action_Taken")) Then
        strDescription = "Action Taken"
        GoSub messagebox
    End If
    
    TCValidation = True
    
    'This exit sub is needed if all criteria are met and so the following code is not run again.
    Exit Function
    
messagebox:
    MsgBox """" & strDescription & """ is a required field.", vbExclamation, "Required Field"
    frmName.Controls("Call_Resolved") = False
    TCValidation = False
    
End Function

-Stoss
 
The argument should be passed as a form object rather than its name. Just drop the .Name from the line where you call the function and it will probably work.

Note that:
Code: 
.Controls("controlname")
can be written as:
Code: 
 .controlname

Another tip. Use the With Block

Code: 
With frmName
     If IsNull(.somecontrolname) Then
     ElseIf IsNull(.anothercontrolname) Then
     etc
End With

Better still you can turn this code into a few lines with a Loop.

Code: 
Dim ctrl as Control
 
For Each ctrl in frmName
    If IsNull(ctrl) Then
       strDescription = ctrl.Name
       GoSub messagebox
    End If
Next

This is only the bare bones of a loop.

You must also add tests to select only controls that you want to check. This often involves using the .Type property of the control to pick only textboxes and avoid buttons, lines, subforms etc.

Another way to select controls for processing is with the .Tag property. Alternatively or as well, the Tag can be used to store the strDescription. The tag is just a string so information can be encoded into it and parsed back out to the limit of your imagination.

There are many other ways too, such as using naming patterns to select the controls for processing. Some techniques information stored in tables or arrays.
 
Last edited:
Thanks all for the help, I think I got it.

Galaxiom, thanks for the suggestion about the passing the object, etc. I was still having problems for hours and then I found that I hadn't declared the form in the function. I really didn't know I needed to but I tried it and it seems to work. I will play with it more tomorrow night.

Thanks vbaInet, I will take a look at it tonight. I am sure it will have great info in it for me.

Thanks!
-Stoss
 
Almost everything is working now.

One problem still though. Two of the controls that I am checking are memo fields. I am guessing at this point that is the reason why the IsNull() is not working correctly on them. I have read other articles with this problem but can't seem to get it to work.

Anyone have any suggestions that would work with my code? All the controls in the code listed above test fine but the 2 that don't are the memo fields ("Problem" and "Action Taken").

Thanks,
-Stoss
 
I have never used a memo field. Just a guess but maybe they are never really null but contain the NullString.

Try testing Len(fieldname) = 0

Memo fields are known to be more easily corrupted than text especially when they get beyond 2000 characters because the rest of the data is then stored in a BLOB. Some developers isolate memo fields into a related table for this reason.

Memo fields also don't cooperate with queries very well.

If I needed a lot of text I would probably use a related table with multiple records in a Text datatype field.
 
Ok, got it to work. Very weird workaround but the following seemed to work.

I ended up using the IsNull() because what I noticed was if the cursor was still in the memo field and I would hit the "Home" key it wouldn't detect that I had something in there. But, if I left the field it would see it for the correct value. So, my workaround was at the beginning of the With statement I did a .setfocus on one of the other controls. Then it would check the memo fields and if there was nothing, I did a .setfocus back to the control that was blank.

Kind of a funky workaround but it seems to work so far. I will have to test it more.

Thanks so much for your time and effort!!!
-Stoss
 
The Value property of the control is only updated after you leave it. Probably the best bet is to move the focus to another control as the first step in the menu procedure.

BTW For the current text in the control that has the focus use the Text property.
However note the Text property is not valid in a control that doesn't have the focus.
 
Stoss said:
Anyone have any suggestions that would work with my code? All the controls in the code listed above test fine but the 2 that don't are the memo fields ("Problem" and "Action Taken").
Click to expand...
Change the Allow Zero Length property of the memo fields in the table to No. IsNull() should work after doing that.

Just fyi, the Len() test should have been:
Code: 
If Len([MemoField] [COLOR=Red]& vbNullString[/COLOR]) = 0 Then
     ' It's empty
End If
Or another test
If Nz([MemoField], vbNullString) = vbNullString Then
' It's empty
End If[/code]
 
vbaInet said:
Change the Allow Zero Length property of the memo fields in the table to No. IsNull() should work after doing that.
Click to expand...

I would not expect changing the field property in the table to make any difference because this test is applied to a control on the form.

The Allow Zero Length property will only come into effect when the record is saved.

The real problem was the focus was still on the control so the Value property was still Null despite text having been typed.
 
You must log in or register to reply here.

Similar threads

P
Need VBA Code Guidance - BeforeUpdate Event
Replies
6
Views
670
The_Doc_Man
The_Doc_Man
C
Before Update event on a continuous form doesn't work
Replies
4
Views
274
The_Doc_Man
The_Doc_Man
MajP
Using A Collection Class to Raise Common Event
2
Replies
33
Views
989
Pat Hartman
P
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
597
Mike Krailo
Mike Krailo
T
White Space in Reports
Replies
6
Views
377
TheSearcher
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom