[AccessD] Form Validation

John Colby jwcolby at gmail.com
Tue Nov 1 13:34:51 CDT 2022


I would love to be able to dictate but it just isn't reliable yet.  It is
getting much better though.

On Mon, Oct 31, 2022 at 11:57 AM Ryan W <wrwehler at gmail.com> wrote:

> Apple's voice to text hates my voice (or Siri really sucks?).  I'm hard of
> hearing so I can only imagine I mumble a lot or my word pronunciation isn't
> as precise as Siri would like.
>
> OTOH; once when I had an interview for an IT support position in my late
> teens (I'm 44 now) the interviewer had me take a typing test in the next
> room.  About 45 seconds after I began the test they came back and said I
> did not need to take the test as they heard the keys clacking from next
> door and knew that I was more than proficient.
>
> In short; yeah I can be verbose at times.  I prefer typing for
> communication over talking as well.
>
>
>
> On Mon, Oct 31, 2022 at 10: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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
John W. Colby
Colby Consulting


More information about the AccessD mailing list