Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Change text box background color depending on value in Label Reports
175 views
Skip to first unread message
harriss...@gmail.com
May 14, 2013, 4:53:23 AM5/14/13
to
Hi, hope someone can help. Struggling with this.
I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each label will have a specific field populated by a food name and I want to associate a background color for the text box with a specific food - therefore a print out may contain labels with a variety of food names each having a text box colored by a specific background color associated with that food name - and there may be a range of foods with each label print.
I would like this specific field to change background color according to the value of the field. Using Conditional formatting, I can enable this but only for three values. However I am using more than 3 values.
In other words, if the value of the text box is "Milk", the text box background should be yellow; if "Wheat", should be red; if "Egg", should be green; etc. Is this possible or have I been wasting my time!
Harris
I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each label will have a specific field populated by a food name and I want to associate a background color for the text box with a specific food - therefore a print out may contain labels with a variety of food names each having a text box colored by a specific background color associated with that food name - and there may be a range of foods with each label print.
I would like this specific field to change background color according to the value of the field. Using Conditional formatting, I can enable this but only for three values. However I am using more than 3 values.
In other words, if the value of the text box is "Milk", the text box background should be yellow; if "Wheat", should be red; if "Egg", should be green; etc. Is this possible or have I been wasting my time!
Harris
Phil
May 14, 2013, 5:09:39 AM5/14/13
to
On 14/05/2013 09:53:25, wrote:
> Hi, hope someone can help. Struggling with this.
>
> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> l abel will have a specific field populated by a food name and I want to
> Hi, hope someone can help. Struggling with this.
>
> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> asso ciate a background color for the text box with a specific food -
> therefore a print out may contain labels with a variety of food names each
> having a t ext box colored by a specific background color associated with
> that food na me - and there may be a range of foods with each label print.
>
> I would like this specific field to change background color according to
> th e value of the field. Using Conditional formatting, I can enable this
> I would like this specific field to change background color according to
> but o nly for three values. However I am using more than 3 values.
>
> In other words, if the value of the text box is "Milk", the text box
> backgr ound should be yellow; if "Wheat", should be red; if "Egg", should
> In other words, if the value of the text box is "Milk", the text box
> be green ; etc. Is this possible or have I been wasting my time!
>
> Harris
>
This may give you a clue.
>
> Harris
>
I believe there is a bug that the first 4 formats have to be set manually.
There is a limit of 50 formats
Private Sub Form_Current()
MealColour.BackStyle = 1 ' Normal
Call Call SetFormatCond(Me, "MealColour", "MealColour", True, "FuncSelected =
True")
End Sub
Sub SetFormatCond(Frm As Form, FldToColour As String, _
FldColourInfo Info As String, ChangeForeColour As Boolean, Optional Criteria
As String) ' Conditional formatting
' Frm is target form,
' FldToColour is the field to which to apply the conditional formatting
' FldColourInfo holds the RGB colour code (Long)
' ChangeForeColour, if true, tries to give a contrasting ForeColor
Dim FormatCond As FormatCondition
Dim i As Long
On Error GoTo SetFormatCond_Err
'Remove existing format conditions but due to an Access bug, if you have
' less than 4 formats, the FormatConditions.Add wont work.
' There is a limit of 3 formats otherwise
Do Until Frm(FldToColour).FormatConditions.Count = 4
Frm(FldToColour).FormatConditions(1).Delete
Loop
' Add the new formats, starting at number 4
With Frm.RecordsetClone
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
Set FormatCond = Frm(FldToColour).FormatConditions.Add _
(acExpression, , "[" & FldColourInfo & "] = " _
& Nz(Frm.RecordsetClone(FldColourInfo)))
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
Set FormatCond = Frm(FldToColour).FormatConditions.Add _
(acExpression, , "[" & FldColourInfo & "] = " _
& Nz(Frm.RecordsetClone(FldColourInfo)))
.MoveNext
Loop
End If
.Close
End With
i = 4 ' Ignore the first 4 formats
With Frm.RecordsetClone
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
If Not IsNull(Frm.RecordsetClone(FldToColour)) Then
Frm(FldToColour).FormatConditions(i).BackColor = _
Nz(Frm.RecordsetClone(FldColourInfo))
If If ChangeForeColour = True Then ' Contrasting ForColor
Frm(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Nz(Frm.RecordsetClone(FldColourInfo)))
End If
Else
Frm(FldToColour).FormatConditions(i).BackColor = _
Frm(FldToColour).FormatConditions(1).BackColor
If If ChangeForeColour = True Then ' Contrasting ForColor
Frm(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Frm(FldToColour).FormatConditions(1).BackColor)
rmatConditions(1).BackColor) End If
End If
i = i + 1
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
If Not IsNull(Frm.RecordsetClone(FldToColour)) Then
Frm(FldToColour).FormatConditions(i).BackColor = _
Nz(Frm.RecordsetClone(FldColourInfo))
If If ChangeForeColour = True Then ' Contrasting ForColor
Frm(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Nz(Frm.RecordsetClone(FldColourInfo)))
End If
Else
Frm(FldToColour).FormatConditions(i).BackColor = _
Frm(FldToColour).FormatConditions(1).BackColor
If If ChangeForeColour = True Then ' Contrasting ForColor
Frm(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Frm(FldToColour).FormatConditions(1).BackColor)
rmatConditions(1).BackColor) End If
End If
i = i + 1
.MoveNext
Loop
End If
.Close
End With
Exit Sub
SetFormatCond_Err:
MsgBox gBox "Set Format Conditions error Number: " & Err.Number & " " &
Err.Description
End Sub
Phil
harriss...@gmail.com
May 14, 2013, 7:32:00 AM5/14/13
to
Thanks a great ton in assisting. I am not a novice, but certainly not an advanced programmer, so if you could please guide me:
There is part of the text missing, so not sure what this (above) should actually be: "rmatConditions(1).BackColor)"
I tried "FormatConditions(1).BackColor)" but still appears to be an error.
Also, not sure of the syntax problem here: "MsgBox gBox "Set Format Conditions error Number: " & Err.Number & " " &"
Secondly, does this code change the Conditional Formatting to allow more or does one then have to vb code the choices?
Regards,
Harris
Phil
May 14, 2013, 8:54:05 AM5/14/13
to
On 14/05/2013 12:31:58, wrote:
> On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> therefor e a print out may contain labels with a variety of food names
> On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> each having a text box colored by a specific background color associated
> with that food name - and there may be a range of foods with each label
> > print.
>>
> > print.
> with that food name - and there may be a range of foods with each label
> > print.
>>
>>
> > print.
>>
> > print.
>> I would like this specific field to change background color according to
> print. the value of the field. Using Conditional formatting, I can enable
> > print.
>> I would like this specific field to change background color according to
> this but only for three values. However I am using more than 3 values.
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
> gre en; etc. Is this possible or have I been wasting my time!
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
>>
>>
>>
>> Harris
>
> Hi Phil,
>
> Thanks a great ton in assisting. I am not a novice, but certainly not an
> ad vanced programmer, so if you could please guide me:
>>
>>
>> Harris
>
> Hi Phil,
>
> Thanks a great ton in assisting. I am not a novice, but certainly not an
>
> There is part of the text missing, so not sure what this (above) should
> act ually be: "rmatConditions(1).BackColor)"
> There is part of the text missing, so not sure what this (above) should
> I tried "FormatConditions(1).BackColor)" but still appears to be an error.
>
> Also, not sure of the syntax problem here: "MsgBox gBox "Set Format
> Conditi ons error Number: " & Err.Number & " " &"
>
> Also, not sure of the syntax problem here: "MsgBox gBox "Set Format
>
> Secondly, does this code change the Conditional Formatting to allow more
> or does one then have to vb code the choices?
>
> Regards,
> Harris
>
Hi Harris
> Secondly, does this code change the Conditional Formatting to allow more
> or does one then have to vb code the choices?
>
> Regards,
> Harris
>
The newsreader seems to have corrupted the VBA
Will re-post it here, and send it to you direct.
You will need to change Frm to Rpt and Form to Report
Remove the lines
If If ChangeForeColour = True Then ' Contrasting ForColor
Frm(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Frm(FldToColour).FormatConditions(1).BackColor)
rmatConditions(1).BackColor) End If
That was a bit of an unsuccessful attempt to ensure there was a contrasting
Frm(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Frm(FldToColour).FormatConditions(1).BackColor)
rmatConditions(1).BackColor) End If
forecolour
MsgBox gBox "Set Format Conditions error Number: " & Err.Number & " " &
Err.Description
End Sub
Phil
harriss...@gmail.com
May 14, 2013, 10:07:05 AM5/14/13
to
Hi Phil,
Thanks for the further input. I have changed the Frm to Rpt and Form to Report.
The text now reads as below.
I am not sure how to impliment it. If I click on the Conditional Formatting button, I am still only offered the limited options.
Regards,
Harris
Private Sub Report_Current()
MealColour.BackStyle = 1 ' Normal
Call SetReportatCond(Me, "MealColour", "MealColour", True, "FuncSelected = True")
End Sub
Sub SetReportatCond(Rpt As Report, FldToColour As String, FldColourInfo As String, ChangeForeColour As Boolean, Optional Criteria As String) ' Conditional Reportatting
' Rpt is target Report,
' FldToColour is the field to which to apply the conditional Reportatting
Dim i As Long
On Error GoTo SetReportatCond_Err
'Remove existing Reportat conditions but due to an Access bug, if you have
' less than 4 Reportats, the ReportatConditions.Add wont work.
' There is a limit of 3 Reportats otherwise
Do Until Rpt(FldToColour).ReportatConditions.Count = 4
Rpt(FldToColour).ReportatConditions(1).Delete
Loop
' Add the new Reportats, starting at number 4
With Rpt.RecordsetClone
With Rpt.RecordsetClone
Rpt(FldToColour).ReportatConditions(i).BackColor = _
Nz(Rpt.RecordsetClone(FldColourInfo))
Rpt(FldToColour).ReportatConditions(i).BackColor = _
Nz(Rpt.RecordsetClone(FldColourInfo))
Contrast(Nz(Rpt.RecordsetClone(FldColourInfo)))
End If
Else
Rpt(FldToColour).ReportatConditions(i).BackColor = _
Rpt(FldToColour).ReportatConditions(1).BackColor
If ChangeForeColour = True Then ' Contrasting ForColor
Rpt(FldToColour).ReportatConditions(i).ForeColor = Contrast(Rpt(FldToColour).ReportatConditions(1).BackColor)
Thanks for the further input. I have changed the Frm to Rpt and Form to Report.
The text now reads as below.
I am not sure how to impliment it. If I click on the Conditional Formatting button, I am still only offered the limited options.
Regards,
Harris
Private Sub Report_Current()
MealColour.BackStyle = 1 ' Normal
Call SetReportatCond(Me, "MealColour", "MealColour", True, "FuncSelected = True")
End Sub
Sub SetReportatCond(Rpt As Report, FldToColour As String, FldColourInfo As String, ChangeForeColour As Boolean, Optional Criteria As String) ' Conditional Reportatting
' Rpt is target Report,
' FldToColour is the field to which to apply the conditional Reportatting
' FldColourInfo holds the RGB colour code (Long)
' ChangeForeColour, if true, tries to give a contrasting ForeColor
Dim ReportatCond As ReportatCondition
' ChangeForeColour, if true, tries to give a contrasting ForeColor
Dim i As Long
On Error GoTo SetReportatCond_Err
'Remove existing Reportat conditions but due to an Access bug, if you have
' less than 4 Reportats, the ReportatConditions.Add wont work.
' There is a limit of 3 Reportats otherwise
Do Until Rpt(FldToColour).ReportatConditions.Count = 4
Rpt(FldToColour).ReportatConditions(1).Delete
Loop
' Add the new Reportats, starting at number 4
With Rpt.RecordsetClone
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
Set ReportatCond = Rpt(FldToColour).ReportatConditions.Add _
.FindFirst Criteria
Do Until .NoMatch
(acExpression, , "[" & FldColourInfo & "] = " _
& Nz(Rpt.RecordsetClone(FldColourInfo)))
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
Set ReportatCond = Rpt(FldToColour).ReportatConditions.Add _
Loop
Else
.MoveFirst
Do Until .EOF
(acExpression, , "[" & FldColourInfo & "] = " _
& Nz(Rpt.RecordsetClone(FldColourInfo)))
.MoveNext
Loop
End If
.Close
End With
i = 4 ' Ignore the first 4 Reportats
Loop
End If
.Close
End With
With Rpt.RecordsetClone
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
If Not IsNull(Rpt.RecordsetClone(FldToColour)) Then
.FindFirst Criteria
Do Until .NoMatch
Rpt(FldToColour).ReportatConditions(i).BackColor = _
Nz(Rpt.RecordsetClone(FldColourInfo))
End If
i = i + 1
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
If Not IsNull(Rpt.RecordsetClone(FldToColour)) Then
i = i + 1
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
Rpt(FldToColour).ReportatConditions(i).BackColor = _
Nz(Rpt.RecordsetClone(FldColourInfo))
If ChangeForeColour = True Then ' Contrasting ForColor
Rpt(FldToColour).ReportatConditions(i).ForeColor = _
Contrast(Nz(Rpt.RecordsetClone(FldColourInfo)))
End If
Else
Rpt(FldToColour).ReportatConditions(i).BackColor = _
Rpt(FldToColour).ReportatConditions(1).BackColor
If ChangeForeColour = True Then ' Contrasting ForColor
Phil
May 14, 2013, 11:30:47 AM5/14/13
to
You need to run the routine on the Onformat of the section of the report. I
don't know if there will be a problem with 3 labels across though.
You need a control bound to the colour you want (probably with visible set to
'No'), as well as control that needs to change colour.
Remove this bit of code
If ChangeForeColour = True Then ' Contrasting ForColor
Rpt(FldToColour).ReportatConditions(i).ForeColor = _
Contrast(Nz(Rpt.RecordsetClone(FldColourInfo)))
End If
It was to try to ensure there was a contrasting forecolour (Red print on a
red background does'nt work)
Does it compile OK
Does your recordsource have a field that has the RGB colour held in it
You don't need to use the conditional format button other than to put 4
formats in (to overcome a bug in Access 2010, so it may also be in Access
2007.
You have substituted Report for Form and Rpt for Frm. That should only have
been done for whole words. So Format has become Reportat etc.
Phil
don't know if there will be a problem with 3 labels across though.
You need a control bound to the colour you want (probably with visible set to
'No'), as well as control that needs to change colour.
Remove this bit of code
If ChangeForeColour = True Then ' Contrasting ForColor
Rpt(FldToColour).ReportatConditions(i).ForeColor = _
Contrast(Nz(Rpt.RecordsetClone(FldColourInfo)))
End If
red background does'nt work)
Does it compile OK
Does your recordsource have a field that has the RGB colour held in it
You don't need to use the conditional format button other than to put 4
formats in (to overcome a bug in Access 2010, so it may also be in Access
2007.
You have substituted Report for Form and Rpt for Frm. That should only have
been done for whole words. So Format has become Reportat etc.
Phil
harriss...@gmail.com
May 14, 2013, 3:49:39 PM5/14/13
to
On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
I have corrected the code as below.
When I try to compile the code, I get the following error: "Compile error: Sub or Function not defined. The highlighted text is Contrast as in Rpt(FldToColour).FormatConditions(i).ForeColor = Contrast(Rpt(FldToColour).FormatConditions(1).BackColor)
Any idea?
Regards,
Harris
Private Sub Form_Current()
MealColour.BackStyle = 1 ' Normal
Call SetFormatCond(Me, "MealColour", "MealColour", True, "FuncSelected = True")
End Sub
Sub SetFormatCond(Rpt As Report, FldToColour As String, FldColourInfo, Info As String, ChangeForeColour As Boolean, Optional Criteria As String) ' Conditional formatting
' Frm is target form,
' FldToColour is the field to which to apply the conditional formatting
' FldColourInfo holds the RGB colour code (Long)
' ChangeForeColour, if true, tries to give a contrasting ForeColor
Dim FormatCond As FormatCondition
' ChangeForeColour, if true, tries to give a contrasting ForeColor
Dim i As Long
On Error GoTo SetFormatCond_Err
'Remove existing format conditions but due to an Access bug, if you have
' less than 4 formats, the FormatConditions.Add wont work.
' There is a limit of 3 formats otherwise
Do Until Rpt(FldToColour).FormatConditions.Count = 4
Rpt(FldToColour).FormatConditions(1).Delete
Loop
' Add the new formats, starting at number 4
With Rpt.RecordsetClone
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
Set FormatCond = Rpt(FldToColour).FormatConditions.Add _
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
(acExpression, , "[" & FldColourInfo & "] = " _
& Nz(Rpt.RecordsetClone(FldColourInfo)))
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
Set FormatCond = Rpt(FldToColour).FormatConditions.Add _
& Nz(Rpt.RecordsetClone(FldColourInfo)))
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
(acExpression, , "[" & FldColourInfo & "] = " _
& Nz(Rpt.RecordsetClone(FldColourInfo)))
.MoveNext
Loop
End If
.Close
End With
i = 4 ' Ignore the first 4 formats
& Nz(Rpt.RecordsetClone(FldColourInfo)))
.MoveNext
Loop
End If
.Close
End With
With Rpt.RecordsetClone
If Nz(Criteria) > "" Then ' Criteria passed
.FindFirst Criteria
Do Until .NoMatch
If Not IsNull(Rpt.RecordsetClone(FldToColour)) Then
If ChangeForeColour = True Then ' Contrasting ForColor
End If
End If
i = i + 1
End If
i = i + 1
.FindNext Criteria
Loop
Else
.MoveFirst
Do Until .EOF
If Not IsNull(Rpt.RecordsetClone(FldToColour)) Then
Rpt(FldToColour).FormatConditions(i).BackColor = _
Loop
Else
.MoveFirst
Do Until .EOF
If Not IsNull(Rpt.RecordsetClone(FldToColour)) Then
Nz(Rpt.RecordsetClone(FldColourInfo))
Else
Rpt(FldToColour).FormatConditions(i).BackColor = _
Rpt(FldToColour).FormatConditions(1).BackColor
If ChangeForeColour = True Then ' Contrasting ForColor
Rpt(FldToColour).FormatConditions(i).ForeColor = _
Contrast(Rpt(FldToColour).FormatConditions(1).BackColor)
End If
i = i + 1
.MoveNext
Loop
End If
.Close
End With
Exit Sub
SetFormatCond_Err:
i = i + 1
.MoveNext
Loop
End If
.Close
End With
Exit Sub
MsgBox "Set Format Conditions error Number: " & Err.Number
Err.Description
End Sub
Phil
May 14, 2013, 4:37:25 PM5/14/13
to
You need to remove any reference to Forecolour and the function
Contrast(.......)
I have not given you the code for this as it is flakey.
As I said it was written to provide a contrasting forcolour to the backcolour
that you require.
Is your table structure OK?
PhilOn 14/05/2013 20:49:37, wrote:
> On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> therefor e a print out may contain labels with a variety of food names
>>
> > print.
Contrast(.......)
I have not given you the code for this as it is flakey.
As I said it was written to provide a contrasting forcolour to the backcolour
that you require.
Is your table structure OK?
PhilOn 14/05/2013 20:49:37, wrote:
> On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> each having a text box colored by a specific background color associated
> with that food name - and there may be a range of foods with each label
> > print.
>>
> > print.
> with that food name - and there may be a range of foods with each label
> > print.
>>
>>
> > print.
>>
> > print.
>> I would like this specific field to change background color according to
> print. the value of the field. Using Conditional formatting, I can enable
> > print.
>> I would like this specific field to change background color according to
> this but only for three values. However I am using more than 3 values.
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
> gre en; etc. Is this possible or have I been wasting my time!
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
harriss...@gmail.com
May 15, 2013, 4:31:29 AM5/15/13
to
Hi Phil,
I found it would not compile, seems like I deleted an End If! What do you mean by the correct table structure?
I found it would not compile, seems like I deleted an End If! What do you mean by the correct table structure?
Phil
May 15, 2013, 6:03:10 AM5/15/13
to
TblProduces
ProduceID Autonumber Primary Key
ProduceName Text (Unique) e,g. Eggs, Milk
ProduceColour Long Integer e.g. 255 = Red, 4227327 = Orange
For the moment you need to get your code to compile and put in some values
for the colours in the table.
I would normally have a routines to open the colour picker box on double
clicking the ProduceCoulour field on the form that you use to enter the
produce
Phil
Harris Steinman
May 15, 2013, 1:32:42 PM5/15/13
to
Hi Phil,
Eventually got time to try it.
The code and all works perfectly Report view but not in in but not in Print Preview mode! I get a Run-time error '424': Object required. Debug indicates the problem to be MealColour.BackStyle = 1 in Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If I try to print without going to Print Preview, it does not with the dialog "a custom macro in this report has failed to run, and is preventing the report from rendering".
So close!! Any advice?
Thanks once again for your help.
Harris
Eventually got time to try it.
The code and all works perfectly Report view but not in in but not in Print Preview mode! I get a Run-time error '424': Object required. Debug indicates the problem to be MealColour.BackStyle = 1 in Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If I try to print without going to Print Preview, it does not with the dialog "a custom macro in this report has failed to run, and is preventing the report from rendering".
So close!! Any advice?
Thanks once again for your help.
Harris
Phil
May 15, 2013, 2:09:38 PM5/15/13
to
On 15/05/2013 18:32:41, Harris Steinman wrote:
> Hi Phil,
>
> Eventually got time to try it.
> The code and all works perfectly Report view but not in in but not in
> Print Preview mode! I get a Run-time error '424': Object required. Debug
> indicat es the problem to be MealColour.BackStyle = 1 in Private Sub
> Hi Phil,
>
> Eventually got time to try it.
> The code and all works perfectly Report view but not in in but not in
> Print Preview mode! I get a Run-time error '424': Object required. Debug
> Detail_Forma t(Cancel As Integer, FormatCount As Integer)
>
> If I try to print without going to Print Preview, it does not with the
> dial og "a custom macro in this report has failed to run, and is
> If I try to print without going to Print Preview, it does not with the
> preventing the report from rendering".
>
> So close!! Any advice?
> Thanks once again for your help.
> Harris
>
This is my last posting on this I'm afraid. Probably no computer access for 6
>
> So close!! Any advice?
> Thanks once again for your help.
> Harris
>
weeks
For MealColour.BackStyle you should have the name of the control that you are
changing the colour. I guess it will be called something like ProduceName.
So ProduceName.BackStyle = 1
This changes is from opaque to transparent or vice versa
Phil
Harris Steinman
May 15, 2013, 5:20:33 PM5/15/13
to
Hi Phil,
Thanks for all your input and patience. I have learnt a great deal from you, and greatly appreciate your help. I will work on your further guidance and trust that I will solve the final part of the puzzle. I will then post the final solution here for others with a similar situation and credit you with having provided the solution.
I accept that this had been a prolonged correspondence and I therefore sincerely thank you.
I assume you are traveling or going on vacation, so have a great time.
Harris
Thanks for all your input and patience. I have learnt a great deal from you, and greatly appreciate your help. I will work on your further guidance and trust that I will solve the final part of the puzzle. I will then post the final solution here for others with a similar situation and credit you with having provided the solution.
I accept that this had been a prolonged correspondence and I therefore sincerely thank you.
I assume you are traveling or going on vacation, so have a great time.
Harris
Phil
Aug 30, 2013, 5:51:58 AM8/30/13
to
You need to remove any reference to Forecolour and the function
Contrast(.......)
I have not given you the code for this as it is flakey.
As I said it was written to provide a contrasting forcolour to the backcolour
that you require.
Is your table structure OK?
PhilOn 14/05/2013 20:49:37, wrote:
Contrast(.......)
I have not given you the code for this as it is flakey.
As I said it was written to provide a contrasting forcolour to the backcolour
that you require.
Is your table structure OK?
PhilOn 14/05/2013 20:49:37, wrote:
> On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> therefor e a print out may contain labels with a variety of food names
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> each having a text box colored by a specific background color associated
> with that food name - and there may be a range of foods with each label
> > print.
>>
> > print.
> with that food name - and there may be a range of foods with each label
> > print.
>>
>>
> > print.
>>
> > print.
>> I would like this specific field to change background color according to
> print. the value of the field. Using Conditional formatting, I can enable
> > print.
>> I would like this specific field to change background color according to
> this but only for three values. However I am using more than 3 values.
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
> gre en; etc. Is this possible or have I been wasting my time!
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
>>
>>
>>
>> Harris
>
> Hi Phil,
>
> I have corrected the code as below.
> When I try to compile the code, I get the following error: "Compile error:
> Sub or Function not defined. The highlighted text is Contrast as in
>>
>>
>> Harris
>
> Hi Phil,
>
> I have corrected the code as below.
> When I try to compile the code, I get the following error: "Compile error:
> Sub or Function not defined. The highlighted text is Contrast as in
Phil
Aug 30, 2013, 5:52:04 AM8/30/13
to
You need to remove any reference to Forecolour and the function
Contrast(.......)
I have not given you the code for this as it is flakey.
As I said it was written to provide a contrasting forcolour to the backcolour
that you require.
Is your table structure OK?
PhilOn 14/05/2013 20:49:37, wrote:
Contrast(.......)
I have not given you the code for this as it is flakey.
As I said it was written to provide a contrasting forcolour to the backcolour
that you require.
Is your table structure OK?
PhilOn 14/05/2013 20:49:37, wrote:
> On Tuesday, 14 May 2013 10:53:23 UTC+2, harriss...@gmail.com wrote:
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> therefor e a print out may contain labels with a variety of food names
>> Hi, hope someone can help. Struggling with this.
>>
>>
>>
>> I have designed in MsAccess 2007 a Reports to print Labels 3 across. Each
> label will have a specific field populated by a food name and I want to as
> sociate a background color for the text box with a specific food -
> each having a text box colored by a specific background color associated
> with that food name - and there may be a range of foods with each label
> > print.
>>
> > print.
> with that food name - and there may be a range of foods with each label
> > print.
>>
>>
> > print.
>>
> > print.
>> I would like this specific field to change background color according to
> print. the value of the field. Using Conditional formatting, I can enable
> > print.
>> I would like this specific field to change background color according to
> this but only for three values. However I am using more than 3 values.
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
> gre en; etc. Is this possible or have I been wasting my time!
>>
>>
>>
>> In other words, if the value of the text box is "Milk", the text box back
> ground should be yellow; if "Wheat", should be red; if "Egg", should be
>>
>>
>>
>> Harris
>
> Hi Phil,
>
> I have corrected the code as below.
> When I try to compile the code, I get the following error: "Compile error:
> Sub or Function not defined. The highlighted text is Contrast as in
>>
>>
>> Harris
>
> Hi Phil,
>
> I have corrected the code as below.
> When I try to compile the code, I get the following error: "Compile error:
> Sub or Function not defined. The highlighted text is Contrast as in
Phil
Aug 30, 2013, 5:52:12 AM8/30/13
to
On 15/05/2013 09:31:32, wrote:
Phil
Aug 30, 2013, 5:52:29 AM8/30/13
to
On 15/05/2013 18:32:41, Harris Steinman wrote:
> Hi Phil,
>
> Eventually got time to try it.
> The code and all works perfectly Report view but not in in but not in
> Print Preview mode! I get a Run-time error '424': Object required. Debug
> indicat es the problem to be MealColour.BackStyle = 1 in Private Sub
>
> Eventually got time to try it.
> The code and all works perfectly Report view but not in in but not in
> Print Preview mode! I get a Run-time error '424': Object required. Debug
> Detail_Forma t(Cancel As Integer, FormatCount As Integer)
>
> If I try to print without going to Print Preview, it does not with the
> dial og "a custom macro in this report has failed to run, and is
> If I try to print without going to Print Preview, it does not with the
> preventing the report from rendering".
>
> So close!! Any advice?
> Thanks once again for your help.
> Harris
>
>
> So close!! Any advice?
> Thanks once again for your help.
> Harris
>
0 new messages