By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,483 Members | 1,744 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,483 IT Pros & Developers. It's quick & easy.

User selectable color spec in data fields on printed reports

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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?
Nov 13 '05 #3

P: n/a
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?
Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
MLH
Wow! Great suggestion, mlh! Worked perfectly.
Thanks for taking the time to lend a hand. You're
a swell guy.
Nov 13 '05 #6

P: n/a
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?
Nov 13 '05 #7

P: n/a
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???
Nov 13 '05 #8

P: n/a
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?
Nov 13 '05 #9

P: n/a
MLH
Yes sir, I do.

Do you have any lines on your report?


Nov 13 '05 #10

P: n/a
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?

Nov 13 '05 #11

P: n/a
MLH
I don't think you understand.
Nov 13 '05 #12

P: n/a
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
Nov 13 '05 #13

P: n/a
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


Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.