In Access 2003, how do you detect when a value changes in a given control. I
have a continous form and in one control (text) the value can be the same for
several records then change to a different value. I would like to have the
control highlighted whenever the value changes. Is this possible?

Thanks for your help.
--
rhardy

Hi rhardy,
you could probably use conditional formatting to do this.
It's on the format menu when you select a textbox.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"rhardy" <(E-Mail Removed)> wrote in message
news:C85DC5B2-8784-48AC-82FA-(E-Mail Removed)...
> In Access 2003, how do you detect when a value changes in a given control.
> I
> have a continous form and in one control (text) the value can be the same
> for
> several records then change to a different value. I would like to have
> the
> control highlighted whenever the value changes. Is this possible?
>
> Thanks for your help.
> --
> rhardy

You would have to use a subquery to determine the "previous" value and
then perhaps you could use that with conditional formatting. Without a
lot more details, it is difficult to tell you an exact solution.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


rhardy wrote:
> In Access 2003, how do you detect when a value changes in a given control. I
> have a continous form and in one control (text) the value can be the same for
> several records then change to a different value. I would like to have the
> control highlighted whenever the value changes. Is this possible?
>
> Thanks for your help.

I have a list of addresses that have an assigned route number, they are
displayed in a continous form sorted by route number. There are approx 20
addresses per route and I would like to change the color of one of the
controls whenever the route number changes. I am stuck on how to tell when
the route number changes and then trigger some type of conditional formatting.
--
rhardy


"John Spencer" wrote:

> You would have to use a subquery to determine the "previous" value and
> then perhaps you could use that with conditional formatting. Without a
> lot more details, it is difficult to tell you an exact solution.
>
>
>
> '====================================================
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
> '====================================================
>
>
> rhardy wrote:
> > In Access 2003, how do you detect when a value changes in a given control. I
> > have a continous form and in one control (text) the value can be the same for
> > several records then change to a different value. I would like to have the
> > control highlighted whenever the value changes. Is this possible?
> >
> > Thanks for your help.
>

I am assuming that you want to show all the routes with the same number in
the same color when the form opens.
Perhaps you mean that when the user edits a route number you want to
temporarily change the color?
Do you mean something else again?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"rhardy" <(E-Mail Removed)> wrote in message
newsCC0DD3F-124D-4A5A-897F-(E-Mail Removed)...
>I have a list of addresses that have an assigned route number, they are
> displayed in a continous form sorted by route number. There are approx 20
> addresses per route and I would like to change the color of one of the
> controls whenever the route number changes. I am stuck on how to tell
> when
> the route number changes and then trigger some type of conditional
> formatting.
> --
> rhardy
>
>
> "John Spencer" wrote:
>
>> You would have to use a subquery to determine the "previous" value and
>> then perhaps you could use that with conditional formatting. Without a
>> lot more details, it is difficult to tell you an exact solution.
>>
>>
>>
>> '====================================================
>> John Spencer
>> Access MVP 2002-2005, 2007-2009
>> The Hilltop Institute
>> University of Maryland Baltimore County
>> '====================================================
>>
>>
>> rhardy wrote:
>> > In Access 2003, how do you detect when a value changes in a given
>> > control. I
>> > have a continous form and in one control (text) the value can be the
>> > same for
>> > several records then change to a different value. I would like to have
>> > the
>> > control highlighted whenever the value changes. Is this possible?
>> >
>> > Thanks for your help.
>>

Unfortunately with conditional formatting, there is no way to reference
anything other than the current record (e.g. you can't run a comparison on
this value versus the previous value). Conditional formatting will not work
with functions either, which is very limiting. Also unfortunately,
continuous forms cannot be formatted by record... whatever you change in one
record will change in all records, except a few examples using conditional
formatting.

All in all, you're rather limited. The maximum different colors you will
every get from cond. formatting is four (default, and three expressions it
allows).

If you don't have more than 4 different routes to format, you should be ok,
but if you do, there's only one thing I can think of, and it won't work that
great unless you know what routes are going to be displayed every time.

Consider you have routes 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10

You have 4 conditions (3 + default)

If Number = 1, 5, or 10, leave at default
If Number = 2, or 6, set to color1
If number = 3 or 7, set to color2
if number = 4 or 8 set to color 3

(if you actually go this route you could set them up by mathematical
operators and comparisons to handle a larger number of possiblilities)

So, if you are ordered by number, and the numbers are sequential, and will
remain that way, you could effectively format your form so you will see
something like

grey
grey
grey
blue
blue
white
white
white
red
red
grey
blue
blue
white
red
red
.....


The only problem is, if there is ever numbers missing and the returned
records will be non-sequential, you can never really count on two whites not
whowing up next to each other where they shouldnt. I'm guessing that the
numbers will be nonsequential, but thats the best I can offer.

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill


"rhardy" wrote:

> I have a list of addresses that have an assigned route number, they are
> displayed in a continous form sorted by route number. There are approx 20
> addresses per route and I would like to change the color of one of the
> controls whenever the route number changes. I am stuck on how to tell when
> the route number changes and then trigger some type of conditional formatting.
> --
> rhardy
>
>
> "John Spencer" wrote:
>
> > You would have to use a subquery to determine the "previous" value and
> > then perhaps you could use that with conditional formatting. Without a
> > lot more details, it is difficult to tell you an exact solution.
> >
> >
> >
> > '====================================================
> > John Spencer
> > Access MVP 2002-2005, 2007-2009
> > The Hilltop Institute
> > University of Maryland Baltimore County
> > '====================================================
> >
> >
> > rhardy wrote:
> > > In Access 2003, how do you detect when a value changes in a given control. I
> > > have a continous form and in one control (text) the value can be the same for
> > > several records then change to a different value. I would like to have the
> > > control highlighted whenever the value changes. Is this possible?
> > >
> > > Thanks for your help.
> >

It really sucks that conditional formatting only works within itself. It
could be an extremely powerful tool otherwise.

AFAIK, as far as VBA is concerned, there's no such thing as conditional
formatting, and as far as conditional formatting is concerned, there's no
such thing as VBA. This makes conditional formatting nearly useless for an
operation like this.

You can do some google searches on background colors in continuous forms and
see a few examples of how to use conditional formatting to highlight the
selected record, or to alternate colors per record,
white/blue/white/blue/white.... Both of these examples are slow and choppy
(scroll down a list of records and the colors can't keep up, looks like crap,
would never use it in a professional situation).

But I think my previous post will be about as far as you will ever be able
to stretch the funcionability of conditional formatting.

John had mentioned something about subquerying, and somehow possibly working
conditional formatting off that, but I'm not quite sure what his idea was
there (perhaps he wasn't fully understanding of what you were trying to do,
and perhaps I'm incorrect in the fact that conditional formatting cannot be
used anywhere but the current record).

Anyway, I don't mean to go on and on, but I don't think conditional
formatting is what you are looking for, and if John has an idea for getting
it to work based off a subquery, I would love to hear it. Unfortunately, I
don't think there's a good way to accomplish what you are trying to do (and I
almost never say that).

good luck

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill


"dymondjack" wrote:

> Unfortunately with conditional formatting, there is no way to reference
> anything other than the current record (e.g. you can't run a comparison on
> this value versus the previous value). Conditional formatting will not work
> with functions either, which is very limiting. Also unfortunately,
> continuous forms cannot be formatted by record... whatever you change in one
> record will change in all records, except a few examples using conditional
> formatting.
>
> All in all, you're rather limited. The maximum different colors you will
> every get from cond. formatting is four (default, and three expressions it
> allows).
>
> If you don't have more than 4 different routes to format, you should be ok,
> but if you do, there's only one thing I can think of, and it won't work that
> great unless you know what routes are going to be displayed every time.
>
> Consider you have routes 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
>
> You have 4 conditions (3 + default)
>
> If Number = 1, 5, or 10, leave at default
> If Number = 2, or 6, set to color1
> If number = 3 or 7, set to color2
> if number = 4 or 8 set to color 3
>
> (if you actually go this route you could set them up by mathematical
> operators and comparisons to handle a larger number of possiblilities)
>
> So, if you are ordered by number, and the numbers are sequential, and will
> remain that way, you could effectively format your form so you will see
> something like
>
> grey
> grey
> grey
> blue
> blue
> white
> white
> white
> red
> red
> grey
> blue
> blue
> white
> red
> red
> ....
>
>
> The only problem is, if there is ever numbers missing and the returned
> records will be non-sequential, you can never really count on two whites not
> whowing up next to each other where they shouldnt. I'm guessing that the
> numbers will be nonsequential, but thats the best I can offer.
>
> hth
>
> --
> Jack Leach
> www.tristatemachine.com
>
> - "Success is the ability to go from one failure to another with no loss of
> enthusiasm." - Sir Winston Churchill
>
>
> "rhardy" wrote:
>
> > I have a list of addresses that have an assigned route number, they are
> > displayed in a continous form sorted by route number. There are approx 20
> > addresses per route and I would like to change the color of one of the
> > controls whenever the route number changes. I am stuck on how to tell when
> > the route number changes and then trigger some type of conditional formatting.
> > --
> > rhardy
> >
> >
> > "John Spencer" wrote:
> >
> > > You would have to use a subquery to determine the "previous" value and
> > > then perhaps you could use that with conditional formatting. Without a
> > > lot more details, it is difficult to tell you an exact solution.
> > >
> > >
> > >
> > > '====================================================
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2009
> > > The Hilltop Institute
> > > University of Maryland Baltimore County
> > > '====================================================
> > >
> > >
> > > rhardy wrote:
> > > > In Access 2003, how do you detect when a value changes in a given control. I
> > > > have a continous form and in one control (text) the value can be the same for
> > > > several records then change to a different value. I would like to have the
> > > > control highlighted whenever the value changes. Is this possible?
> > > >
> > > > Thanks for your help.
> > >

dymondjack, thank you I understand what you are saying. Fortunatley I do
have a finite number of routes and they are sequential, so I may be able to
get the example you gave to work. Thanks for your time and insight.
--
rhardy


"dymondjack" wrote:

> Unfortunately with conditional formatting, there is no way to reference
> anything other than the current record (e.g. you can't run a comparison on
> this value versus the previous value). Conditional formatting will not work
> with functions either, which is very limiting. Also unfortunately,
> continuous forms cannot be formatted by record... whatever you change in one
> record will change in all records, except a few examples using conditional
> formatting.
>
> All in all, you're rather limited. The maximum different colors you will
> every get from cond. formatting is four (default, and three expressions it
> allows).
>
> If you don't have more than 4 different routes to format, you should be ok,
> but if you do, there's only one thing I can think of, and it won't work that
> great unless you know what routes are going to be displayed every time.
>
> Consider you have routes 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
>
> You have 4 conditions (3 + default)
>
> If Number = 1, 5, or 10, leave at default
> If Number = 2, or 6, set to color1
> If number = 3 or 7, set to color2
> if number = 4 or 8 set to color 3
>
> (if you actually go this route you could set them up by mathematical
> operators and comparisons to handle a larger number of possiblilities)
>
> So, if you are ordered by number, and the numbers are sequential, and will
> remain that way, you could effectively format your form so you will see
> something like
>
> grey
> grey
> grey
> blue
> blue
> white
> white
> white
> red
> red
> grey
> blue
> blue
> white
> red
> red
> ....
>
>
> The only problem is, if there is ever numbers missing and the returned
> records will be non-sequential, you can never really count on two whites not
> whowing up next to each other where they shouldnt. I'm guessing that the
> numbers will be nonsequential, but thats the best I can offer.
>
> hth
>
> --
> Jack Leach
> www.tristatemachine.com
>
> - "Success is the ability to go from one failure to another with no loss of
> enthusiasm." - Sir Winston Churchill
>
>
> "rhardy" wrote:
>
> > I have a list of addresses that have an assigned route number, they are
> > displayed in a continous form sorted by route number. There are approx 20
> > addresses per route and I would like to change the color of one of the
> > controls whenever the route number changes. I am stuck on how to tell when
> > the route number changes and then trigger some type of conditional formatting.
> > --
> > rhardy
> >
> >
> > "John Spencer" wrote:
> >
> > > You would have to use a subquery to determine the "previous" value and
> > > then perhaps you could use that with conditional formatting. Without a
> > > lot more details, it is difficult to tell you an exact solution.
> > >
> > >
> > >
> > > '====================================================
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2009
> > > The Hilltop Institute
> > > University of Maryland Baltimore County
> > > '====================================================
> > >
> > >
> > > rhardy wrote:
> > > > In Access 2003, how do you detect when a value changes in a given control. I
> > > > have a continous form and in one control (text) the value can be the same for
> > > > several records then change to a different value. I would like to have the
> > > > control highlighted whenever the value changes. Is this possible?
> > > >
> > > > Thanks for your help.
> > >

I was just playing around with this a little bit more... here's the syntax
you will need.


From the dropdown:
Expression Is

and the text:
([fldID]=1) Or ([fldID]=2)

etc etc


I wasn't sure it was going to work (i hadn't tried it before my earlier
post, that was basically off the top of my head), but turns out it will.
That's the syntax you need though... Field Value won't work, thats the first
place I tried. The above assumes a field name of fldID of course...

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill


"rhardy" wrote:

> dymondjack, thank you I understand what you are saying. Fortunatley I do
> have a finite number of routes and they are sequential, so I may be able to
> get the example you gave to work. Thanks for your time and insight.
> --
> rhardy
>
>
> "dymondjack" wrote:
>
> > Unfortunately with conditional formatting, there is no way to reference
> > anything other than the current record (e.g. you can't run a comparison on
> > this value versus the previous value). Conditional formatting will not work
> > with functions either, which is very limiting. Also unfortunately,
> > continuous forms cannot be formatted by record... whatever you change in one
> > record will change in all records, except a few examples using conditional
> > formatting.
> >
> > All in all, you're rather limited. The maximum different colors you will
> > every get from cond. formatting is four (default, and three expressions it
> > allows).
> >
> > If you don't have more than 4 different routes to format, you should be ok,
> > but if you do, there's only one thing I can think of, and it won't work that
> > great unless you know what routes are going to be displayed every time.
> >
> > Consider you have routes 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
> >
> > You have 4 conditions (3 + default)
> >
> > If Number = 1, 5, or 10, leave at default
> > If Number = 2, or 6, set to color1
> > If number = 3 or 7, set to color2
> > if number = 4 or 8 set to color 3
> >
> > (if you actually go this route you could set them up by mathematical
> > operators and comparisons to handle a larger number of possiblilities)
> >
> > So, if you are ordered by number, and the numbers are sequential, and will
> > remain that way, you could effectively format your form so you will see
> > something like
> >
> > grey
> > grey
> > grey
> > blue
> > blue
> > white
> > white
> > white
> > red
> > red
> > grey
> > blue
> > blue
> > white
> > red
> > red
> > ....
> >
> >
> > The only problem is, if there is ever numbers missing and the returned
> > records will be non-sequential, you can never really count on two whites not
> > whowing up next to each other where they shouldnt. I'm guessing that the
> > numbers will be nonsequential, but thats the best I can offer.
> >
> > hth
> >
> > --
> > Jack Leach
> > www.tristatemachine.com
> >
> > - "Success is the ability to go from one failure to another with no loss of
> > enthusiasm." - Sir Winston Churchill
> >
> >
> > "rhardy" wrote:
> >
> > > I have a list of addresses that have an assigned route number, they are
> > > displayed in a continous form sorted by route number. There are approx 20
> > > addresses per route and I would like to change the color of one of the
> > > controls whenever the route number changes. I am stuck on how to tell when
> > > the route number changes and then trigger some type of conditional formatting.
> > > --
> > > rhardy
> > >
> > >
> > > "John Spencer" wrote:
> > >
> > > > You would have to use a subquery to determine the "previous" value and
> > > > then perhaps you could use that with conditional formatting. Without a
> > > > lot more details, it is difficult to tell you an exact solution.
> > > >
> > > >
> > > >
> > > > '====================================================
> > > > John Spencer
> > > > Access MVP 2002-2005, 2007-2009
> > > > The Hilltop Institute
> > > > University of Maryland Baltimore County
> > > > '====================================================
> > > >
> > > >
> > > > rhardy wrote:
> > > > > In Access 2003, how do you detect when a value changes in a given control. I
> > > > > have a continous form and in one control (text) the value can be the same for
> > > > > several records then change to a different value. I would like to have the
> > > > > control highlighted whenever the value changes. Is this possible?
> > > > >
> > > > > Thanks for your help.
> > > >

I need to make a correction...

> Conditional formatting will not work with functions

The above is a false statement (unbeknownst to me until a moment ago).

You can reference a public function that returns a boolean datatype
(possibly others, boolean was all I tried), using the Expression Is and
typing the function name and arguments in the text box.

I was never aware of this... had actually thought that I tried doing this at
some point in the past and came to the conclusion that it was not possible.


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill


"dymondjack" wrote:

> Unfortunately with conditional formatting, there is no way to reference
> anything other than the current record (e.g. you can't run a comparison on
> this value versus the previous value). Conditional formatting will not work
> with functions either, which is very limiting. Also unfortunately,
> continuous forms cannot be formatted by record... whatever you change in one
> record will change in all records, except a few examples using conditional
> formatting.
>
> All in all, you're rather limited. The maximum different colors you will
> every get from cond. formatting is four (default, and three expressions it
> allows).
>
> If you don't have more than 4 different routes to format, you should be ok,
> but if you do, there's only one thing I can think of, and it won't work that
> great unless you know what routes are going to be displayed every time.
>
> Consider you have routes 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10
>
> You have 4 conditions (3 + default)
>
> If Number = 1, 5, or 10, leave at default
> If Number = 2, or 6, set to color1
> If number = 3 or 7, set to color2
> if number = 4 or 8 set to color 3
>
> (if you actually go this route you could set them up by mathematical
> operators and comparisons to handle a larger number of possiblilities)
>
> So, if you are ordered by number, and the numbers are sequential, and will
> remain that way, you could effectively format your form so you will see
> something like
>
> grey
> grey
> grey
> blue
> blue
> white
> white
> white
> red
> red
> grey
> blue
> blue
> white
> red
> red
> ....
>
>
> The only problem is, if there is ever numbers missing and the returned
> records will be non-sequential, you can never really count on two whites not
> whowing up next to each other where they shouldnt. I'm guessing that the
> numbers will be nonsequential, but thats the best I can offer.
>
> hth
>
> --
> Jack Leach
> www.tristatemachine.com
>
> - "Success is the ability to go from one failure to another with no loss of
> enthusiasm." - Sir Winston Churchill
>
>
> "rhardy" wrote:
>
> > I have a list of addresses that have an assigned route number, they are
> > displayed in a continous form sorted by route number. There are approx 20
> > addresses per route and I would like to change the color of one of the
> > controls whenever the route number changes. I am stuck on how to tell when
> > the route number changes and then trigger some type of conditional formatting.
> > --
> > rhardy
> >
> >
> > "John Spencer" wrote:
> >
> > > You would have to use a subquery to determine the "previous" value and
> > > then perhaps you could use that with conditional formatting. Without a
> > > lot more details, it is difficult to tell you an exact solution.
> > >
> > >
> > >
> > > '====================================================
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2009
> > > The Hilltop Institute
> > > University of Maryland Baltimore County
> > > '====================================================
> > >
> > >
> > > rhardy wrote:
> > > > In Access 2003, how do you detect when a value changes in a given control. I
> > > > have a continous form and in one control (text) the value can be the same for
> > > > several records then change to a different value. I would like to have the
> > > > control highlighted whenever the value changes. Is this possible?
> > > >
> > > > Thanks for your help.
> > >