Conditional Formatting w/Requery Causes a Flicker or Flash

[macoldtimer]

The database I am designing is a backup to another program which
tracks calls. Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required. So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc. The problem I am
running into is on the visual side. The original program color-codes
the calls based on certain criteria which I want to match. I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.

The database has a front and back end. One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information. The main form has the
command line and two sub-forms. The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched. Each sub-form’s recordset is based on a query of the
main table.

For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field. The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.

The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection. Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection. If the two values differ then the value of true is
returned, if they are the same then false is returned. This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.] The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.

The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows. Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.

As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above. However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears. For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow. If the person has arrived at the
address then all those fields are aqua blue. This same flashing
effect occurs as above.

Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.

Thanks!

 

[Ken Snell MVP]

Perhaps turn the Echo property off and on as part of your requery action:

Application.Echo False
Me.Requery
Application.Echo True

Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The database I am designing is a backup to another program which
tracks calls. Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required. So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc. The problem I am
running into is on the visual side. The original program color-codes
the calls based on certain criteria which I want to match. I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.

The database has a front and back end. One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information. The main form has the
command line and two sub-forms. The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched. Each sub-form’s recordset is based on a query of the
main table.

For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field. The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.

The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection. Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection. If the two values differ then the value of true is
returned, if they are the same then false is returned. This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.] The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.

The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows. Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.

As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above. However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears. For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow. If the person has arrived at the
address then all those fields are aqua blue. This same flashing
effect occurs as above.

Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.

Thanks!

 

[macoldtimer]

Perhaps turn the Echo property off and on as part of your requery action:

Application.Echo False
Me.Requery
Application.Echo True

Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problem I am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.

The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form has the
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query of the
main table.

For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.

The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.

The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.

As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.

Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.

Thanks!

I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working. Even created a an "on/off" button to see if it was working.
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.

 

[Ken Snell MVP]

I don't know of an alternative method to avoid the flickering. Sorry.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Peter Hibbs]

A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.

What you would need to do is replace your Continuous form with a Flex
Grid control to show the same data. Then you can change the colour of
any cell with a couple of lines of code (and you can have thousands of
different colours if you wish). As a matter of interest I have just
tried something similar on a test form and I can alternate the colour
of a cell every 100mS with no trace of a flicker. Of course, you will
be doing this over a network so it would probably be slower than that
in practice but it should do what you want.

Go to http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

HTH

Peter Hibbs.

Perhaps turn the Echo property off and on as part of your requery action:

Application.Echo False
Me.Requery
Application.Echo True

Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problem I am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.

The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form has the
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query of the
main table.

For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.

The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.

The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.

As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.

Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.

Thanks!

I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working. Even created a an "on/off" button to see if it was working.
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.

 

[macoldtimer]

A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.

What you would need to do is replace your Continuous form with a Flex
Grid control to show the same data. Then you can change the colour of
any cell with a couple of lines of code (and you can have thousands of
different colours if you wish). As a matter of interest I have just
tried something similar on a test form and I can alternate the colour
of a cell every 100mS with no trace of a flicker. Of course, you will
be doing this over a network so it would probably be slower than that
in practice but it should do what you want.

Go tohttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

HTH

Peter Hibbs.

Perhaps turn the Echo property off and on as part of your requery action:
Application.Echo False
Me.Requery
Application.Echo True
Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--
        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problem I am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.
The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form has the
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query ofthe
main table.
For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.
The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.
The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.
As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.
Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.
Thanks!

I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working.  Even created a an "on/off" button to see if it was working.
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.- Hide quoted text -

- Show quoted text -

Thanks for Info and sample database...looks great!!!!

 

[macoldtimer]

A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.

What you would need to do is replace your Continuous form with a Flex
Grid control to show the same data. Then you can change the colour of
any cell with a couple of lines of code (and you can have thousands of
different colours if you wish). As a matter of interest I have just
tried something similar on a test form and I can alternate the colour
of a cell every 100mS with no trace of a flicker. Of course, you will
be doing this over a network so it would probably be slower than that
in practice but it should do what you want.

Go tohttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

HTH

Peter Hibbs.

On Mar 7, 11:28 pm, "Ken Snell MVP" <[email protected]>
wrote:
Perhaps turn the Echo property off and on as part of your requery action:
Application.Echo False
Me.Requery
Application.Echo True
Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--
        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problem I am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.
The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form hasthe
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query of the
main table.
For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from greento red
when someone adds a new “comment” to the field Information field..
This lets the dispatcher know there is new information that needs to
be aired over the radio.
The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.
The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.
As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.
Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.
Thanks!
I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working.  Even created a an "on/off" button to see if it was working..
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.- Hide quoted text-

- Show quoted text -

Thanks for Info and sample database...looks great!!!!

Peter,

I was able to install the ocx file on my work computer(Windows XP and
Access 2003), register it, and open/view the database with no
problems. At home using Windows XP and Access 2007 I get "Object
doesn't support this property or method" and the form does not open.
In a new database with a blank I try adding the Microsoft Flexgrid
Control and it gives an error that "Microsoft Office does not support
this ActiveX control."

I have tried researching the solution to fix this problem, but have
come up with nothing that works. Any ideas?

 

[Peter Hibbs]

OldTimer,

I think this is due to a recent Windows XP Security update. Microsoft
have disabled about 26 ActiveX controls (including MSFLXGRD.OCX) for
security reasons. This site gives more information.

http://www.microsoft.com/technet/security/advisory/960715.mspx

If you have VB6 installed on your PC you can download an update which
will replace those OCX files with new versions. Have a look at :-

http://support.microsoft.com/kb/957924

for the download. You may also need to register the new version again.

Since the FlexGrid demo works on your work PC, I can only assume that
either the security update (KB960715) has NOT been implemented (you
can check this in the Add/Remove form on the Control Panel) or you
already have the newer version of the FlexGrid control on this
computer. The version number for the new control is 6.1.98.12.

It is also possible to either un-install the security update or reset
the KillBit flag in the Registry, the problem with that is that each
time you get a Windows XP Update, the problem just comes back again.

I don't think the fact that you are using Access 2007 at home is
relevant except, of course, that it needs to be in a 'trusted' folder
to work properly.

If you cannot fix the problem with the above, drop me an email and I
will see if I can come up with some alternative solution.

HTH

Peter Hibbs.

A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.

What you would need to do is replace your Continuous form with a Flex
Grid control to show the same data. Then you can change the colour of
any cell with a couple of lines of code (and you can have thousands of
different colours if you wish). As a matter of interest I have just
tried something similar on a test form and I can alternate the colour
of a cell every 100mS with no trace of a flicker. Of course, you will
be doing this over a network so it would probably be slower than that
in practice but it should do what you want.

Go tohttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

Peter Hibbs.

On Mar 7, 11:28 pm, "Ken Snell MVP" <[email protected]>
wrote:
Perhaps turn the Echo property off and on as part of your requery action:

Application.Echo False
Me.Requery
Application.Echo True

Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"macoldtimer" <[email protected]> wrote in message

The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problem I am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.

The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form has the
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query of the
main table.

For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.

The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.

The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.

As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.

Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.

I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working.  Even created a an "on/off" button to see if it was working.
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.- Hide quoted text -

- Show quoted text -

Thanks for Info and sample database...looks great!!!!

Peter,

I was able to install the ocx file on my work computer(Windows XP and
Access 2003), register it, and open/view the database with no
problems. At home using Windows XP and Access 2007 I get "Object
doesn't support this property or method" and the form does not open.
In a new database with a blank I try adding the Microsoft Flexgrid
Control and it gives an error that "Microsoft Office does not support
this ActiveX control."

I have tried researching the solution to fix this problem, but have
come up with nothing that works. Any ideas?

 

[macoldtimer]

OldTimer,

I think this is due to a recent Windows XP Security update. Microsoft
have disabled about 26 ActiveX controls (including MSFLXGRD.OCX) for
security reasons. This site gives more information.

http://www.microsoft.com/technet/security/advisory/960715.mspx

If you have VB6 installed on your PC you can download an update which
will replace those OCX files with new versions.  Have a look at :-

http://support.microsoft.com/kb/957924

for the download. You may also need to register the new version again.

Since the FlexGrid demo works on your work PC, I can only assume that
either the security update (KB960715) has NOT been implemented (you
can check this in the Add/Remove form on the Control Panel) or you
already have the newer version of the FlexGrid control on this
computer. The version number for the new control is 6.1.98.12.

It is also possible to either un-install the security update or reset
the KillBit flag in the Registry, the problem with that is that each
time you get a Windows XP Update, the problem just comes back again.

I don't think the fact that you are using Access 2007 at home is
relevant except, of course, that it needs to be in a 'trusted' folder
to work properly.

If you cannot fix the problem with the above, drop me an email and I
will see if I can come up with some alternative solution.

HTH

Peter Hibbs.

On Mar 8, 6:31 pm, Peter Hibbs <[email protected]_SPAM>
wrote:
A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.
What you would need to do is replace your Continuous form with a Flex
Grid control to show the same data. Then you can change the colour of
any cell with a couple of lines of code (and you can have thousands of
different colours if you wish). As a matter of interest I have just
tried something similar on a test form and I can alternate the colour
of a cell every 100mS with no trace of a flicker. Of course, you will
be doing this over a network so it would probably be slower than that
in practice but it should do what you want.
Go tohttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180
HTH
Peter Hibbs.
28 pm, "Ken Snell MVP" <[email protected]>
wrote:
Perhaps turn the Echo property off and on as part of your requeryaction:
Application.Echo False
Me.Requery
Application.Echo True
Be sure that you also put the "Application.Echo True" step in anyerror
handler so that you turn the Echo back on in case of an error.
--
        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used whenthe
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problemI am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.
The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form has the
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls thathave
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query of the
main table.
For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needsto
be aired over the radio.
The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks atthe
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.
The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.
As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.
Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.
Thanks!
I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working.  Even created a an "on/off" button to see if it was working.
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.- Hide quoted text -
- Show quoted text -
Thanks for Info and sample database...looks great!!!!

Peter,

I was able to install the ocx file on my work computer(Windows XP and
Access 2003), register it, and open/view the database with no
problems.  At home using Windows XP and Access 2007 I get "Object
doesn't support this property or method" and the form does not open.
In a new database with a blank I try adding the Microsoft Flexgrid
Control and it gives an error that "Microsoft Office does not support
this ActiveX control."

I have tried researching the solution to fix this problem, but have
come up with nothing that works.  Any ideas?

I did read the info about the security update prior to posting, but I
do not have VB6 on my home computer or work computer(IT is also ways
way behind on updates). I used the ocx file included in the zip which
is not the latest version. Is there a way of getting the latest
version to replace the old one? Is there new way to the same thing as
a flex grid?

 

[Peter Hibbs]

Email me at the address in the documentation.

Peter Hibbs.

OldTimer,

I think this is due to a recent Windows XP Security update. Microsoft
have disabled about 26 ActiveX controls (including MSFLXGRD.OCX) for
security reasons. This site gives more information.

http://www.microsoft.com/technet/security/advisory/960715.mspx

If you have VB6 installed on your PC you can download an update which
will replace those OCX files with new versions.  Have a look at :-

http://support.microsoft.com/kb/957924

for the download. You may also need to register the new version again.

Since the FlexGrid demo works on your work PC, I can only assume that
either the security update (KB960715) has NOT been implemented (you
can check this in the Add/Remove form on the Control Panel) or you
already have the newer version of the FlexGrid control on this
computer. The version number for the new control is 6.1.98.12.

It is also possible to either un-install the security update or reset
the KillBit flag in the Registry, the problem with that is that each
time you get a Windows XP Update, the problem just comes back again.

I don't think the fact that you are using Access 2007 at home is
relevant except, of course, that it needs to be in a 'trusted' folder
to work properly.

If you cannot fix the problem with the above, drop me an email and I
will see if I can come up with some alternative solution.

HTH

Peter Hibbs.

On Mar 8, 6:31 pm, Peter Hibbs <[email protected]_SPAM>
wrote:

A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.

What you would need to do is replace your Continuous form with a Flex
Grid control to show the same data. Then you can change the colour of
any cell with a couple of lines of code (and you can have thousands of
different colours if you wish). As a matter of interest I have just
tried something similar on a test form and I can alternate the colour
of a cell every 100mS with no trace of a flicker. Of course, you will
be doing this over a network so it would probably be slower than that
in practice but it should do what you want.

Go tohttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

Peter Hibbs.

On Mar 7, 11:28 pm, "Ken Snell MVP" <[email protected]>
wrote:
Perhaps turn the Echo property off and on as part of your requery action:

Application.Echo False
Me.Requery
Application.Echo True

Be sure that you also put the "Application.Echo True" step in any error
handler so that you turn the Echo back on in case of an error.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"macoldtimer" <[email protected]> wrote in message

The database I am designing is a backup to another program which
tracks calls.  Mine is a cut-down version meant to be used when the
main program crashes, rarely, or when it is taken down for two to
three hours once a month for maintenance. I am trying hard to make
Access function the exact same the original program so there is no
learning curve or training time required.  So, far this meant using a
text box as a command line to open the form(s) to input the calls,
dispatch the calls, add comments to the calls etc.  The problem I am
running into is on the visual side.  The original program color-codes
the calls based on certain criteria which I want to match.  I am using
conditional formatting to color-code, but have found out since I need
to requery every 2-3 seconds, the colors make a flash or flicker
effect. This flashing or flickering is what I need to eliminate.

The database has a front and back end.  One reason I need to requery
so often is that several people will be entering and updating calls at
one time with every time sensitive information.  The main form has the
command line and two sub-forms.  The top sub-form, named frmNonDisp,
is a continuous form which displays uncompleted active calls that have
not been dispatched. The bottom sub-form, named frmDisp, is a
continuous form which displays uncompleted active calls which have
been dispatched.  Each sub-form’s recordset is based on a query of the
main table.

For calls that are received and not dispatched (top sub-form called
frmNonDisp) conditional formatting occurs for two fields, the “Status”
field and the “Time” field.  The Status field turns from green to red
when someone adds a new “comment” to the field Information field.
This lets the dispatcher know there is new information that needs to
be aired over the radio.

The way I achieve this is having the query call a VBA function. As the
form loads it queries all active calls it adds the call number and
information field to a custom collection.  Every time the call
requeries it checks to see if data in the information field of the
table (tblcalls) differs from the information in the custom
collection.  If the two values differ then the value of true is
returned, if they are the same then false is returned.  This Boolean
value is assigned to an invisible check box, which is NOT bound to the
table (tblcalls). Once the dispatcher opens the call and looks at the
information field the data is then passed to the custom collection
making them “equal” thus returning a false on the next requery turning
the Status field green. [The reason I do not store this value in the
table is that if another calltaker or dispatcher looks at the
information it would “clear” or return the value of false to the
table, and the Main dispatcher would possibly never have seen that
there was new information.]  The requery thus briefly returns all
Status fields to green for a fraction of a second, before each Status
field that needs to be red turns red for a second or two then it
requery happens all over.

The “Timer” field turns from green to red, using conditional
formatting, when the query calls a function to see how long the call
has been received and the elapsed time is greater than what the
priority of the call allows.  Again, this function turns True or False
and assigns it to an invisible check box, which is NOT bound to the
table.

As for dispatched calls (bottom sub-form called frmDisp) the “Status”
field still changes to red in the same manor has above.  However,
depending on the whether a person is enroute to the call or arrived at
a call determines what color the entire record/line appears.  For
example, if a person is enroute the call number, priority, address,
status, etc would all be yellow.  If the person has arrived at the
address then all those fields are aqua blue.  This same flashing
effect occurs as above.

Any suggestions on how to constantly show new active calls and flag
for new information for each user, whether or not another user has
viewed the new information, without having the conditional formatting
flicker/flash on every requery.

I have tried turn the Echo off as listed above and it appears to have
no effect at all. I have even put a break-point in to make sure it is
working.  Even created a an "on/off" button to see if it was working.
When I turned Echo off no visible update occured as soon as I turned
it back on everything visibly updated and flickered.- Hide quoted text -

- Show quoted text -

Thanks for Info and sample database...looks great!!!!

I was able to install the ocx file on my work computer(Windows XP and
Access 2003), register it, and open/view the database with no
problems.  At home using Windows XP and Access 2007 I get "Object
doesn't support this property or method" and the form does not open.
In a new database with a blank I try adding the Microsoft Flexgrid
Control and it gives an error that "Microsoft Office does not support
this ActiveX control."

I have tried researching the solution to fix this problem, but have
come up with nothing that works.  Any ideas?

I did read the info about the security update prior to posting, but I
do not have VB6 on my home computer or work computer(IT is also ways
way behind on updates). I used the ocx file included in the zip which
is not the latest version. Is there a way of getting the latest
version to replace the old one? Is there new way to the same thing as
a flex grid?