Solved Validation Code (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:01
Joined
Apr 27, 2015
Messages
6,268
Good morning everyone,

I grinded this out yesterday when the Dev for the App I am documenting reached out to me with a new problem. I was able to make it work but I cant hlp but feel there is a better way to do it.

He wanted a way to disable some controls and alert the user as to which fields were required. Borrowing Gina's fValidateData Function (excellent job Gina!) I was able to give him what he wanted at first until he notified me of the problem.

The problem: I tagged each field with "Required" and all was well - until he told me that two of the Required fields are only required under a certain situation.

The Situation:
- If the fields ("Solicitation" and "DeliveryOrder" are NOT null) OR if the ("Solicitation" field is not null and the "DeleiveryOrder" IS null) then any control with "Required" gets evaluated.
- If the Fields "DeliveryOrder" is NOT null and the "Solicitation" field is null then all but two of the required fields get evaluated.

To account for this, for those records that fall into the second category, I added a "1" at the end of the "Required" tag: "Required1", designating that record as a "Delivery Order Only" record. I check for this on the Form's Current() event and it assigns either True or False to a variable: blnDO_Only. I pass the form name and blnDO_Only value to the function and it works.

The code:
Code:
Function fValidateData(frm As Form, Optional blnDO_only As Boolean) As Boolean
On Error GoTo ErrHandler

'From  https://www.access-diva.com/vba13.html
'Use with Command Button on Subform or on single Form in Before_Update event
'rtw 8.29.2015 Updated to cut off "ID" and split based on CamelCase
'rtw 7.30.2019 Updated to add highlighting of Control with missing data

    Dim ctl As Control
    Dim blnValid As Boolean

    blnValid = True

    For Each ctl In frm.Controls
        If ctl.Tag <> "" Then
            If ctl.Enabled Then
                If InStr(1, ctl.Tag, "Require") Then
                    If Nz(ctl, "") = "" Then
                        If InStr(1, ctl.Tag, "1") Then
                            If blnDO_only Then
                                Select Case ctl.ControlType
                                    Case acTextBox
                                        ctl.BackColor = vbWhite
                                    Case acComboBox
                                        ctl.BackColor = vbWhite
                                End Select
                            Else
                                blnValid = False
                                    Select Case ctl.ControlType
                                        Case acTextBox
                                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                                        Case acComboBox
                                            ctl.BackColor = RGB(254, 242, 154)  'Yellow
                                    End Select
                                    'Sets the focus to the Control
                                ctl.SetFocus
                                GoTo Complete
                            End If
                        Else
                            blnValid = False
                                Select Case ctl.ControlType
                                    Case acTextBox
                                        ctl.BackColor = RGB(254, 242, 154)  'Yellow
                                    Case acComboBox
                                        ctl.BackColor = RGB(254, 242, 154)  'Yellow
                                End Select
                                    'Sets the focus to the Control
                                ctl.SetFocus
                                GoTo Complete
                        End If
                    Else
                 'If more than one Control will turn the filled in ones back to white
                        Select Case ctl.ControlType
                            Case acTextBox
                                ctl.BackColor = vbWhite
                            Case acComboBox
                                ctl.BackColor = vbWhite
                        End Select
                    End If
                End If
            End If
        End If
    Next ctl

Complete:
    Set ctl = Nothing
    fValidateData = blnValid
    'Debug.Print "Valid = " & blnValid
    Exit Function

ErrHandler:
    blnValid = False
    MsgBox ("Error validating: " & Err.Description)
    Resume Complete
End Function

As I said, it works exactly as I intend it to, but the fact that I have to use the same code twice in a single function grates on me but it was the only way I was able to get it to work.

I thought maybe I could pass the validation part off to a separate sub but I so not see how that could work. If this is the way it has to be then I'll live with it, but if there is something I am over-looking I would be interested in what you think.

Thanks in advance!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:01
Joined
Apr 27, 2015
Messages
6,268
Anyone? There is NO way I got this right on the first try...
 

Minty

AWF VIP
Local time
Today, 09:01
Joined
Jul 26, 2013
Messages
10,346
I have an app that required a mixture of fields to be completed based on some complex conditions.
I handled it by enabling or disabling other controls after certain fields were completed and by values entered.

In the "Required" tag checking I then initially checked if the control was enabled, if it was then I also checked for the tag, then the contents.
It gave me a way of switching in and out the checking based on a variety of conditions, not sure if that can help you in this situation.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:01
Joined
Sep 21, 2011
Messages
13,964
I would have thought you could set/clear the tag property and use that validation function as normal?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:01
Joined
Jul 9, 2003
Messages
16,224
I seem to recall that Colin "Isladog"has some extensive tag-based validation code on his website.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:01
Joined
Jul 9, 2003
Messages
16,224
validation code on his website.
Well I've had a look on Colins website and I can't see it now.


Either I'm imagining things, or he's removed it --- don't know...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:01
Joined
Jul 9, 2003
Messages
16,224
Found it! Bit of lateral thinking... I remember seeing Colin blog about it within (AWF) Access World Forums... I searched on Colin's name and "Tag" and lo and behold found a thread linking back to his website:-

Set Controls
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:01
Joined
Jul 9, 2003
Messages
16,224
Well this is a bit odd? None of the links at the bottom of Colin's homepage appear to be working. Also, in the the tab at the top of the browser I've got the word NULL showing...

Home Page:-

Is it anybody else experiencing the same thing, or is it just me?
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:01
Joined
Sep 21, 2011
Messages
13,964
Using my phone and I see null top left,but can get to programs on the menu?
 

isladogs

MVP / VIP
Local time
Today, 09:01
Joined
Jan 14, 2017
Messages
18,164
My current website was built using Flash and can no longer be edited.
Unfortunately the links on my home page no longer work. The null issue is related to that. For now the menu items still work.
All other content on the rest of the site is still working except for links on the JATFA page.

Its a total pain .The entire website will need to be be redone over the next few months.

In the meantime, I think this was the page being referred to Set Controls - Mendip Data Systems
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:01
Joined
Feb 19, 2002
Messages
42,872
I don't know if you found the solution yet but here's one if you need it.

In the current event of the record run code to determine if the optional fields are required and set/reset the tag property appropriately. Then the other code will work when you are validating.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:01
Joined
Apr 27, 2015
Messages
6,268
Thanks Pat,

The code I posted works perfectly and I have it on the Current event as well as the After Update on the required fields (contemplating a Class Mod for that).

The issue I had was having to use the same code in different parts of the sub. I was hoping for some advice on how to avoid that.
 

Isaac

Lifelong Learner
Local time
Today, 02:01
Joined
Mar 14, 2017
Messages
8,711
I started to leverage the Tag property as a place to dump validation type of information at one point in the past, but abandoned it after not very much use for this very reason. It's too one-dimensional and any logic beyond a simple yes/no can't easily be made to work. Unless you start layering in more and more logic (like multiple data tags with multiple meanings, perhaps delimited, or something) -- But then, once you've gone to those lengths, is it any easier than just writing code to handle it from scratch? At least this was my take on it.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:01
Joined
Jul 9, 2003
Messages
16,224
I made a container out of an MS Access Rectangle.


You can surround a group of controls with it and treat them as if you'd tagged them. Handy if you use the tag for something else.

I imagine a scenario where you could have a complicated setup like a venn diagram on your form, grouping the different controls as you require, without worrying about trying to tag this one or that one. You just either increase the size of the rectangle or move the control over.

Free sample available. PM me for details...
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:01
Joined
Apr 27, 2015
Messages
6,268
Ok, I'm a certifiable a dense brick. I just know realized (after a couple of IPAs) what Minty, Paul (Gasman), and Pat were trying to tell me. Brilliant solution and I can use Gina's code as is

Maybe I should just start drinking earlier in the day...
 

Users who are viewing this thread

Top Bottom