[AccessD] Form Validation
Rocky Smolin
rockysmolin2 at gmail.com
Tue Nov 1 14:46:39 CDT 2022
Well it was John Clark on OT. But...if the shoe fits...
On Tue, Nov 1, 2022 at 11:33 AM John Colby <jwcolby at gmail.com> wrote:
> Hey, I resemble that remark🤔!
>
> On Mon, Oct 31, 2022 at 11:49 AM Rocky Smolin <rockysmolin2 at gmail.com>
> wrote:
>
> > 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
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> John W. Colby
> Colby Consulting
> --
> 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