[AccessD] Converting table data to Expression?

Thu May 20 07:37:07 CDT 2021

Paul,

  I'm sure it was written before conditional formatting, but the thing I was mainly pointing to was the highlighting of required fields, not so much highlighting the current field.

  There is a neat routine there which does it all for you automatically.   I still have a variation of that in my base code today even (conditional formatting adds quite a few CPU cycles, especially if you have multiple rules on every control).

Jim.

-----Original Message-----
From: AccessD On Behalf Of Paul Wolstenholme
Sent: Wednesday, May 19, 2021 11:36 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Converting table data to Expression?

Jim,

You referred to  http://allenbrowne.com/highlight.html which was last
updated in 2009.
I think Allen must have written that before conditional formatting
supported the rule description "Field Has Focus" (which is supported in
Access 2010).  Most of his article refers to setting up got focus and lost
focus events on all the controls.  That will be why he wrote that his
solution does not work on continuous forms.

Paul Wolstenholme

On Thu, 20 May 2021 at 14:36, Rocky Smolin <rockysmolin2 at gmail.com> wrote:

> I assumed that the update with fire when they click a button labeled save.
> That might be a way to enforce completing the record. Barring that, you can
> give them a warning message when they try to leave the record, that is
> moved to a new record or a different record, telling them they haven’t
> completed all of the fields and that they must completed before moving to a
> different record. Yes the app won’t allow them to leave that record,
> eventually they’ll figure out if they have to complete all the fields.
> Especially if you draw their attention to the fields which still have a
> yellow background and on the third try include some insulting message about
> what a bonehead they are and please complete the fields!
>
> On Tue, May 18, 2021 at 9:38 PM Ryan W <wrwehler at gmail.com> wrote:
>
> > Well the issue is sometimes they skip some of the “required” fields all
> > together. So when they don’t enter the field at all, no after update
> event
> > can fire.
> >
> >
> >
> > Sent from my iPhone
> >
> > > On May 18, 2021, at 9:40 PM, Rocky Smolin <rockysmolin2 at gmail.com>
> > wrote:
> > >
> > > Conditional formatting won't, IIRC, work on an unbound text box.  But
> > could
> > > you run the rule on that box in the after update event of the text box?
> > >
> > > AFAIK, you can't conditionally format the rows in a cmbo box.  But
> > here's a
> > > couple of alternative ideas:
> > >
> > >
> >
> https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3f9ea9e1-dd25-4550-b632-4c4af9e62c2b/conditional-formatting-of-combo-box-text-access-2010?forum=accessdev
> > >
> > > HTH
> > >
> > > r
> > >
> > >
> > >
> > >> On Tue, May 18, 2021 at 6:57 PM Ryan W <wrwehler at gmail.com> wrote:
> > >>
> > >> Yeah the rules would be in their own table. I didn’t think about
> > >> conditional formatting but I’ll have to see if it works on forms. I
> know
> > >> I’ve used it on datasheets but to my recollection never forms text or
> > combo
> > >> boxes.
> > >>
> > >> And yeah the idea is to highlight client specific “requirements” at
> the
> > >> time of data entry.
> > >>
> > >> Jim’s example gave me something to ponder about.
> > >>
> > >>
> > >> Sent from my iPhone
> > >>
> > >>> On May 18, 2021, at 7:51 PM, Rocky Smolin <rockysmolin2 at gmail.com>
> > >> wrote:
> > >>>
> > >>> If it's the display of the validated data that you need I would bind
> > >> those
> > >>> fields to a form and use conditional formatting which I believe you
> can
> > >>> manipulate with code. Instead of putting the rules in the same table
> > with
> > >>> the data I would put all of your rules in a rules table with table
> > name,
> > >>> field name, and rule, and allow those rules to be set by the user
> > >> through a
> > >>> nice restrictive UI so they can roll their own without having to go
> to
> > IT
> > >>> for every rule change.
> > >>>
> > >>> r
> > >>>
> > >>>> On Tue, May 18, 2021 at 12:53 PM Ryan W <wrwehler at gmail.com> wrote:
> > >>>>
> > >>>> Does anyone know how to convert table data to an expression?
> > >>>>
> > >>>> For example:
> > >>>>
> > >>>> I want to make a table called TBL_ValidationRules
> > >>>>
> > >>>> Columns:
> > >>>>
> > >>>> Client
> > >>>> Field
> > >>>> Eval_for
> > >>>>
> > >>>> so say Client A REQUIRES Field B to be NOT NULL.
> > >>>>
> > >>>> So the data would be
> > >>>> Client: A
> > >>>> Field: B
> > >>>> EVAL_FOR: NULL
> > >>>>
> > >>>> So with a button press on a form the code would loop through the
> > >> recordset
> > >>>> for the current record/client in question and highlight or colorize
> > >> Field B
> > >>>> if the field is null.
> > >>>>
> > >>>> I can't use Table or Form validation rules for this because they
> > change
> > >>>> from client to client and sometimes the end user doesn't even ENTER
> a
> > >> field
> > >>>> for a validation rule to fire on it... so I was just thinking of I
> > had a
> > >>>> way for my data entry people to click validate (or have it validate
> on
> > >>>> close/new record) and highlight the deficiencies that would be
> really
> > >> cool.
> > >>>>
> > >>>>
> > >>>> So my question is how would I go about turning the text in the
> > Eval_For
> > >>>> data into the expression eg:
> > >>>>
> > >>>> if me(rst!Field) = rst!Eval_For then me(rst!Field).BackColor=#999999
> > >>>>
> > >>>>
> > >>>> where obviously rst!Eval_for is somehow turned into NULL (so
> > expression
> > >>>> reads:
> > >>>> if me(rst!Field) = null then ..... .
> > >>>>
> > >>>> Unless you fellas have a better idea?
> > >>>> --
> > >>>> 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
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com