conditional formatting datasheet view

[Keith G Hicks]

First off, I don't like continuous forms. So if that's the only way to do
this, then let me know so I can abandon the idea. I've looked at the Lebans
A2KConditionalFormattingVer27.mdb and it seems to only handle colors and
only for either 1 or alternate rows. I couldn't find another one on their
site for datasheet layouts.

I have a subform that's set up as datasheet view. There are several
columns. One of the columns shows a numeric value (it is a text field
though because it can store other than numeric data for certain rows) and
another stores the type of value that is in that first column. For example,
if the 2nd column says "TIME" then the first column is a time value. If the
2nd column says "Numeric" then the 1st column is just an integer. Of course
if I set the format of the 1st column to something to show it as time then
all the rows show as time including the ones I don't want to.

I know that I can set up conditional formatting in the FORMAT property to
show colors for if the value is positive, negative or 0, and of course
that's only 3 conditions, but what about the case where I need more than 3
conditions and where I am not interested in changing the color. I need my
time values to display hh:mm and my numeric ones to be like 123.23. This of
course does not handle the input masking problem.

Also, please please please don't tell me that I should rethink the database
and design it differently. That is not helpful here. It's set up this way
for good reasons that would take up way too much space to explain in this
post and has been very successfully running for several years in a
production environment. I wrote code in the OnCurrent event so that if they
are on a record where the field should display as time then that field does
but of course so do all the others. Then when they move a numeric only
field then all of the rows switch formatting AND THE INPUT MASK to numeric.
It works and they are ok with it. But it bugs me a little. If I were doing
this in Delphi and using something like the Developer Express Quantum grid,
this would be a simple task. Of course this is not Delphi.

I could also leave the input masking and the formatting out of this and
allow users to type in the text or values as needed and then pop up
messages if they enter time values inappropriately, but that's grungy. I'd
really rather not do that.

Ideally I want to have both formatting and masking operate appropriately for
each row independently of the others.

Thanks for any help you can render,

Keith

 

[Dan W]

*First off, I don't like continuous forms. So if that's the only wa
to do
this, then let me know so I can abandon the idea. I've looked at th
Lebans
A2KConditionalFormattingVer27.mdb and it seems to only handle color
and
only for either 1 or alternate rows. I couldn't find another one o
their
site for datasheet layouts.

I have a subform that's set up as datasheet view. There are several
columns. One of the columns shows a numeric value (it is a tex
field
though because it can store other than numeric data for certain rows
and
another stores the type of value that is in that first column. Fo
example,
if the 2nd column says "TIME" then the first column is a time value.
If the
2nd column says "Numeric" then the 1st column is just an integer. O
course
if I set the format of the 1st column to something to show it as tim
then
all the rows show as time including the ones I don't want to.

I know that I can set up conditional formatting in the FORMA
property to
show colors for if the value is positive, negative or 0, and o
course
that's only 3 conditions, but what about the case where I need mor
than 3
conditions and where I am not interested in changing the color.
need my
time values to display hh:mm and my numeric ones to be like 123.23
This of
course does not handle the input masking problem.

Also, please please please don't tell me that I should rethink th
database
and design it differently. That is not helpful here. It's set up thi
way
for good reasons that would take up way too much space to explain i
this
post and has been very successfully running for several years in a
production environment. I wrote code in the OnCurrent event so tha
if they
are on a record where the field should display as time then tha
field does
but of course so do all the others. Then when they move a numeri
only
field then all of the rows switch formatting AND THE INPUT MASK t
numeric.
It works and they are ok with it. But it bugs me a little. If I wer
doing
this in Delphi and using something like the Developer Expres
Quantum grid,
this would be a simple task. Of course this is not Delphi.

I could also leave the input masking and the formatting out of thi
and
allow users to type in the text or values as needed and then pop up
messages if they enter time values inappropriately, but that'
grungy. I'd
really rather not do that.

Ideally I want to have both formatting and masking operat
appropriately for
each row independently of the others.

Thanks for any help you can render,

Keith *

I've also wanted to do what you are wanting to do. Unfortunately,
don't know any way to change the behavior -it seems to be part of th
bits from Microsoft.

I'm writing to offer one alternative to so the column makes sense whe
viewed in datasheet view, but it involves adding a 3rd column, whic
could maybe be hidden.

For the displayed column, format the value in the query with th
Format() function, using iif() to determine which way format to use
something like this:
ColumnC
iif(ColumnA="NUMERIC",Format(ColumnB,"Standard"),iif(ColumnA="TIME",Format(ColumnB,"Shor
Time"))

The drawback of this solution is you now have a calculated field tha
can't be edited. So, you could hide ColumnB in the detail section
have some sort of user action call vb code to get the user-input, an
set the hidden value with the users returned value. Some difficultie
there are the limitations of InputBox(), namely no input mas
functionality, or having to build a small form for dialog input with a
input mask, creating a way (like a vb function) to return the valu
entered, etc.

Of course another work-around is to just have 3 columns so it doesn'
matter so much the the input column changes appearance as you move fro
record to record, but that's not a very elegant solution either.

Good Luck


-
Dan