MS Access vba BeforeUpdate event on 38 textboxes

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

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

great...will give it a go later on when I'm back at pc....thanks

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?

<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:
https://www.experts-exchange.com/questions/21357890/Need-additional-help-w-For-Each-ctl-In-Me-Controls-function.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

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

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?

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

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

Select allOpen in new window

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

Fantastic....it works a treat!!

Many thanks.

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

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.

??

Then its all good...
;-)

satisfied at both ends....his solution works a treat...thanks for your input tho!