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! =)
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! =)