[AccessD] Form Validation

[Ryan W]

This is what I came up with this morning:

As it turns out I was storing the color as a long, but have a lookup table
that allows me to select a human recognizable name rather than just some
integer for the highlighting color.

I had to do some control looping to find the control name of the subform if
the form isn't a standalone form.  I tried to use
frm.Parent.ActiveControl.Name but since the active control is a selectable
index on the left side of the form and not the control in question (and
this runs on Form_Current) this is what I came up with.

Public Function ValidateLoginRules(frm As Form, ClientID As String)
    'loop through validation rules and highlight the appropriate
    'fields if they are empty

    On Error GoTo ValidateLoginRules_Error
    Dim rst         As DAO.Recordset
    Dim strSQL      As String
    Dim FldName     As String
    Dim exField     As String
    Dim fldPath     As String

    strSQL = "SELECT * FROM TBL_LoginValidationRules WHERE
RegexMatch([ClientIDRE], '" & ClientID & "',False,True)"

    Set rst = dbLocal.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        Do Until rst.EOF
            exField = rst!ExceptionField
            FldName = rst!fieldName

            'if the exception rule matches, do not flag the field
            If RegexMatch(rst!ExceptionRule, frm.Controls(exField).value)
Then
                frm.Controls(FldName).BackColor = vbWhite
                frm.Controls(FldName).ControlTipText = vbNullString
                Exit Do
            End If


            If frm.Parent Is Nothing Then 'is the parent form
                fldPath = "Forms!" & frm.Name & "!" & FldName
            Else 'is a child form
                Dim ctl As control

                For Each ctl In Forms(frm.Parent.Name).Controls
                    Select Case ctl.ControlType
                        Case acSubform
                            If ctl.SourceObject = frm.Name Then _
                                fldPath = "Forms!" & frm.Parent.Name & "!"
& _
                                ctl.Name & "!" & FldName
                    End Select
                Next ctl
            End If

            If Eval(Replace(rst!ValidationRule, "[Parameter]", fldPath))
Then
                frm.Controls(FldName).BackColor = rst!HiliteColor
                frm.Controls(FldName).ControlTipText = rst!ControlTipText
            Else
                frm.Controls(FldName).BackColor = vbWhite
                frm.Controls(FldName).ControlTipText = vbNullString
            End If
            rst.MoveNext
        Loop
    End If


ValidateLoginRules_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function

ValidateLoginRules_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure
ValidateLoginRules."
    Resume ValidateLoginRules_Exit

End Function

On Sat, Oct 29, 2022 at 9:31 PM Ryan Wehler <wrwehler at gmail.com> wrote:

> I was thinking it had to be a string with quotes for eval to work. Like
> Eval(“len(blah)>0”) when I wrote that out but was wrong and yeah I forgot
> the bang… that’s what I get for not double checking my work when emailing.
> Hahaha
>
> I’ll work it out when I’m not on a single screen device with a 14”
> display. It was hard to juggle my vba window, access form and gmail.. I
> kept getting lost trying to get the process down and communicate it back.
>
>
>
> > On Oct 29, 2022, at 9:26 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
> wrote:
> >
> > That looks workable ( with an additional "bang" between Me.Name and
> > rst!FieldToScrutinize) :)
> >
> > Why are you wrapping z in double quotes?
> >
> > You could turn the actual evaluaion into a "one-liner" with
> >
> > IF EVAL(Replace(rst!Function, "<fldval>", _
> >     "Forms!" & Me.Name & "!" & rst!FieldToScrutinize)) THEN 'Evaluates
> to True
> > ...
> > ELSE 'Evaluates to False
> > ...
> > END IF
> >
> >
> >> On 29 Oct 2022 at 20:24, Ryan W wrote:
> >>
> >> Yeah the docs mention you can use Forms!Form1!Text0 etc.   I could
> >> simply make the Function Len(Forms!MyForm!MyField)>0 but that locks it
> >> down a bit as far as flexibility.
> >>
> >> I suppose I could make a string
> >>
> >> s = "Forms!" & Me.Name & rst!FieldToScrutinize
> >>
> >> z = chr(34) & Replace(rst!Function, "<fldval>", s) & chr(34)
> >>
> >> eval(z)
> >>
> >>
> >> perhaps?
> >>
> >>
> >>
> >>
> >>
> >>
> >> On Sat, Oct 29, 2022 at 8:19 PM Stuart McLachlan
> >> <stuart at lexacorp.com.pg> wrote:
> >>
> >>>> On 30 Oct 2022 at 10:52, Stuart McLachlan wrote:
> >>>
> >>>> EVAL() is a totally different thing. All it can work with are
> >>>> values and operators. It knows nothing about Object models such as
> >>>> Forms and controls. .
> >>>
> >>> I was mistaken.
> >>> It DOES know about object models. But it needs a fully qualified
> >>> object reference. It just can't evaluate  the reference "me".
> >>>
> >>> This works:
> >>> 'MsgBox Eval("Len(Forms!Form1!Text0) > 0")
> >>> this doesn't:
> >>> MsgBox Eval("Len(Me!Text0) > 0")
> >>>
> >>>
> >>>
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >>>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> https://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >>
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>