[AccessD] Form Validation
Rocky Smolin
rockysmolin2 at gmail.com
Mon Oct 31 10:49:16 CDT 2022
About voice to text? You could compete with John C for words per day. 😆
On Mon, Oct 31, 2022 at 6:57 AM Ryan W <wrwehler at gmail.com> wrote:
> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
More information about the AccessD
mailing list