User selectable color spec in data fields on printed reports

[MLH]

I have a number of reports that are essentially black 'n
white prepared forms (picture an IRS form 1040). The
data fields are overlaid onto the report fields in the
correct positions and I would to empower the user to
use blue/red/black - maybe more - as the forecolor
property for the textbox controls holding data so that
the black & white forms will have all the printed data
shown in the desired color.

I put a textbox control on the report (rptENF260) named
DataColor. Then I tried setting a single datafield textbox's
ForeColor property to =Me!DataColor. Well, that didn't work.
So I tried =Forms!rptENF260!DataColor and just
Forms!rptENF260!DataColor. Access 97 HELP says
any expression evaluating to a numeric value can be
used. I can't seem to find one that works.

[MLH]

Further testing proves that I cannot even put this into the ForeColor
property setting: =16711680*10/10. I'm sure that's a numeric
expression.

[MLH]

I am thinking of setting the desired report textbox fields'
ForeSkin property to desired value from within code. I know
its a lot of typing but I cannot seem to get around the
property settings box limitation allowing ONLY a number
to be typed in the ForeColor property setting field.

Would someone like to contribute a code snippet that
lists only those textbox controls on a report having specified
ForeColor property setting value of 16711680?

[MLH]

>Would someone like to contribute a code snippet that

lists only those textbox controls on a report having specified
ForeColor property setting value of 16711680?

OK, I'll have a go at it...

Private Sub ListControlsBttn_Click()
'************************************************* ************************
' Purpose: Run the Controls Collection for user-specified form.
' The controls collection is a form's default collection.
'************************************************* ************************
On Error GoTo ListControlsBttn_ClickError
Dim ThisForm As String
ThisForm = Me.Name
Dim I As Integer, intHowmany As Integer, WhichRept As String

Msg = "Enter report name." ' Set prompt.
Title = "Report Name?" ' Set title.
Defvalue = "rptENF260" ' Set default return
value.
WhichRept = InputBox$(Msg, Title, Defvalue) ' Get user input.
If WhichRept = "" Then Exit Sub
For I = 0 To Reports(WhichRept).Count - 1
intHowmany = intHowmany + 1
If Reports(WhichRept)(I).ForeColor = 16711680 And
Reports(WhichRept)(I).ControlType = acTextBox Then
Debug.Print intHowmany; ") "; Reports(WhichRept)(I).Name,
Reports(WhichRept)(I).ControlType
End If
Next I

ExitButton11_Click:
Exit Sub

ListControlsBttn_ClickError:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
ListControlsBttn_Click, CBF on " & ThisForm & "."
k = CRLF & CRLF & "Error # " & Trim$(str$(Err)) & ": " & Quote &
Error$ & Quote
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume ExitButton11_Click

End Sub

Problem I'm having is the process stops prematurely - after listing
about 20 controls in the immediate window - complaining of an error
#438 ==> "Object doesn't support this property or method." Hmmmm???
Dunno what that's about. I'm kind-a-restricting the objects to only
textbox's (I thought). Wonder why one of the textboxes will not
support some property or method that the others seem to support
wholeheartedly?

[MLH]

Well then, try this...

Private Sub ListControlsBttn_Click()
'************************************************* ************************
' Purpose: Run the Controls Collection for user-specified form.
' The controls collection is a form's default collection.
'************************************************* ************************
On Error GoTo ListControlsBttn_ClickError
Dim ThisForm As String
ThisForm = Me.Name
Dim I As Integer, intHowmany As Integer, WhichRept As String

Msg = "Enter report name." ' Set prompt.
Title = "Report Name?" ' Set title.
Defvalue = "rptENF260" ' Set default return
value.
WhichRept = InputBox$(Msg, Title, Defvalue) ' Get user input.
If WhichRept = "" Then Exit Sub
For I = 0 To Reports(WhichRept).Count - 1
intHowmany = intHowmany + 1
If Reports(WhichRept)(I).ControlType = acTextBox Then
If Reports(WhichRept)(I).ForeColor = 16711680 Then
Debug.Print intHowmany; ") ";
Reports(WhichRept)(I).Name, Reports(WhichRept)(I).ControlType
End If
End If
Next I

ExitButton11_Click:
Exit Sub

ListControlsBttn_ClickError:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
ListControlsBttn_Click, CBF on " & ThisForm & "."
k = CRLF & CRLF & "Error # " & Trim$(str$(Err)) & ": " & Quote &
Error$ & Quote
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume ExitButton11_Click

End Sub

[MLH]

Wow! Great suggestion, mlh! Worked perfectly.
Thanks for taking the time to lend a hand. You're
a swell guy.

[rkc]

MLH wrote:

I am thinking of setting the desired report textbox fields'
ForeSkin property to desired value from within code.

What version of Access are you using?

[MLH]

ACCESS 97.
I am really hoping for an easier solution than what I'm having to
do in code (example below)

MLH wrote:

I am thinking of setting the desired report textbox fields'
ForeSkin property to desired value from within code.

What version of Access are you using?

Private Sub Report_Open(Cancel As Integer)
'************************************************* ******************
'************************************************* ******************
On Error GoTo Err_Report_Open
Dim ThisReport As String
ThisReport = Me.Name
GlobalString = ThisReport
ThisReportsDataColor = DLookup("[DataColor]", "tblReportColors",
"[ReportName]=GetGlobalString() AND [RptUsed]=True")

7 Me!VehicleMake.ForeColor = ThisReportsDataColor
9 Me!VehicleYear.ForeColor = ThisReportsDataColor
11 Me!MotorNum.ForeColor = ThisReportsDataColor
13 Me!SerialNum.ForeColor = ThisReportsDataColor
16 Me!TagNum.ForeColor = ThisReportsDataColor
18 Me!TagYear.ForeColor = ThisReportsDataColor
20 Me!TagState.ForeColor = ThisReportsDataColor
22 Me!TagCounty.ForeColor = ThisReportsDataColor
24 Me!VehicleValue.ForeColor = ThisReportsDataColor
26 Me!InspStickNum.ForeColor = ThisReportsDataColor
28 Me!InspStickState.ForeColor = ThisReportsDataColor
30 Me!InspStickExpDate.ForeColor = ThisReportsDataColor
32 Me!OtherReason.ForeColor = ThisReportsDataColor
33 Me!DateLeft.ForeColor = ThisReportsDataColor
34 Me!VehicleLocationName.ForeColor = ThisReportsDataColor
36 Me!TowCoAddr.ForeColor = ThisReportsDataColor
38 Me!LocPhone.ForeColor = ThisReportsDataColor
39 Me!OwnrEvidence.ForeColor = ThisReportsDataColor
40 Me!VehRemark.ForeColor = ThisReportsDataColor
47 Me!AuthorizedBy.ForeColor = ThisReportsDataColor
49 Me!AEAddress.ForeColor = ThisReportsDataColor
79 Me!Text160.ForeColor = ThisReportsDataColor
87 Me!MyTowCoName.ForeColor = ThisReportsDataColor
88 Me!MyTowCoAddr.ForeColor = ThisReportsDataColor
161 Me!SigningAuthority.ForeColor = ThisReportsDataColor
162 Me!Text248.ForeColor = ThisReportsDataColor

Exit_Report_Open:
Exit Sub

Err_Report_Open:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub Report_Open,
CBR on " & ThisReport & "."
k = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error, Exiting Report - " &
MyApp$ & ", rev. " & MY_VERSION$
Resume Exit_Report_Open

End Sub

Having to do that in the open event code of EVERY report is
a bear. Surely, I can handle this in the ForeColor property???

[rkc]

MLH wrote:

ACCESS 97.
I am really hoping for an easier solution than what I'm having to
do in code (example below)

Do you have any lines on your report?

[MLH]

Yes sir, I do.

Do you have any lines on your report?

[rkc]

MLH wrote:

Yes sir, I do.

Do you have any lines on your report?

Is a line a control?
Does a line have a forecolor property?

[MLH]

I don't think you understand.

[rkc]

MLH wrote:

I don't think you understand.

You can be confusing.
Enlighten me.

Private Sub SetAllTextBoxForeColor (clr As Long)

Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.ForeColor = clr
End If
Next

Set ctl = Nothing

End Sub

[MLH]

You sure hit the nail right on the head. Thx for bringing
me around. Six replies above this one, I showed you
my donkey approach (stubborn ass approach)...
7 Me!VehicleMake.ForeColor = ThisReportsDataColor
9 Me!VehicleYear.ForeColor = ThisReportsDataColor
11 Me!MotorNum.ForeColor = ThisReportsDataColor
13 Me!SerialNum.ForeColor = ThisReportsDataColor
16 Me!TagNum.ForeColor = ThisReportsDataColor
18 Me!TagYear.ForeColor = ThisReportsDataColor
20 Me!TagState.ForeColor = ThisReportsDataColor
22 Me!TagCounty.ForeColor = ThisReportsDataColor
24 Me!VehicleValue.ForeColor = ThisReportsDataColor
26 Me!InspStickNum.ForeColor = ThisReportsDataColor
28 Me!InspStickState.ForeColor = ThisReportsDataColor
30 Me!InspStickExpDate.ForeColor = ThisReportsDataColor
32 Me!OtherReason.ForeColor = ThisReportsDataColor
33 Me!DateLeft.ForeColor = ThisReportsDataColor
34 Me!VehicleLocationName.ForeColor = ThisReportsDataColor
36 Me!TowCoAddr.ForeColor = ThisReportsDataColor
38 Me!LocPhone.ForeColor = ThisReportsDataColor
39 Me!OwnrEvidence.ForeColor = ThisReportsDataColor
40 Me!VehRemark.ForeColor = ThisReportsDataColor
47 Me!AuthorizedBy.ForeColor = ThisReportsDataColor
49 Me!AEAddress.ForeColor = ThisReportsDataColor
79 Me!Text160.ForeColor = ThisReportsDataColor
87 Me!MyTowCoName.ForeColor = ThisReportsDataColor
88 Me!MyTowCoAddr.ForeColor = ThisReportsDataColor
161 Me!SigningAuthority.ForeColor = ThisReportsDataColor
162 Me!Text248.ForeColor = ThisReportsDataColor

I'm finding more and more often that when it seems to me that
someone is confused about something I've said - it turns out I'm
the one who is confused. You need no enlightening - you knew
precisely what I needed. The code below proves it. Thx, rkc
for having the patience to help me out here. You were trying to
teach me to fish instead of tossing me a cod - somehow, I just
didn't get it. Now that I do get it, I appreciate your effort.
On Sat, 05 Nov 2005 02:09:17 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

MLH wrote:

I don't think you understand.

You can be confusing.
Enlighten me.

Private Sub SetAllTextBoxForeColor (clr As Long)

Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.ForeColor = clr
End If
Next

Set ctl = Nothing

End Sub