Form - Conditional Formatting

[Bill]

I have a drop-down box whose ControlSource
is a table where only a single text field from that
table is displayed, table name = FolioID.

With the drop-down box selected in design view,
I clicked on Formatting -> Conditional Formatting
from the menu-bar and specified:

Expression is: left([FolioID],1) = "S"

My intent was to toggle the color when the expression
evaluates to True.

Can I even do this in a drop-down box?

Thanks,
Bill

 

[Bill]

I don't know if it makes any difference, but
it's the "RowSource" that defines the contents
of the dropdown, not the ControlSource as
I originally posted.
Bill

 

[John W. Vinson]

I have a drop-down box whose ControlSource
is a table where only a single text field from that
table is displayed, table name = FolioID.

With the drop-down box selected in design view,
I clicked on Formatting -> Conditional Formatting
from the menu-bar and specified:

Expression is: left([FolioID],1) = "S"

My intent was to toggle the color when the expression
evaluates to True.

Can I even do this in a drop-down box?

Thanks,
Bill

What's the RowSource of the combo? The Value of the combo box is its bound
column, which might not be visible and might not start with S.

Note that this is quite different from the ControlSource, which is the field
into which the combo's value will be stored.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

The RowSource is comprised of 3 columns
taken from a 3 column table, only one of
which is displayed. Column count is three,
but the column widths are 0";0";1". It is the
3rd column whose table name is "FolioID".
The bound column is entered in the properties
of the combo as 2, as I assume the columns
are zero origin indexing.

The combo box in design view shows as being
"unbound".

Thanks John for taking the time to look at this
one for me.

Bill

I have a drop-down box whose ControlSource
is a table where only a single text field from that
table is displayed, table name = FolioID.

With the drop-down box selected in design view,
I clicked on Formatting -> Conditional Formatting
from the menu-bar and specified:

Expression is: left([FolioID],1) = "S"

My intent was to toggle the color when the expression
evaluates to True.

Can I even do this in a drop-down box?

Thanks,
Bill

What's the RowSource of the combo? The Value of the combo box is its bound
column, which might not be visible and might not start with S.

Note that this is quite different from the ControlSource, which is the
field
into which the combo's value will be stored.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John W. Vinson]

The RowSource is comprised of 3 columns
taken from a 3 column table, only one of
which is displayed. Column count is three,
but the column widths are 0";0";1". It is the
3rd column whose table name is "FolioID".
The bound column is entered in the properties
of the combo as 2, as I assume the columns
are zero origin indexing.

Which of the three columns contains the letter S? The Bound Column is actually
1-based not 0-based.

You may be SEEING an S (from the third column) but Access is perhaps seeing a
number (the second column); the number will not meet the conditional
formatting criterion. What you might be able to do is use

Expression is: Left([comboboxname].Column(1),1) = "S"

The Column property, unlike the Bound Column property, is in fact zero based
so this would pull the second column. Use (2) if your S is in the third (the
visible) column.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

John,

When present, the "S" will be the first character of
the data (FolioID) in the 3rd column. The primary
key is in the first column, a special sort key is in the
2nd column and the "FolioID" is in the 3rd column.
The RowSource is a query that returns the 3
columns with the sorting criteria expressed in
the 2nd column.

The combo's bound column is 3 and I tried
LEFT([cmboRetreats]).Column(2),1)="S"
in the conditional formatting without success.

I put this statement in the combo's OnClick event:

MsgBox Left([cmboRetreats].Column(2), 1)

and it in fact displayed the first character, which
happened to be an "S" as purposely selected.

I'm clueless as to what to try next?

Bill

The RowSource is comprised of 3 columns
taken from a 3 column table, only one of
which is displayed. Column count is three,
but the column widths are 0";0";1". It is the
3rd column whose table name is "FolioID".
The bound column is entered in the properties
of the combo as 2, as I assume the columns
are zero origin indexing.

Which of the three columns contains the letter S? The Bound Column is
actually
1-based not 0-based.

You may be SEEING an S (from the third column) but Access is perhaps
seeing a
number (the second column); the number will not meet the conditional
formatting criterion. What you might be able to do is use

Expression is: Left([comboboxname].Column(1),1) = "S"

The Column property, unlike the Bound Column property, is in fact zero
based
so this would pull the second column. Use (2) if your S is in the third
(the
visible) column.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Douglas J Steele]

As John pointed out, the BoundColumn property is 1 based, not 0 based (which
the Column collection is...)

Change your BoundColumn to 3.

"Bill" wrote in message

John,

When present, the "S" will be the first character of
the data (FolioID) in the 3rd column. The primary
key is in the first column, a special sort key is in the
2nd column and the "FolioID" is in the 3rd column.
The RowSource is a query that returns the 3
columns with the sorting criteria expressed in
the 2nd column.

The combo's bound column is 3 and I tried
LEFT([cmboRetreats]).Column(2),1)="S"
in the conditional formatting without success.

I put this statement in the combo's OnClick event:

MsgBox Left([cmboRetreats].Column(2), 1)

and it in fact displayed the first character, which
happened to be an "S" as purposely selected.

I'm clueless as to what to try next?

Bill

The RowSource is comprised of 3 columns
taken from a 3 column table, only one of
which is displayed. Column count is three,
but the column widths are 0";0";1". It is the
3rd column whose table name is "FolioID".
The bound column is entered in the properties
of the combo as 2, as I assume the columns
are zero origin indexing.

Which of the three columns contains the letter S? The Bound Column is
actually
1-based not 0-based.

You may be SEEING an S (from the third column) but Access is perhaps
seeing a
number (the second column); the number will not meet the conditional
formatting criterion. What you might be able to do is use

Expression is: Left([comboboxname].Column(1),1) = "S"

The Column property, unlike the Bound Column property, is in fact zero
based
so this would pull the second column. Use (2) if your S is in the third
(the
visible) column.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Hi Doug,
As I posted, the bound column IS 3.
Bill

As John pointed out, the BoundColumn property is 1 based, not 0 based
(which the Column collection is...)

Change your BoundColumn to 3.

"Bill" wrote in message

John,

When present, the "S" will be the first character of
the data (FolioID) in the 3rd column. The primary
key is in the first column, a special sort key is in the
2nd column and the "FolioID" is in the 3rd column.
The RowSource is a query that returns the 3
columns with the sorting criteria expressed in
the 2nd column.

The combo's bound column is 3 and I tried
LEFT([cmboRetreats]).Column(2),1)="S"
in the conditional formatting without success.

I put this statement in the combo's OnClick event:

MsgBox Left([cmboRetreats].Column(2), 1)

and it in fact displayed the first character, which
happened to be an "S" as purposely selected.

I'm clueless as to what to try next?

Bill

The RowSource is comprised of 3 columns
taken from a 3 column table, only one of
which is displayed. Column count is three,
but the column widths are 0";0";1". It is the
3rd column whose table name is "FolioID".
The bound column is entered in the properties
of the combo as 2, as I assume the columns
are zero origin indexing.

Which of the three columns contains the letter S? The Bound Column is
actually
1-based not 0-based.

You may be SEEING an S (from the third column) but Access is perhaps
seeing a
number (the second column); the number will not meet the conditional
formatting criterion. What you might be able to do is use

Expression is: Left([comboboxname].Column(1),1) = "S"

The Column property, unlike the Bound Column property, is in fact zero
based
so this would pull the second column. Use (2) if your S is in the third
(the
visible) column.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Just out of curiosity, I created a combo box
using the wizard where I enter the values
myself and named the column "Test". Then,
I entered 3 entries where the 3rd of the 3
begins with the character "S". I.e.,
RowSource is Value List. With the the
conditional formatting set to:
Expression Is Left([Test],1)="S" to display
the item in red, the conditional formatting
had NO EFFECT.

I also tried Left([combo115].Value, 1) = "S".

I don't seem to be having any problems with
other controls with the use of conditional formatting.

I'm at a loss as to what to try next.

Bill

 

[John W. Vinson]

Just out of curiosity, I created a combo box
using the wizard where I enter the values
myself and named the column "Test". Then,
I entered 3 entries where the 3rd of the 3
begins with the character "S". I.e.,
RowSource is Value List. With the the
conditional formatting set to:
Expression Is Left([Test],1)="S" to display
the item in red, the conditional formatting
had NO EFFECT.

I also tried Left([combo115].Value, 1) = "S".

I don't seem to be having any problems with
other controls with the use of conditional formatting.

I'm at a loss as to what to try next.

I'd try a DLookUp looking at the actual table containing the field, using the
value of the combo box as a criterion. You might need to overlay a textbox
(conditionally formatted) over the combo box. I'm certainly perplexed.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

I too am perplexed, as I thought I knew as
much about this stuff as required. Anyway,
I'll revisit some of this again on Sunday.

Thanks John for the time you've given to
this. Hopefully, this will end with some
sort of success.

Bill

Just out of curiosity, I created a combo box
using the wizard where I enter the values
myself and named the column "Test". Then,
I entered 3 entries where the 3rd of the 3
begins with the character "S". I.e.,
RowSource is Value List. With the the
conditional formatting set to:
Expression Is Left([Test],1)="S" to display
the item in red, the conditional formatting
had NO EFFECT.

I also tried Left([combo115].Value, 1) = "S".

I don't seem to be having any problems with
other controls with the use of conditional formatting.

I'm at a loss as to what to try next.

I'd try a DLookUp looking at the actual table containing the field, using
the
value of the combo box as a criterion. You might need to overlay a textbox
(conditionally formatted) over the combo box. I'm certainly perplexed.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

I changed the query so that the items of interest
were in column 1, ignoring the remaining columns.

Using LEFT([cmboRetreats].value,1)="S", I was
able to toggle the color of whatever item in the
list having the "S" that was selected. However,
perhaps I've not stated my desire from the
outset, in that what I've been after here is to
have any item shown in the dropdown that begins
with a "S" display in a different color, like one
would do with text-boxes in a continuous form.

Bill

Just out of curiosity, I created a combo box
using the wizard where I enter the values
myself and named the column "Test". Then,
I entered 3 entries where the 3rd of the 3
begins with the character "S". I.e.,
RowSource is Value List. With the the
conditional formatting set to:
Expression Is Left([Test],1)="S" to display
the item in red, the conditional formatting
had NO EFFECT.

I also tried Left([combo115].Value, 1) = "S".

I don't seem to be having any problems with
other controls with the use of conditional formatting.

I'm at a loss as to what to try next.

I'd try a DLookUp looking at the actual table containing the field, using
the
value of the combo box as a criterion. You might need to overlay a textbox
(conditionally formatted) over the combo box. I'm certainly perplexed.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Jim Bunton]

I'm assuming that you want the forecolor of the text in the combo box to be
red when the FolioName begins with "S".
BUT otherwise be black

If this is the case I think you need to code the OnClick even of the drop
down to be something like:-

Private Sub DropdownName_Click()
if left([DropdownName],1) = "S" then
me.dropdownName.forecolor = 255 'Red
else
me.dropdownName.forecolor = 0 'Black
end if
End Sub

I don't know if it makes any difference, but
it's the "RowSource" that defines the contents
of the dropdown, not the ControlSource as
I originally posted.
Bill

I have a drop-down box whose ControlSource
is a table where only a single text field from that
table is displayed, table name = FolioID.

With the drop-down box selected in design view,
I clicked on Formatting -> Conditional Formatting
from the menu-bar and specified:

Expression is: left([FolioID],1) = "S"

My intent was to toggle the color when the expression
evaluates to True.

Can I even do this in a drop-down box?

Thanks,
Bill

 

[Bill]

Thanks for the thought Jim. That does work if the
item selected begins with a "S", but then applies to
every item being displayed in the dropped list. What
I wanted was for the dropped list itself to be sensitive
to each item displayed so that items that begin with
a "S" stand out in the list. For example, if every other
item begins with a "S", then every other forecolor
would be red.

So far, I've not found a way to get Access to format
the display of the dropped list to toggle forecolor
depending on the first character.

Thanks again,
Bill

I'm assuming that you want the forecolor of the text in the combo box to
be red when the FolioName begins with "S".
BUT otherwise be black

If this is the case I think you need to code the OnClick even of the drop
down to be something like:-

Private Sub DropdownName_Click()
if left([DropdownName],1) = "S" then
me.dropdownName.forecolor = 255 'Red
else
me.dropdownName.forecolor = 0 'Black
end if
End Sub

I don't know if it makes any difference, but
it's the "RowSource" that defines the contents
of the dropdown, not the ControlSource as
I originally posted.
Bill

I have a drop-down box whose ControlSource
is a table where only a single text field from that
table is displayed, table name = FolioID.

With the drop-down box selected in design view,
I clicked on Formatting -> Conditional Formatting
from the menu-bar and specified:

Expression is: left([FolioID],1) = "S"

My intent was to toggle the color when the expression
evaluates to True.

Can I even do this in a drop-down box?

Thanks,
Bill

 

[John W. Vinson]

So far, I've not found a way to get Access to format
the display of the dropped list to toggle forecolor
depending on the first character.

To the best of my knowledge, you cannot do this in a Combo Box or Listbox -
only in a Continuous Form using conditional formatting.

Why isn't your combo box in alphabetical order so that all the S entries will
appear together, and so that you can use autocomplete? (just curious)
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

The contents of the dropdown are Spring and Fall
events that are sorted by year in descending order.
The Spring events are prefixed with a "S" while the
Fall events are prefixed with a "F".

The intent was to simply highlight the Spring events
by changing the forecolor of the display so that they
were readily apparent. Purely a user friendly touch
that isn't a show-stopper.

Bill

 

[Bill]

If time permits, I might just replace the dropdown
with a continuous subform, with only the vertical
scroll, superimposed on a command button using
the same source query and simply toggle its visibility.
Bill