Make Backstyle transparent on continuous form

[RFrechette]

Hopefully, someone can help me with this.

I have a continuous form that has a check box in each record. If the box is
checked, it means that the user is finished with that record. I want the
BackStyle of only that record's controls to change to Transparent when the
Checkbox is checked.

I can almost do this with the Conditional Formatting tool, however it just
lets me pick a BackColor and I want the control to be the same grey shade as
the background of the form.

To test, I've tried making just one of the controls in the record
transparent. But when I check the box, every record on the form has that one
control's BackStyle changed to Transparent, not just the record I checked.

Here's what I got in VB:

If [Chk_Box] = -1 Then

With Me!txt_AcctNo
.BackStyle = 0
End With

Else

With Me!txt_AcctNo
.BackStyle = 1
End With

End If

Can someone please help me? I'm going out of my mind.

Thank you in advance.
Rachel

 

[SteveM]

There is a way without using conditional formatting.

Set all your textboxes to transparent.

Place another textbox underneath each one (also transparent)
Set the ControlSource to: =IIf(mycheckbox = True, True, "")
Set the ForeColor of the textbox to white (or whatever you currently have as
the BackColor)
Set the FontName to System
Set the Format to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

Give it a try!

Steve

 

[SteveM]

Sorry Rachel, a slight change to get it working the way you want...

The ControlSource should be: =IIf(mycheckbox = True, "", True)

Experiment with font sizes if the textboxes don't fill fully.
You may also want to set the boder to transparent for these textboxes.

Steve

There is a way without using conditional formatting.

Set all your textboxes to transparent.

Place another textbox underneath each one (also transparent)
Set the ControlSource to: =IIf(mycheckbox = True, True, "")
Set the ForeColor of the textbox to white (or whatever you currently have as
the BackColor)
Set the FontName to System
Set the Format to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

Give it a try!

Steve

Hopefully, someone can help me with this.

I have a continuous form that has a check box in each record. If the box is
checked, it means that the user is finished with that record. I want the
BackStyle of only that record's controls to change to Transparent when the
Checkbox is checked.

I can almost do this with the Conditional Formatting tool, however it just
lets me pick a BackColor and I want the control to be the same grey shade as
the background of the form.

To test, I've tried making just one of the controls in the record
transparent. But when I check the box, every record on the form has that one
control's BackStyle changed to Transparent, not just the record I checked.

Here's what I got in VB:

If [Chk_Box] = -1 Then

With Me!txt_AcctNo
.BackStyle = 0
End With

Else

With Me!txt_AcctNo
.BackStyle = 1
End With

End If

Can someone please help me? I'm going out of my mind.

Thank you in advance.
Rachel

 

[AccessVandal via AccessMonster.com]

Hi Rachel,

Where have you put this code into?

RFrechette wrote:
If [Chk_Box] = -1 Then

With Me!txt_AcctNo
.BackStyle = 0
End With

Else

With Me!txt_AcctNo
.BackStyle = 1
End With

End If

 

[RFrechette]

I put it in the AfterUpdate of the checkbox control.

Hi Rachel,

Where have you put this code into?

RFrechette wrote:
If [Chk_Box] = -1 Then

With Me!txt_AcctNo
.BackStyle = 0
End With

Else

With Me!txt_AcctNo
.BackStyle = 1
End With

End If

 

[RFrechette]

Hi Steve,

Thank you for your response.

I'm not sure what I'm doing wrong while using your instructions, but it's
not working for me.

I put another textbox (txt_AcctNo1) under the original textbox (txt_AcctNo).
Both BackStyles are set to Transparent.
I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
I set the ForeColor of txt_AcctNo to White
(You said to make this whatever I have as the BackColor, my original
BackColor is white, so it stays white when the check box is checked and my
control is still not transparent. My original problem is basically, I need
to get the BackColor to be the same color as my form is in the background.)
I set the FontName to System
I set the Format to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"
(That just gave me ";"ÛÛÛÛÛÛ...." as the contents of the new control when
the checkbox was checked.)

I'm not sure what I'm doing wrong.

I'm thinking there must be a way to do this using VB in the AfterUpdate
event of the checkbox control.

Something like:

If [Checkbox] = True Then

Me!txt_AcctNo.BackStyle...(make it Transparent)

Else

Me!txt_AcctNo.BackStyle...(make it white)

End If

Any ideas on this?

Thank you,
Rachel

Sorry Rachel, a slight change to get it working the way you want...

The ControlSource should be: =IIf(mycheckbox = True, "", True)

Experiment with font sizes if the textboxes don't fill fully.
You may also want to set the boder to transparent for these textboxes.

Steve

There is a way without using conditional formatting.

Set all your textboxes to transparent.

Place another textbox underneath each one (also transparent)
Set the ControlSource to: =IIf(mycheckbox = True, True, "")
Set the ForeColor of the textbox to white (or whatever you currently have as
the BackColor)
Set the FontName to System
Set the Format to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

Give it a try!

Steve

Hopefully, someone can help me with this.

I have a continuous form that has a check box in each record. If the box is
checked, it means that the user is finished with that record. I want the
BackStyle of only that record's controls to change to Transparent when the
Checkbox is checked.

I can almost do this with the Conditional Formatting tool, however it just
lets me pick a BackColor and I want the control to be the same grey shade as
the background of the form.

To test, I've tried making just one of the controls in the record
transparent. But when I check the box, every record on the form has that one
control's BackStyle changed to Transparent, not just the record I checked.

Here's what I got in VB:

If [Chk_Box] = -1 Then

With Me!txt_AcctNo
.BackStyle = 0
End With

Else

With Me!txt_AcctNo
.BackStyle = 1
End With

End If

Can someone please help me? I'm going out of my mind.

Thank you in advance.
Rachel

 

[SteveM]

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White

Should be txt_AcctNo1

Steve

 

[RFrechette]

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel

 

[RFrechette]

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

 

[SteveM]

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

 

[RFrechette]

Hi Steve,

Thank you for all your help thus far.

I removed the extra textboxes we added before. I changed the textbox
..Enabled property to "No" for all my textboxes. I copy and pasted your code,
first the Public Function in a standard module, then in the OnLoad event -
the Private. Then I changed [myYesNoField] to the name of my checkbox
([ChkBox]), and when I compile it I get this message:

Compile error:
Method or data member not found

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

 

[SteveM]

It goes to the Sub Function that I put in the OnLoad event and stops at

".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve

 

[RFrechette]

Hi Steve,

Again, thank you for everything. When I use the form now, all the textboxes
are not enabled so they are all grayed out. When I check the [ChkBox] field,
it appears that nothing happens. The only difference is that the row has a
checkmark in the [ChkBox] field. I was hoping for any field without a
checkmark in [ChkBox] will be white, once I put a checkmark on that record,
it turns gray.

I understand if you're too busy to continue helping me and you may not want
to spend any more time on this. However, if you wish to continue, here's
what I got...

All textboxes’ .Enabled property is set to 'No'

I have the following code in a standard module:

Public Function ToggleControl(ctl As Control) As Boolean

ToggleControl = ctl.Value

'Steve, when I typed ToggleControl = ctl. above, "Value" does not
show up as one of the options
' to choose from, but I typed it anyway

End Function

In the Form's OnLoad event, I have the following code:

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([ChkBox])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

Thank you for everything.

Rachel

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

:

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve

 

[SteveM]

Ok you are doing this the opposite to what I have done. In my system, records
are ticked when they are 'active' so this is the opposite of what you want.
We just need to change a couple of things.

Set all your textbox properties to Enabled = Yes
Then change .Enabled = True to .Enabled = False in the Onload event
procedure.:

Steve

Hi Steve,

Again, thank you for everything. When I use the form now, all the textboxes
are not enabled so they are all grayed out. When I check the [ChkBox] field,
it appears that nothing happens. The only difference is that the row has a
checkmark in the [ChkBox] field. I was hoping for any field without a
checkmark in [ChkBox] will be white, once I put a checkmark on that record,
it turns gray.

I understand if you're too busy to continue helping me and you may not want
to spend any more time on this. However, if you wish to continue, here's
what I got...

All textboxes’ .Enabled property is set to 'No'

I have the following code in a standard module:

Public Function ToggleControl(ctl As Control) As Boolean

ToggleControl = ctl.Value

'Steve, when I typed ToggleControl = ctl. above, "Value" does not
show up as one of the options
' to choose from, but I typed it anyway

End Function

In the Form's OnLoad event, I have the following code:

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([ChkBox])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

Thank you for everything.

Rachel

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel


:

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

:

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve

 

[RFrechette]

Hi Steve,

After I made the changes you suggested, when I check off the [ChkBox], all
the fields on the form flash gray for a second then back to white. But the
record where the box is checked is still white as well.

These are the changes I made:

For all the textboxes, I changed Enabled to True. And changed .Enabled =
False in the code. (See below)

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([Chk])")

' Set property
With objFcnd
.Enabled = False
End With

End If
Next

Set objFcnd = Nothing

End Sub

Any thoughts?

Thank you so much. I'm sorry this is such a pain in the neck.
Rachel

Ok you are doing this the opposite to what I have done. In my system, records
are ticked when they are 'active' so this is the opposite of what you want.
We just need to change a couple of things.

Set all your textbox properties to Enabled = Yes
Then change .Enabled = True to .Enabled = False in the Onload event
procedure.:

Steve

Hi Steve,

Again, thank you for everything. When I use the form now, all the textboxes
are not enabled so they are all grayed out. When I check the [ChkBox] field,
it appears that nothing happens. The only difference is that the row has a
checkmark in the [ChkBox] field. I was hoping for any field without a
checkmark in [ChkBox] will be white, once I put a checkmark on that record,
it turns gray.

I understand if you're too busy to continue helping me and you may not want
to spend any more time on this. However, if you wish to continue, here's
what I got...

All textboxes’ .Enabled property is set to 'No'

I have the following code in a standard module:

Public Function ToggleControl(ctl As Control) As Boolean

ToggleControl = ctl.Value

'Steve, when I typed ToggleControl = ctl. above, "Value" does not
show up as one of the options
' to choose from, but I typed it anyway

End Function

In the Form's OnLoad event, I have the following code:

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([ChkBox])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

Thank you for everything.

Rachel

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel


:

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

:

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve

 

[SteveM]

I have tested this and it works for me.

Is [Chk] the name of your checkbox - this is what you have:

"ToggleControl([Chk])")

You have formerly called this [ChkBox]

If you still can't get it working, post a temp email address and I will
email you. It would probably be easier if I just do it for you...

Steve

Hi Steve,

After I made the changes you suggested, when I check off the [ChkBox], all
the fields on the form flash gray for a second then back to white. But the
record where the box is checked is still white as well.

These are the changes I made:

For all the textboxes, I changed Enabled to True. And changed .Enabled =
False in the code. (See below)

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([Chk])")

' Set property
With objFcnd
.Enabled = False
End With

End If
Next

Set objFcnd = Nothing

End Sub

Any thoughts?

Thank you so much. I'm sorry this is such a pain in the neck.
Rachel

Ok you are doing this the opposite to what I have done. In my system, records
are ticked when they are 'active' so this is the opposite of what you want.
We just need to change a couple of things.

Set all your textbox properties to Enabled = Yes
Then change .Enabled = True to .Enabled = False in the Onload event
procedure.:

Steve

Hi Steve,

Again, thank you for everything. When I use the form now, all the textboxes
are not enabled so they are all grayed out. When I check the [ChkBox] field,
it appears that nothing happens. The only difference is that the row has a
checkmark in the [ChkBox] field. I was hoping for any field without a
checkmark in [ChkBox] will be white, once I put a checkmark on that record,
it turns gray.

I understand if you're too busy to continue helping me and you may not want
to spend any more time on this. However, if you wish to continue, here's
what I got...

All textboxes’ .Enabled property is set to 'No'

I have the following code in a standard module:

Public Function ToggleControl(ctl As Control) As Boolean

ToggleControl = ctl.Value

'Steve, when I typed ToggleControl = ctl. above, "Value" does not
show up as one of the options
' to choose from, but I typed it anyway

End Function

In the Form's OnLoad event, I have the following code:

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([ChkBox])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

Thank you for everything.

Rachel

:

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel


:

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

:

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve

 

[RFrechette]

Steve,

I have created a Test database and imported the forms, tables, queries, etc
from my original database. I can email this to you if you would like.

It is almost working. The only problem seems to be after I check the box,
the form is only updated when I pass my cursor over the row.

My temporary email address is (e-mail address removed)

Thank you,
Rachel

I have tested this and it works for me.

Is [Chk] the name of your checkbox - this is what you have:

"ToggleControl([Chk])")

You have formerly called this [ChkBox]

If you still can't get it working, post a temp email address and I will
email you. It would probably be easier if I just do it for you...

Steve

Hi Steve,

After I made the changes you suggested, when I check off the [ChkBox], all
the fields on the form flash gray for a second then back to white. But the
record where the box is checked is still white as well.

These are the changes I made:

For all the textboxes, I changed Enabled to True. And changed .Enabled =
False in the code. (See below)

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([Chk])")

' Set property
With objFcnd
.Enabled = False
End With

End If
Next

Set objFcnd = Nothing

End Sub

Any thoughts?

Thank you so much. I'm sorry this is such a pain in the neck.
Rachel

Ok you are doing this the opposite to what I have done. In my system, records
are ticked when they are 'active' so this is the opposite of what you want.
We just need to change a couple of things.

Set all your textbox properties to Enabled = Yes
Then change .Enabled = True to .Enabled = False in the Onload event
procedure.:

Steve

:

Hi Steve,

Again, thank you for everything. When I use the form now, all the textboxes
are not enabled so they are all grayed out. When I check the [ChkBox] field,
it appears that nothing happens. The only difference is that the row has a
checkmark in the [ChkBox] field. I was hoping for any field without a
checkmark in [ChkBox] will be white, once I put a checkmark on that record,
it turns gray.

I understand if you're too busy to continue helping me and you may not want
to spend any more time on this. However, if you wish to continue, here's
what I got...

All textboxes’ .Enabled property is set to 'No'

I have the following code in a standard module:

Public Function ToggleControl(ctl As Control) As Boolean

ToggleControl = ctl.Value

'Steve, when I typed ToggleControl = ctl. above, "Value" does not
show up as one of the options
' to choose from, but I typed it anyway

End Function

In the Form's OnLoad event, I have the following code:

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([ChkBox])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

Thank you for everything.

Rachel

:

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel


:

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

:

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve

 

[Marshall Barton]

Ok you are doing this the opposite to what I have done. In my system, records
are ticked when they are 'active' so this is the opposite of what you want.
We just need to change a couple of things.

Set all your textbox properties to Enabled = Yes
Then change .Enabled = True to .Enabled = False in the Onload event
procedure.:

PMJI, but you triggered a thought.

Instead of messing with the Enabled property, you can set
all the text boxes to use CF with a back color of gray. I
don't think you'll get the sane gray as the form's back
color but it might be acceptable.

If it's not an acceptable shade of gray, you can get the
same color by using code in the form's Open or Load event to
change the CF color:

On Error Resume Next
For Each ctl In Me.Controls
ctl.FormatConditions(1).BackColor = _
Me.Section(0).BackColor
Next ctl

 

[RFrechette]

Hi Marsh,

Thank you for your response. Steve has figured it out for me and it's
working great now. But I really appreciate your input.

Rachel

 

[RFrechette]

Hi Steve,

Thank you so much for all your help. You've got it working perfectly. And
you're willingness to help me outside of the forum was so extremely nice of
you.

I don't know what I would have done without you.

My deepest appreciation,

Rachel

I have tested this and it works for me.

Is [Chk] the name of your checkbox - this is what you have:

"ToggleControl([Chk])")

You have formerly called this [ChkBox]

If you still can't get it working, post a temp email address and I will
email you. It would probably be easier if I just do it for you...

Steve

Hi Steve,

After I made the changes you suggested, when I check off the [ChkBox], all
the fields on the form flash gray for a second then back to white. But the
record where the box is checked is still white as well.

These are the changes I made:

For all the textboxes, I changed Enabled to True. And changed .Enabled =
False in the code. (See below)

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([Chk])")

' Set property
With objFcnd
.Enabled = False
End With

End If
Next

Set objFcnd = Nothing

End Sub

Any thoughts?

Thank you so much. I'm sorry this is such a pain in the neck.
Rachel

Ok you are doing this the opposite to what I have done. In my system, records
are ticked when they are 'active' so this is the opposite of what you want.
We just need to change a couple of things.

Set all your textbox properties to Enabled = Yes
Then change .Enabled = True to .Enabled = False in the Onload event
procedure.:

Steve

:

Hi Steve,

Again, thank you for everything. When I use the form now, all the textboxes
are not enabled so they are all grayed out. When I check the [ChkBox] field,
it appears that nothing happens. The only difference is that the row has a
checkmark in the [ChkBox] field. I was hoping for any field without a
checkmark in [ChkBox] will be white, once I put a checkmark on that record,
it turns gray.

I understand if you're too busy to continue helping me and you may not want
to spend any more time on this. However, if you wish to continue, here's
what I got...

All textboxes’ .Enabled property is set to 'No'

I have the following code in a standard module:

Public Function ToggleControl(ctl As Control) As Boolean

ToggleControl = ctl.Value

'Steve, when I typed ToggleControl = ctl. above, "Value" does not
show up as one of the options
' to choose from, but I typed it anyway

End Function

In the Form's OnLoad event, I have the following code:

Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then

'Delete conditions for current control
ctl.FormatConditions.Delete

Set objFcnd = ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([ChkBox])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

Thank you for everything.

Rachel

:

It goes to the Sub Function that I put in the OnLoad event and stops at
".ctl" in the following line:
Me.ctl.FormatConditions.Delete

Sorry, remove the 'Me.' to ctl.FormatConditions.Delete
Also remove 'Me.' to ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

Should start; Public Function ToggleControl(ctl As Control) As Boolean

Sorry about that. I actually copied the code from an in-progress backup and
I obviously hadn't finished with this at the time!

Steve

I did type the Public Function in myself and as I was typing, when I got to
the line:
ToggleControl = ctl.Value
the "value" part was not in the dropdown list that automatically pops up
after you type the ctl.

What did I do wrong? Any thoughts?

Again, thank you so much for your help.

Rachel


:

Ah! you have reminded me of something...
A while back I had the task of displaying 'inactive' records like you seem
to desire but in fact I found a way to disable all textboxes in those records
which added a couple of other advantages. I just found the backup and checked
the code I used - it is quite simple and I should have put this in my code
library DB!

First of all you can remove the extrat textboxes that we were using in the
previous posts.

Set all your textbox .Enabled property to 'No'

Put the following code in a standard module:
Public Function ToggleControl(ctl As Control)

ToggleControl = ctl.Value

End Function

Now in your Form's OnLoad event, put the following code:
Private Sub Form_Load()

Dim objFcnd As FormatCondition
Dim ctl As Control

For Each ctl in Me.Controls
If TypeOf ctl Is Textbox Then

' Delete conditions for current control
Me.ctl.FormatConditions.Delete

Set objFcnd = Me.ctl.FormatConditions.Add(acExpression, ,
"ToggleControl([myYesNoField])")

' Set property
With objFcnd
.Enabled = True
End With

End If
Next

Set objFcnd = Nothing

End Sub

That's it! Try it out, it may actually be closer to what you are looking
for...
Change 'myYesNoField' to the name of your checkbox.

Steve

:

It turns out that the reason the cell is white when I FIRST open the form is
because it is the active cell. After the form is open, when the "txt_AcctNo"
cell is selected on any record, the BackColor turns white.

:

Steve,

Thank you for your rapid responses. I think I followed your directions
correctly, please see below.

“txt_AcctNo†is my original textbox.
“txt_AcctNo1†is placed underneath “txt_AcctNoâ€.

BackStyle of “txt_AcctNo†and “txt_AcctNo1†are both set to:
Transparent.

ControlSource of “txt_AcctNo1†is set to:
=IIf(mycheckbox = True, "", True)

ControlSource of “txt_AcctNo†is set to:
AcctNo (field from the underlying table)

ForeColor of “txt_AcctNo1†is set to:
–2147483633
(This is the BackColor of the Detail Section)

FontName of “txt_AcctNo1†is set to:
System

FontSize of “txt_AcctNo1†is set to:
1

Format of “txt_AcctNo1†is set to:
;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ"

My Results:

When I open the form:
BackColor of “txt_AcctNo†is White (Whether “mycheckbox†is true or not)
(This should only be White if “mycheckbox†is false. If “mycheckbox†is
true, it should be the same color as the detail BackColor.)

If “mycheckbox†is not checked and I check it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(This is what I want it to be if “mycheckbox†is true)

If “mycheckbox†is checked and I uncheck it:
BackColor of “txt_AcctNo†is the same color as the BackColor of the detail
section
(The BackColor of “txt_AcctNo†should be White if “mycheckbox†is false)

It looks like I’m getting a lot closer.

Any other suggestions?

Thank you,
Rachel


:

I set the ControlSource for txt_AcctNo to: IIf(mycheckbox = True, "", True)
Needs to be: =IIf(mycheckbox = True, "", True) '(with the = sign)
Should be txt_AcctNo1

I set the ForeColor of txt_AcctNo to White
Should be txt_AcctNo1

Steve