Detail Section background colour

  • Thread starter Thread starter rawdstorage
  • Start date Start date
R

rawdstorage

I would like to use alternating section background (see more at
http://msdn.microsoft.com/en-us/library/bb243860.aspx,
http://msdn.microsoft.com/en-us/library/bb225493.aspx) but different
sections that share a specific control value (i.e. if the company name
is the same on several consecutive rows) should have the same
background colour. Thus I want to "cancel" some of the alternating
backgrounds out in the detail section, depending on my "company name"
condition. The company names themselves I am getting from an SQL
query.

My idea was to grab all company names (for instance 97 not distinct
company names) and putting them into an array with 97 positions. While
Access paints the reports I would check if the current company name is
same as the last one (having the first one on array index zero be the
first one gotten with the SQL squery). If the name is the same as the
last one, I would not change the detail background colour to an
alternate colour. Something like this:

for each value in the array loop
check
is the company name same as the last one?
if so
keep the previous background colour
if not
change to alternate colour
end check
end loop

If I am to give it a go with VBA I am speculating it would be
something like:

Public Sub MyReport_Load()
'build the array
'get the number of company names
Dim nrOfNames As Integer
nrOfNames = "SELECT Count(*) FROM CompanyTable" ' does this
assignment really work or should I make this a table and take the
value as CtrlSrc from there?

Dim namesArray As Array
namesArray = [nrOfNames] 'should this variable be declared
public somehow?
End Sub

Private Sub Detail_OnFormat | _Print | _Paint() 'which event is best
to use?
Dim currentName As String
currentName = (SELECT Top 1 CompanyName FROM CompanyTable)

Dim i As Integer
i = 0

For (i ; i < nrOfNames; i++)
If cboCompanyName.Value <> currentName
Me.Section("Detail").AlternateBackColor = RGB(205, 214,
255)
currentName = cboCompanyName.Value
End For
End Sub

Questions I have here are:
Which is the best event procedure to use?
How do I read an SQL query result into a variable?
I am working the assumption that each row in the report gets its own
index and that I can match that index, is this assumption correct? I
see that I haven't really matched my currently printed/painted row
index with my own index in any way though.... =/

I am also quite sure that loop syntax is off the wall!


I wrote all this code in here and thus I have never tested it, it's
just a sketch of how I would like for things to work.
Any help here is greatly appreciated! =)
 
Seems like the hard way to do this.

I would have a group for company name and change the alternating background
color of the detail section in the group header.

You can keep all your current sorting and just add one more level of sorting
and grouping as the last level based on company names, add a group header.

In the group header's format section set the detail section's background
color. If you do not want to see the group header in your report, you can
either set the group headers's height to zero or set the group header's
visible property to No (false).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I would like to use alternating section background (see more at
http://msdn.microsoft.com/en-us/library/bb243860.aspx,
http://msdn.microsoft.com/en-us/library/bb225493.aspx) but different
sections that share a specific control value (i.e. if the company name
is the same on several consecutive rows) should have the same
background colour. Thus I want to "cancel" some of the alternating
backgrounds out in the detail section, depending on my "company name"
condition. The company names themselves I am getting from an SQL
query.

My idea was to grab all company names (for instance 97 not distinct
company names) and putting them into an array with 97 positions. While
Access paints the reports I would check if the current company name is
same as the last one (having the first one on array index zero be the
first one gotten with the SQL squery). If the name is the same as the
last one, I would not change the detail background colour to an
alternate colour. Something like this:

for each value in the array loop
check
is the company name same as the last one?
if so
keep the previous background colour
if not
change to alternate colour
end check
end loop

If I am to give it a go with VBA I am speculating it would be
something like:

Public Sub MyReport_Load()
'build the array
'get the number of company names
Dim nrOfNames As Integer
nrOfNames = "SELECT Count(*) FROM CompanyTable" ' does this
assignment really work or should I make this a table and take the
value as CtrlSrc from there?

Dim namesArray As Array
namesArray = [nrOfNames] 'should this variable be declared
public somehow?
End Sub

Private Sub Detail_OnFormat | _Print | _Paint() 'which event is best
to use?
Dim currentName As String
currentName = (SELECT Top 1 CompanyName FROM CompanyTable)

Dim i As Integer
i = 0

For (i ; i < nrOfNames; i++)
If cboCompanyName.Value <> currentName
Me.Section("Detail").AlternateBackColor = RGB(205, 214,
255)
currentName = cboCompanyName.Value
End For
End Sub

Questions I have here are:
Which is the best event procedure to use?
How do I read an SQL query result into a variable?
I am working the assumption that each row in the report gets its own
index and that I can match that index, is this assumption correct? I
see that I haven't really matched my currently printed/painted row
index with my own index in any way though.... =/

I am also quite sure that loop syntax is off the wall!


I wrote all this code in here and thus I have never tested it, it's
just a sketch of how I would like for things to work.
Any help here is greatly appreciated! =)
Click to expand...
 
Thank you for your quick reply!

Questions I have then are:
What is a group?
How can I change a group header?
Is there sorting to be done in the group?

One thing I have not mentioned (to keep the inital problem formulation
simpler) is that I want a line at the end of the sections containing
the same company name, to make the separation between companies even
clearer.
Is it possible to include this horisontal line within the group as
well?

Thanks in advance!


Seems like the hard way to do this.

I would have a group for company name and change the alternating background
color of the detail section in the group header.

You can keep all your current sorting and just add one more level of sorting
and grouping as the last level based on company names, add a group header..

In the group header's format section set the detail section's background
color.  If you do not want to see the group header in your report, you can
either set the group headers's height to zero or set the group header's
visible property to No (false).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County



[email protected] said:
I would like to use alternating section background (see more at
http://msdn.microsoft.com/en-us/library/bb243860.aspx,
http://msdn.microsoft.com/en-us/library/bb225493.aspx) but different
sections that share a specific control value (i.e. if the company name
is the same on several consecutive rows) should have the same
background colour. Thus I want to "cancel" some of the alternating
backgrounds out in the detail section, depending on my "company name"
condition. The company names themselves I am getting from an SQL
query.
Click to expand...
My idea was to grab all company names (for instance 97 not distinct
company names) and putting them into an array with 97 positions. While
Access paints the reports I would check if the current company name is
same as the last one (having the first one on array index zero be the
first one gotten with the SQL squery). If the name is the same as the
last one, I would not change the detail background colour to an
alternate colour. Something like this:
Click to expand...
for each value in the array loop
   check
      is the company name same as the last one?
      if so
         keep the previous background colour
      if not
         change to alternate colour
   end check
end loop
Click to expand...
If I am to give it a go with VBA I am speculating it would be
something like:
Click to expand...
Public Sub MyReport_Load()
    'build the array
    'get the number of company names
    Dim nrOfNames As Integer
    nrOfNames = "SELECT Count(*) FROM CompanyTable" ' does this
assignment really work or should I make this a table and take the
value as CtrlSrc from there?
Click to expand...
   Dim namesArray As Array
   namesArray = [nrOfNames]    'should this variable be declared
public somehow?
End Sub
Click to expand...
Private Sub Detail_OnFormat | _Print |  _Paint()  'which event is best
to use?
    Dim currentName As String
    currentName = (SELECT Top 1 CompanyName FROM CompanyTable)
Click to expand...
    Dim i As Integer
    i = 0
Click to expand...
    For (i ; i < nrOfNames; i++)
         If cboCompanyName.Value <> currentName
            Me.Section("Detail").AlternateBackColor = RGB(205, 214,
255)
            currentName = cboCompanyName.Value
    End For
End Sub
Click to expand...
Questions I have here are:
Which is the best event procedure to use?
How do I read an SQL query result into a variable?
I am working the assumption that each row in the report gets its own
index and that I can match that index, is this assumption correct? I
see that I haven't really matched my currently printed/painted row
index with my own index in any way though....  =/
Click to expand...
I am also quite sure that loop syntax is off the wall!
Click to expand...
I wrote all this code in here and thus I have never tested it, it's
just a sketch of how I would like for things to work.
Any help here is greatly appreciated! =)- Dölj citerad text -
Click to expand...

- Visa citerad text -
Click to expand...
 
In report's design view
Select View: Sorting and Grouping from the menu
If your report is sorted you should see entries in the dialog
In the first blank row, select the Company field under Field/Expression column
Set Group Header to YES
Set Group Footer to YES
Close the Sorting and Grouping window.
There should now be two new areas in your report design

-- Use the line tool to draw a line in the group footer
-- adjust the height of the group footer to your desired height

-- Click on the Group header
-- The properties dialog is not showing, right-click and select properties
-- Click on the event tab
-- Click in On Format and type
[Event Procedure]
-- Click on the three dots at the end and enter the vba code to change the
background color of the detail section. Remove the code that was doing this
in the detail section. (You can probably cut and paste the code from the
detail section procedure to the Group header section)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you for your quick reply!

Questions I have then are:
What is a group?
How can I change a group header?
Is there sorting to be done in the group?

One thing I have not mentioned (to keep the inital problem formulation
simpler) is that I want a line at the end of the sections containing
the same company name, to make the separation between companies even
clearer.
Is it possible to include this horisontal line within the group as
well?

Thanks in advance!


Seems like the hard way to do this.

I would have a group for company name and change the alternating background
color of the detail section in the group header.

You can keep all your current sorting and just add one more level of sorting
and grouping as the last level based on company names, add a group header.

In the group header's format section set the detail section's background
color. If you do not want to see the group header in your report, you can
either set the group headers's height to zero or set the group header's
visible property to No (false).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County



[email protected] said:
I would like to use alternating section background (see more at
http://msdn.microsoft.com/en-us/library/bb243860.aspx,
http://msdn.microsoft.com/en-us/library/bb225493.aspx) but different
sections that share a specific control value (i.e. if the company name
is the same on several consecutive rows) should have the same
background colour. Thus I want to "cancel" some of the alternating
backgrounds out in the detail section, depending on my "company name"
condition. The company names themselves I am getting from an SQL
query.
My idea was to grab all company names (for instance 97 not distinct
company names) and putting them into an array with 97 positions. While
Access paints the reports I would check if the current company name is
same as the last one (having the first one on array index zero be the
first one gotten with the SQL squery). If the name is the same as the
last one, I would not change the detail background colour to an
alternate colour. Something like this:
for each value in the array loop
check
is the company name same as the last one?
if so
keep the previous background colour
if not
change to alternate colour
end check
end loop
If I am to give it a go with VBA I am speculating it would be
something like:
Public Sub MyReport_Load()
'build the array
'get the number of company names
Dim nrOfNames As Integer
nrOfNames = "SELECT Count(*) FROM CompanyTable" ' does this
assignment really work or should I make this a table and take the
value as CtrlSrc from there?
Dim namesArray As Array
namesArray = [nrOfNames] 'should this variable be declared
public somehow?
End Sub
Private Sub Detail_OnFormat | _Print | _Paint() 'which event is best
to use?
Dim currentName As String
currentName = (SELECT Top 1 CompanyName FROM CompanyTable)
Dim i As Integer
i = 0
For (i ; i < nrOfNames; i++)
If cboCompanyName.Value <> currentName
Me.Section("Detail").AlternateBackColor = RGB(205, 214,
255)
currentName = cboCompanyName.Value
End For
End Sub
Questions I have here are:
Which is the best event procedure to use?
How do I read an SQL query result into a variable?
I am working the assumption that each row in the report gets its own
index and that I can match that index, is this assumption correct? I
see that I haven't really matched my currently printed/painted row
index with my own index in any way though.... =/
I am also quite sure that loop syntax is off the wall!
I wrote all this code in here and thus I have never tested it, it's
just a sketch of how I would like for things to work.
Any help here is greatly appreciated! =)- Dölj citerad text -
Click to expand...
- Visa citerad text -
Click to expand...
Click to expand...
 
I managed to not only create one but several groups with different
formatting shortly after the previous response I got. I did not make
it look like I first was hoping for, but it
still looks good. Instead I added a second group with a formatting of
its own and the layout still manages to convey a sense for where the
different sections and sub-sections have their boundaries.

I will have a look at this new response as well.

Thanks for the help!


In report's design view
Select View: Sorting and Grouping from the menu
If your report is sorted you should see entries in the dialog
In the first blank row, select the Company field under Field/Expression column
Set Group Header to YES
Set Group Footer to YES
Close the Sorting and Grouping window.
There should now be two new areas in your report design

-- Use the line tool to draw a line in the group footer
-- adjust the height of the group footer to your desired height

-- Click on the Group header
-- The properties dialog is not showing, right-click and select properties
-- Click on the event tab
-- Click in On Format and type
    [Event Procedure]
-- Click on the three dots at the end and enter the vba code to change the
background color of the detail section.  Remove the code that was doingthis
in the detail section. (You can probably cut and paste the code from the
detail section procedure to the Group header section)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County



[email protected] said:
Thank you for your quick reply!
Click to expand...
Questions I have then are:
What is a group?
How can I change a group header?
Is there sorting to be done in the group?
Click to expand...
One thing I have not mentioned (to keep the inital problem formulation
simpler) is that I want a line at the end of the sections containing
the same company name, to make the separation between companies even
clearer.
Is it possible to include this horisontal line within the group as
well?
Click to expand...
Thanks in advance!
Click to expand...
Seems like the hard way to do this.
I would have a group for company name and change the alternating background
color of the detail section in the group header.
You can keep all your current sorting and just add one more level of sorting
and grouping as the last level based on company names, add a group header.
In the group header's format section set the detail section's background
color.  If you do not want to see the group header in your report, you can
either set the group headers's height to zero or set the group header's
visible property to No (false).
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
I would like to use alternating section background (see more at
http://msdn.microsoft.com/en-us/library/bb243860.aspx,
http://msdn.microsoft.com/en-us/library/bb225493.aspx) but different
sections that share a specific control value (i.e. if the company name
is the same on several consecutive rows) should have the same
background colour. Thus I want to "cancel" some of the alternating
backgrounds out in the detail section, depending on my "company name"
condition. The company names themselves I am getting from an SQL
query.
My idea was to grab all company names (for instance 97 not distinct
company names) and putting them into an array with 97 positions. While
Access paints the reports I would check if the current company name is
same as the last one (having the first one on array index zero be the
first one gotten with the SQL squery). If the name is the same as the
last one, I would not change the detail background colour to an
alternate colour. Something like this:
for each value in the array loop
   check
      is the company name same as the last one?
      if so
         keep the previous background colour
      if not
         change to alternate colour
   end check
end loop
If I am to give it a go with VBA I am speculating it would be
something like:
Public Sub MyReport_Load()
    'build the array
    'get the number of company names
    Dim nrOfNames As Integer
    nrOfNames = "SELECT Count(*) FROM CompanyTable" ' does this
assignment really work or should I make this a table and take the
value as CtrlSrc from there?
   Dim namesArray As Array
   namesArray = [nrOfNames]    'should this variable be declared
public somehow?
End Sub
Private Sub Detail_OnFormat | _Print |  _Paint()  'which event isbest
to use?
    Dim currentName As String
    currentName = (SELECT Top 1 CompanyName FROM CompanyTable)
    Dim i As Integer
    i = 0
    For (i ; i < nrOfNames; i++)
         If cboCompanyName.Value <> currentName
            Me.Section("Detail").AlternateBackColor = RGB(205, 214,
255)
            currentName = cboCompanyName.Value
    End For
End Sub
Questions I have here are:
Which is the best event procedure to use?
How do I read an SQL query result into a variable?
I am working the assumption that each row in the report gets its own
index and that I can match that index, is this assumption correct? I
see that I haven't really matched my currently printed/painted row
index with my own index in any way though....  =/
I am also quite sure that loop syntax is off the wall!
I wrote all this code in here and thus I have never tested it, it's
just a sketch of how I would like for things to work.
Any help here is greatly appreciated! =)- Dölj citerad text -
- Visa citerad text -- Dölj citerad text -
Click to expand...
Click to expand...

- Visa citerad text -
Click to expand...
 
Back
Top