Changing only the current record's formatting according to a field's value, in a countinous form.

[Amir]

Hi!



I have a continous form with some fields, like: ProductKeyInReceipt,
Diameter etc.

I want the Diameter field to not be locked only if the product that the user
selects from the ProductKeyInReceipt should have a diameter (otherwise I
want it to be 0). In addition I want the combobox of the diameter to change
it's color due to the selection in the ProductKeyInReceipt combobox (so that
the color will 'tell' the user if this product should have a diameter or
not).



Here is the solution I've used:



In the both the Form_Current event and in the
ProductKeyInReceipt_AfterUpdate event I used the following code:



If Me!ProductKeyInReceipt.Column(3) = False Then

' Column(3) is a boolean value which

'tells if the product should have a diameter or not.

Me!Diameter.BackColor = 9009253

Me!Diameter.ForeColor = 14935011

Me!Diameter.Value = 0

Me!Diameter.Enabled = False

Me!Diameter.Locked = True

Else

Me!Diameter.BackColor = 15723495

Me!Diameter.ForeColor = 0

Me!Diameter.Enabled = True

Me!Diameter.Locked = False

Me!ProductKeyInReceipt.SetFocus

End If





This works fine in single form, but when I change the form's design to
continous form, this code changes the colors of ALL the records in the view,
instead of changing only the colors in the record the user is currently
editing.



How can I make it change only the current record's colors?



I remember I once heard of a solution to this problem using unbounded
textboxes that are 'on' the bounded comboboxes and copy their value from the
bound comboboxes so they don't change their color when the value changes,
but I don't remember how to do that..



I will be grateful if anyone could answer this (using the solution I've
mentioned or any other solution you might think of..)



Thank you very much!

Regards,

Amir.

 

[Penguin]

Try this link:

http://www.mvps.org/access/forms/frm0024.htm

Hope this helps.

 

[Amir]

Hello,

First of all, Thank you very much for the help!
It seems that I don't know how to apply this solution to my case(That's not
the link I was thinking of..)

In my form the records are always filtered, so there's a problem with
counting the lines.
In addition, I don't exactly understands where in the code access checks in
the table and applies the correct formatting only to the fields I want.
I can see the lines which are changing the ColorHighlight field's fore and
back colors, but how does it change the colors only in the
records which has True value in the ColorHighlight field in the Customers
table?

I would be grateful if you could explain that.

Any other solutions would be great, since I couldn't solve this one so
far... Don't forget the form is always filtered.

Thank you all in advance,
Kind Regards,
Amir.

 

[Marshall Barton]

I have a continous form with some fields, like: ProductKeyInReceipt,
Diameter etc.

I want the Diameter field to not be locked only if the product that the user
selects from the ProductKeyInReceipt should have a diameter (otherwise I
want it to be 0). In addition I want the combobox of the diameter to change
it's color due to the selection in the ProductKeyInReceipt combobox (so that
the color will 'tell' the user if this product should have a diameter or
not).

Here is the solution I've used:
In the both the Form_Current event and in the
ProductKeyInReceipt_AfterUpdate event I used the following code:

If Me!ProductKeyInReceipt.Column(3) = False Then
' Column(3) is a boolean value which
'tells if the product should have a diameter or not.
Me!Diameter.BackColor = 9009253
Me!Diameter.ForeColor = 14935011
Me!Diameter.Value = 0
Me!Diameter.Enabled = False
Me!Diameter.Locked = True
Else [snip]

This works fine in single form, but when I change the form's design to
continous form, this code changes the colors of ALL the records in the view,
instead of changing only the colors in the record the user is currently
editing.

How can I make it change only the current record's colors?

I remember I once heard of a solution to this problem using unbounded
textboxes that are 'on' the bounded comboboxes and copy their value from the
bound comboboxes so they don't change their color when the value changes,
but I don't remember how to do that..

I will be grateful if anyone could answer this (using the solution I've
mentioned or any other solution you might think of..)

You can not do this kind of thing using code. Since there
is only one set of controls, any time you set a property, it
will be displayed for all rows in a continuous form (or
datasheet).

In A97 and earlier, the only way to get this effect is to
use additional controls that are bound to a record source
field, usually as part of a conditional expression. The
article that Penguin pointed you to is a good start. Some
related articles are:
http://www.mvps.org/access/forms/frm0047.htm
http://www.mvps.org/access/forms/frm0055.htm

Starting with A2K, you can use Conditional Formatting
(Format menu) to affect the properties of only the rows that
satisfy your conditions.

The trick of using a **bound** text box to overlay the text
portion of the combo box has been explained many times. Try
searching the Google newsgroup archives for threads on the
topic.

 

[Amir]

I have a continous form with some fields, like: ProductKeyInReceipt,
Diameter etc.

I want the Diameter field to not be locked only if the product that the user
selects from the ProductKeyInReceipt should have a diameter (otherwise I
want it to be 0). In addition I want the combobox of the diameter to change
it's color due to the selection in the ProductKeyInReceipt combobox (so that
the color will 'tell' the user if this product should have a diameter or
not).

Here is the solution I've used:
In the both the Form_Current event and in the
ProductKeyInReceipt_AfterUpdate event I used the following code:

If Me!ProductKeyInReceipt.Column(3) = False Then
' Column(3) is a boolean value which
'tells if the product should have a diameter or not.
Me!Diameter.BackColor = 9009253
Me!Diameter.ForeColor = 14935011
Me!Diameter.Value = 0
Me!Diameter.Enabled = False
Me!Diameter.Locked = True
Else [snip]

This works fine in single form, but when I change the form's design to
continous form, this code changes the colors of ALL the records in the view,
instead of changing only the colors in the record the user is currently
editing.

How can I make it change only the current record's colors?

I remember I once heard of a solution to this problem using unbounded
textboxes that are 'on' the bounded comboboxes and copy their value from the
bound comboboxes so they don't change their color when the value changes,
but I don't remember how to do that..

I will be grateful if anyone could answer this (using the solution I've
mentioned or any other solution you might think of..)

You can not do this kind of thing using code. Since there
is only one set of controls, any time you set a property, it
will be displayed for all rows in a continuous form (or
datasheet).

In A97 and earlier, the only way to get this effect is to
use additional controls that are bound to a record source
field, usually as part of a conditional expression. The
article that Penguin pointed you to is a good start. Some
related articles are:
http://www.mvps.org/access/forms/frm0047.htm
http://www.mvps.org/access/forms/frm0055.htm

Starting with A2K, you can use Conditional Formatting
(Format menu) to affect the properties of only the rows that
satisfy your conditions.

The trick of using a **bound** text box to overlay the text
portion of the combo box has been explained many times. Try
searching the Google newsgroup archives for threads on the
topic.

Thank you all very much!

I think i'll use the conditional formatting option. It seems to suit my
case.

By the way, Is there any way to change the colors in the conditional
formatting
to other custom colors?

Kind Regards,
Amir.

 

[Marshall Barton]

I have a continous form with some fields, like: ProductKeyInReceipt,
Diameter etc.

I want the Diameter field to not be locked only if the product that the user
selects from the ProductKeyInReceipt should have a diameter (otherwise I
want it to be 0). In addition I want the combobox of the diameter to change
it's color due to the selection in the ProductKeyInReceipt combobox (so that
the color will 'tell' the user if this product should have a diameter or
not).

Here is the solution I've used:
In the both the Form_Current event and in the
ProductKeyInReceipt_AfterUpdate event I used the following code:

If Me!ProductKeyInReceipt.Column(3) = False Then
' Column(3) is a boolean value which
'tells if the product should have a diameter or not.
Me!Diameter.BackColor = 9009253
Me!Diameter.ForeColor = 14935011
Me!Diameter.Value = 0
Me!Diameter.Enabled = False
Me!Diameter.Locked = True
Else [snip]

This works fine in single form, but when I change the form's design to
continous form, this code changes the colors of ALL the records in the view,
instead of changing only the colors in the record the user is currently
editing.

How can I make it change only the current record's colors?

I remember I once heard of a solution to this problem using unbounded
textboxes that are 'on' the bounded comboboxes and copy their value from the
bound comboboxes so they don't change their color when the value changes,
but I don't remember how to do that..

I will be grateful if anyone could answer this (using the solution I've
mentioned or any other solution you might think of..)

"Marshall Barton" wrote
You can not do this kind of thing using code. Since there
is only one set of controls, any time you set a property, it
will be displayed for all rows in a continuous form (or
datasheet).

In A97 and earlier, the only way to get this effect is to
use additional controls that are bound to a record source
field, usually as part of a conditional expression. The
article that Penguin pointed you to is a good start. Some
related articles are:
http://www.mvps.org/access/forms/frm0047.htm
http://www.mvps.org/access/forms/frm0055.htm

Starting with A2K, you can use Conditional Formatting
(Format menu) to affect the properties of only the rows that
satisfy your conditions.

The trick of using a **bound** text box to overlay the text
portion of the combo box has been explained many times. Try
searching the Google newsgroup archives for threads on the
topic.

I think i'll use the conditional formatting option. It seems to suit my
case.

By the way, Is there any way to change the colors in the conditional
formatting to other custom colors?

Yes, you can set all the condition properties using code,
probably in the form's open event procedure. Here's an
example I tried in a report:

Private Sub Report_Open(Cancel As Integer)
With Me.txtYear.FormatConditions
.Add acFieldValue, acEqual, 2000
.Item(0).BackColor = RGB(255, 190, 190)
.Item(0).ForeColor = RGB(190, 0, 255)
.Item(0).FontBold = True
.Add acFieldValue, acEqual, 2001
.Item(1).BackColor = RGB(190, 255, 190)
.Item(1).ForeColor = RGB(190, 0, 190)
.Item(1).FontBold = True
End With
End Sub

See FormatConditions Collection in Help for details.

 

[Amir]

Amir wrote:
I have a continous form with some fields, like: ProductKeyInReceipt,
Diameter etc.

I want the Diameter field to not be locked only if the product that

the
user

selects from the ProductKeyInReceipt should have a diameter (otherwise I
want it to be 0). In addition I want the combobox of the diameter to change
it's color due to the selection in the ProductKeyInReceipt combobox

(so
that

the color will 'tell' the user if this product should have a diameter or
not).

Here is the solution I've used:
In the both the Form_Current event and in the
ProductKeyInReceipt_AfterUpdate event I used the following code:

If Me!ProductKeyInReceipt.Column(3) = False Then
' Column(3) is a boolean value which
'tells if the product should have a diameter or not.
Me!Diameter.BackColor = 9009253
Me!Diameter.ForeColor = 14935011
Me!Diameter.Value = 0
Me!Diameter.Enabled = False
Me!Diameter.Locked = True
Else
[snip]

This works fine in single form, but when I change the form's design to
continous form, this code changes the colors of ALL the records in the view,
instead of changing only the colors in the record the user is currently
editing.

How can I make it change only the current record's colors?

I remember I once heard of a solution to this problem using unbounded
textboxes that are 'on' the bounded comboboxes and copy their value

from
the

bound comboboxes so they don't change their color when the value changes,
but I don't remember how to do that..

I will be grateful if anyone could answer this (using the solution I've
mentioned or any other solution you might think of..)

"Marshall Barton" wrote
You can not do this kind of thing using code. Since there
is only one set of controls, any time you set a property, it
will be displayed for all rows in a continuous form (or
datasheet).

In A97 and earlier, the only way to get this effect is to
use additional controls that are bound to a record source
field, usually as part of a conditional expression. The
article that Penguin pointed you to is a good start. Some
related articles are:
http://www.mvps.org/access/forms/frm0047.htm
http://www.mvps.org/access/forms/frm0055.htm

Starting with A2K, you can use Conditional Formatting
(Format menu) to affect the properties of only the rows that
satisfy your conditions.

The trick of using a **bound** text box to overlay the text
portion of the combo box has been explained many times. Try
searching the Google newsgroup archives for threads on the
topic.

I think i'll use the conditional formatting option. It seems to suit my
case.

By the way, Is there any way to change the colors in the conditional
formatting to other custom colors?

Yes, you can set all the condition properties using code,
probably in the form's open event procedure. Here's an
example I tried in a report:

Private Sub Report_Open(Cancel As Integer)
With Me.txtYear.FormatConditions
.Add acFieldValue, acEqual, 2000
.Item(0).BackColor = RGB(255, 190, 190)
.Item(0).ForeColor = RGB(190, 0, 255)
.Item(0).FontBold = True
.Add acFieldValue, acEqual, 2001
.Item(1).BackColor = RGB(190, 255, 190)
.Item(1).ForeColor = RGB(190, 0, 190)
.Item(1).FontBold = True
End With
End Sub

See FormatConditions Collection in Help for details.

I have no words to express my thanks..
You guys are experts like i've never seen..
Accurate, quick, professional and totally clear answers.

Thank you very much!
Kind Regards,
Amir.