MS Access vba BeforeUpdate event on 38 textboxes

Hi,

Is there a way to create a vba function to check 38 textboxes for valid data without having to create 38 different sub procedures (one for each field)?

I currently use the following sub on 1 field only. The other 37 fields need validating in the same way?

Private Sub Amt_Pens_BeforeUpdate(Cancel As Integer)
    If Nz(Me.Amt_Pens, "") = "" Or Me.Amt_Pens < 0 Or Me.Amt_Pens > 100 Then
        MsgBox "Activity amount must be between 0 and 100.", vbInformation, "Activity Alert"
        Cancel = True
    End If
End Sub

FYI. On each textbox there is an AfterUpdate() function that performs calculations on each control so i need the validation to fire on beforeUpdate/LostFocus to avoid this function from running.

Many thanks
andrewpiconnectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewpiconnectAuthor Commented:
I have tried using the following function called on the BeforeUpdate event of each textbox. This uses the controls "tag" option to check whether to validate the control or not. However, after the function has run I cannot get it to put the focus back onto the control that fired it.

Private Function ActivityAlert()
    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl
    If ctrl.ControlType = acTextBox And ctrl.Tag = "ActivityAlert" And ctrl.Value & "" = "" Then
'        'ctrl.Value = 0
        MsgBox "Activity Alertttttttt", vbInformation, "Activity Alert"
'        'Set ctrl = Screen.ActiveControl
        ctrl.SetFocus
        Exit Function
    End If
End Function
0
Jeffrey CoachmanMIS LiasonCommented:
Yes using something like this for Before update will be a challenge.
...and remember you cannot use .setfocus for more than 1 field.
;-)

Sadly, systems like this may never be perfect.
They cannot account for every user data entry scenario...
(unless you make the code very complex)

What you can do is, do this on the Form Before updated event, and the flag the bad controls, using a "HasError" function, ...something like this:

Private Function HasError(ctlName) As Boolean
    'Insert your specific validation below
    If IsNull(ctlName) Then
        HasError = True
    Else
        HasError = False
    End If
End Function


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strErrorControls As String

    For Each ctl In Me.Controls
        If HasError(ctl) = True Then
            strErrorControls = strErrorControls & vbCrLf & ctl.Name & " has invalid data."
            Cancel = True
        End If
    Next ctl
   
    If Len(strErrorControls) > 0 Then
        MsgBox strErrorControls
    End If

End Sub

Again, perhaps not perfect, but it works and you can, perhaps, use this for the basis for something more comprehensive.

Also note that as soon as one control needs a slightly different validation spec, then that will have to be a separate sub.


JeffCoachman
0
andrewpiconnectAuthor Commented:
great...will give it a go later on when I'm back at pc....thanks
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

andrewpiconnectAuthor Commented:
Ok....gave it a go and it didnt work.

I wanted to call a function on the before update event on each control (as the user moves through them in whatever order they choose) not on the before update event of the entire form itself, if that makes any sense and is at all possible?
0
Jeffrey CoachmanMIS LiasonCommented:
<Ok....gave it a go and it didnt work.>
Define "didnt work."

It worked for me, as I edited a record, it would stop me and alert me of the invalid fields

<is at all possible>
...Anything is possible, all that matters is how many resources you want to throw at it.
... ;-)
Now, ...Despite the seemingly "simple" nature of what you are asking, this is not easy to do, (nor is it commonly done) and have it work for every user scenario.

Some issue are:
You cannot use .SetFocus on the before update event
will not trigger for values that are invalid when the record was opened, ...if the field is "skipped" manually by the user.
If the user does not follow the standard tab order.
A new record is added
a record is deleted
filters are on,
Other conflicting errors
...and some form and control properties , ...can all effect this in different ways causing all sorts of unaccepted issues.

Think of a web form,

You fill out all the fields, then after you hit "submit" (or "Continue"), ...only then are *all* fields checked for validity and flagged.

I have seen code systems that do what you are asking, but they all have drawbacks.... see here fro an example:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21357890.html

Have you considered using the validation rule setting in the table for each field?

Finally, what is the issue with having a sub for each control...
If it is 38 fields, you can do this with a copy and paste in about 38 minutes, and you are done forever.
;-)
Even if you need to make a change, you can use find/replace to change all the subs at the same time.
There will be no performance hits using a system like this...

JeffCoachman
0
andrewpiconnectAuthor Commented:
Hi Jeff,

Im not at my pc at the moment so cannot provide exact details of the errors that were shown.

However, its interesting that you mention <using a sub for each control as there will be no performance hits using a system like this>

I am currently using each controls built in validation rule and text, this works well, but the alert box that shows when this is fired also contains a built in hyperlink asking if this info was helpful and it is for this reason I wanted to use another solution.

As the forms module already contains quite a lot of subs and functions i thought it would be more efficient to use a single function rather than 38 subs. If i am wrong then i will use 38 subs like you suggest.

Many thanks
0
andrewpiconnectAuthor Commented:
Additionally.

The form contains approx another 20 or so controls that each perform an action on their AfterUpdate event..showing/hiding labels and other text boxes etc.
The 38 controls in this question each fire another AfterUpdate event (the same for each control) which basically performs a calculation based on other control values and the 38 controls values. If any of the 38 controls (which are defaulted to '0') are accidentally made empty by a user or contain a minus figure then the AfterUpdate event fails due to a null value or invalid value. Hence the reason i require each of the 38 controls to be validated if tbe user makes an invalid change.
Hope this makes sense?
0
Jeffrey CoachmanMIS LiasonCommented:
Then this is more complex than had original thought.

The basic premise of what I stated, still remains.

A "Global" validation system will be to hard to manage for every user scenario.
And with the new complexity you just mentioned, I imaging that this only makes any solution even more complex.

You may find something that works for you, but I rarely build apps that need this type of custom functionality.

Sorry I could not help more...

JeffCoachman
0
IrogSintaCommented:
You could try something like this.  You would just need to put Cancel = VerifyMe in the BeforeUpdate event of all the pertinent controls:
Private Sub Amt_Pens_BeforeUpdate(Cancel As Integer)
    Cancel = VerifyMe
    
End Sub

Private Function VerifyMe() As Boolean
    Const Canx As Integer = -1
    
    With Me.ActiveControl
        If .Name = "Amt_Pens" Then
            If Nz(.Value, "") = "" Or .Value < 0 Or .Value > 100 Then
                MsgBox "Activity amount must be between 0 and 100.", vbInformation, "Activity Alert"
                VerifyMe = Canx
            End If
        Else
            'verification for other controls
        End If
    End With
    
End Function

Open in new window

0
andrewpiconnectAuthor Commented:
IrogSinta,
Thanks for that. However, by the time the other 37 fields into your function the lines of code will be no less than if i just put the following in the BeforeUpdate event of each field:

Private Sub Amt_Pens_BeforeUpdate(Cancel As Integer)
    If Nz(Me.Amt_Pens, "") = "" Or Me.Amt_Pens < 0 Or Me.Amt_Pens > 100 Then
        MsgBox "Activity amount must be between 0 and 100.", vbInformation, "Activity Alert"
        Cancel = True
    End If
End Sub.

Irog & Jeff,

I think we've reached a stalemate here so I am going to use my original sub (above) and put this in the BeforeUpdate event of each field and remove the built in validation rule/text that is in place.

Many thanks for your attempts to assist me with this. You're expertise is always appreciated.

Jeff,

If its okay with you I will accept both yours and my answers as a solution and share the points?

Many thanks
J
0
IrogSintaCommented:
When you said "The other 37 fields need validating in the same way," I thought that you meant that Amt_Pens needed a certain kind of validation and that the other 37 all used the same kind, that's why I figured you would add one more validation to the code I gave you.  

Now if all 38 used the same validation, then you would revise the code this way:
Private Sub Amt_Pens_BeforeUpdate(Cancel As Integer)
    Cancel = VerifyMe
    
End Sub

Private Function VerifyMe() As Boolean
    Const Canx As Integer = -1
    
    With Me.ActiveControl      
        If Nz(.Value, "") = "" Or .Value < 0 Or .Value > 100 Then
            MsgBox "Activity amount must be between 0 and 100.", vbInformation, "Activity Alert"
            VerifyMe = Canx
        End If
    End With
    
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andrewpiconnectAuthor Commented:
Fantastic....it works a treat!!

Many thanks.
0
Jeffrey CoachmanMIS LiasonCommented:
Hey, Ron presented a great solution here, so he should get all the points.
In fact I have flagged this Q for future reference...
;-)

JeffCoachman
0
andrewpiconnectAuthor Commented:
Jeff,
If IrogSinta is "Ron" then i have awarded him all the points....he never posted the solution until after my penultimate comment, but once i saw it and tested it i awarded him the points.

??
0
Jeffrey CoachmanMIS LiasonCommented:
Then its all good...
;-)
0
andrewpiconnectAuthor Commented:
satisfied at both ends....his solution works a treat...thanks for your input tho!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.