programmatically add controls to report

  • Thread starter Thread starter G
  • Start date Start date
G

G

I am having difficulty adding controls to a report. I am
able to create the report as follows:
Set rpt = CreateReport
With rpt
.RecordSource = "qryCalculateMktValue"
.Caption = "LNW"
.Visible = True
End With

' Restore new report.
DoCmd.Restore
But the following does not work:
with CreateReportControl(reportname as string, controltype
as integer)
It not only does nothing, it will not let me set anything,
like .name
what am I doing wrong?

thanx
G
 
G said:
I am having difficulty adding controls to a report. I am
able to create the report as follows:
Set rpt = CreateReport
With rpt
.RecordSource = "qryCalculateMktValue"
.Caption = "LNW"
.Visible = True
End With

' Restore new report.
DoCmd.Restore
But the following does not work:
with CreateReportControl(reportname as string, controltype
as integer)
It not only does nothing, it will not let me set anything,
like .name
what am I doing wrong?
Click to expand...


It sounds like you're due for my Create... lecture ;-)

From the tone of your question, I get the impression that
you are not using that code to build yourself a design aid
kind of wizard. If you're thinking of doing this kind of
thing at runtime, then you're on the wrong track.

For several very good reasons, it's a really bad idea to be
creating heavy duty objects such as forms or reports on the
fly. Instead, you should have a prebuilt report that can
take care of your various situations and then set control
and/or grouplevel properties to adjust as needed at runtime
(usually in the report's Open event).

Besides, without seeing the actual line of code that you're
trying to use, no one's going to be able to figure out
what's wrong with it.
 
Thank you, Marsh.
I have a pre-built report that runs off a query.
I have purchased developer books and that is where this
code comes from. However, I am still experimenting with it.
I have a ways to go, I know *S*

The problem I have is this:
1. The report will be run for different customers each time.
2. Each customer has several different account names
(column headings)
3. The source is a crosstab query.

Do you have a better solution for this? These will be
showing different accounts and their data, kinda like it is
done for different months.

Yes, I had it in the wrong area......

Once again, thank you.

G
 
Well, for a simple, limited list of values such as months,
you should use the crosstab query's ColumnHeadings property
to pre specify the months. this way the report can bind
controls to the columns without worrying about whether a
column won't be there.

For a potentially random set of values, that won't work.
What I've done in the very limited cases like this is to use
a bunch of labels and text boxes named something like lbl1,
lbl2, ... and txt1, txt2, .... Then I use code in the
report's Open event to set the label's Caption and the text
box's ControlSource. Here's some example code:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim k As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.RecordSource)

With rs.Fields
For k = 2 To .Count - 1
Me("lbl" & k - 1).Caption = .Item(k).Name
Me("txt" & k - 1).ControlSource = .Item(k).Name
Next k
End With

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

I don't claim to be particularly adept with crosstab reports
and the above may not be an especially good way to do this,
but, as long as the query isn't a monster, it has worked ok
for me.

Duane Hookom is a guru of crosstab reports and you may want
to check his more general approaches at:
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Cross Tab'
 
Thank you very much Marsh. Your assistance is greatly
appreciated.
The only concern I have with this is making sure that if
there are only 3 fields instead of 7, that the columns are
centered on the page, instead of being grouped to the
left, leaving blank space on the right side of the report.

Once again, thank you for your help.

G


-----Original Message-----
Well, for a simple, limited list of values such as months,
you should use the crosstab query's ColumnHeadings property
to pre specify the months. this way the report can bind
controls to the columns without worrying about whether a
column won't be there.

For a potentially random set of values, that won't work.
What I've done in the very limited cases like this is to use
a bunch of labels and text boxes named something like lbl1,
lbl2, ... and txt1, txt2, .... Then I use code in the
report's Open event to set the label's Caption and the text
box's ControlSource. Here's some example code:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim k As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.RecordSource)

With rs.Fields
For k = 2 To .Count - 1
Me("lbl" & k - 1).Caption = .Item(k).Name
Me("txt" & k - 1).ControlSource = .Item(k).Name
Next k
End With

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

I don't claim to be particularly adept with crosstab reports
and the above may not be an especially good way to do this,
but, as long as the query isn't a monster, it has worked ok
for me.

Duane Hookom is a guru of crosstab reports and you may want
to check his more general approaches at:
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
http://www.rogersaccesslibrary.com/Otherdownload.asp? SampleName='Cross%20Tab'
--
Marsh
MVP [MS Access]


Thank you, Marsh.
I have a pre-built report that runs off a query.
I have purchased developer books and that is where this
code comes from. However, I am still experimenting with it.
I have a ways to go, I know *S*

The problem I have is this:
1. The report will be run for different customers each time.
2. Each customer has several different account names
(column headings)
3. The source is a crosstab query.

Do you have a better solution for this? These will be
showing different accounts and their data, kinda like it is
done for different months.
Click to expand...
.
Click to expand...
 
That code was just a skeleton to gice you the idea. You
will want to set other things as well (e.g. make the needed
controls visible). To center the controls you need to
calculate the total width of the visible controls, use that
to calculate the available space and then determine the Left
property of each label and text box.

If you have totals in the report footer section, you'll need
to set those ControlSource expression as well. This kind
of thing is more tedious than difficult (once you understand
it ;-)
--
Marsh
MVP [MS Access]


Thank you very much Marsh. Your assistance is greatly
appreciated.
The only concern I have with this is making sure that if
there are only 3 fields instead of 7, that the columns are
centered on the page, instead of being grouped to the
left, leaving blank space on the right side of the report.


-----Original Message-----
Well, for a simple, limited list of values such as months,
you should use the crosstab query's ColumnHeadings property
to pre specify the months. this way the report can bind
controls to the columns without worrying about whether a
column won't be there.

For a potentially random set of values, that won't work.
What I've done in the very limited cases like this is to use
a bunch of labels and text boxes named something like lbl1,
lbl2, ... and txt1, txt2, .... Then I use code in the
report's Open event to set the label's Caption and the text
box's ControlSource. Here's some example code:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim k As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.RecordSource)

With rs.Fields
For k = 2 To .Count - 1
Me("lbl" & k - 1).Caption = .Item(k).Name
Me("txt" & k - 1).ControlSource = .Item(k).Name
Next k
End With

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

I don't claim to be particularly adept with crosstab reports
and the above may not be an especially good way to do this,
but, as long as the query isn't a monster, it has worked ok
for me.

Duane Hookom is a guru of crosstab reports and you may want
to check his more general approaches at:
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
http://www.rogersaccesslibrary.com/Otherdownload.asp? SampleName='Cross%20Tab'
--
Marsh
MVP [MS Access]


Thank you, Marsh.
I have a pre-built report that runs off a query.
I have purchased developer books and that is where this
code comes from. However, I am still experimenting with it.
I have a ways to go, I know *S*

The problem I have is this:
1. The report will be run for different customers each time.
2. Each customer has several different account names
(column headings)
3. The source is a crosstab query.

Do you have a better solution for this? These will be
showing different accounts and their data, kinda like it is
done for different months.


-----Original Message-----
G wrote:

I am having difficulty adding controls to a report. I am
able to create the report as follows:
Set rpt = CreateReport
With rpt
.RecordSource = "qryCalculateMktValue"
.Caption = "LNW"
.Visible = True
End With

' Restore new report.
DoCmd.Restore
But the following does not work:
with CreateReportControl(reportname as string, controltype
as integer)
It not only does nothing, it will not let me set anything,
like .name
what am I doing wrong?


It sounds like you're due for my Create... lecture ;-)

From the tone of your question, I get the impression that
you are not using that code to build yourself a design aid
kind of wizard. If you're thinking of doing this kind of
thing at runtime, then you're on the wrong track.

For several very good reasons, it's a really bad idea to be
creating heavy duty objects such as forms or reports on the
fly. Instead, you should have a prebuilt report that can
take care of your various situations and then set control
and/or grouplevel properties to adjust as needed at runtime
(usually in the report's Open event).

Besides, without seeing the actual line of code that you're
trying to use, no one's going to be able to figure out
what's wrong with it.
Click to expand...
.
Click to expand...
Click to expand...
 
Duane,
I looked at that, and also went to my ACCESS 97 book and
my ACCESS 2002 Desktop Developer's Handbook. I ended up
using the coding from the "Help" in the Access database I
am working in. That took me to the Solutions database.
I am getting a really simple error "type mismatch". I know
what that means, but don't see it in this code snippet.
Could you look at it and perhaps be able to see where I've
gone astray? The only thing I changed was the column
numbers, and the text fields names to be close to the
sample.
The line that hangs is "lngRowTotal = lngRowTotal + Me
("txtDet" + Format$(intX))
"
I believe that this should be adding making lngRowTotal =
0 + whatever value Me(txtDet(somenumber) is.

Thank you in advance.

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize lngRowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 3 To intColumnCount
' Starting at column 2 (first text box with
crosstab value),
' compute total for current row in detail
section.
lngRowTotal = lngRowTotal + Me("txtDet" +
Format$(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal
(intX) + _
Me("txtDet" + Format$(intX))
Next intX

' Place row total in text box in detail section.
Me("txtDet" + Format$(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If

End Sub


G
 
Thank you so very much Marshall. Your assistance is
greatly appreciated!

G

-----Original Message-----
That code was just a skeleton to gice you the idea. You
will want to set other things as well (e.g. make the needed
controls visible). To center the controls you need to
calculate the total width of the visible controls, use that
to calculate the available space and then determine the Left
property of each label and text box.

If you have totals in the report footer section, you'll need
to set those ControlSource expression as well. This kind
of thing is more tedious than difficult (once you understand
it ;-)
--
Marsh
MVP [MS Access]


Thank you very much Marsh. Your assistance is greatly
appreciated.
The only concern I have with this is making sure that if
there are only 3 fields instead of 7, that the columns are
centered on the page, instead of being grouped to the
left, leaving blank space on the right side of the report.


-----Original Message-----
Well, for a simple, limited list of values such as months,
you should use the crosstab query's ColumnHeadings property
to pre specify the months. this way the report can bind
controls to the columns without worrying about whether a
column won't be there.

For a potentially random set of values, that won't work.
What I've done in the very limited cases like this is
Click to expand...
to
use
a bunch of labels and text boxes named something like lbl1,
lbl2, ... and txt1, txt2, .... Then I use code in the
report's Open event to set the label's Caption and the text
box's ControlSource. Here's some example code:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim k As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.RecordSource)

With rs.Fields
For k = 2 To .Count - 1
Me("lbl" & k - 1).Caption = .Item(k).Name
Me("txt" & k - 1).ControlSource = .Item(k).Name
Next k
End With

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

I don't claim to be particularly adept with crosstab reports
and the above may not be an especially good way to do this,
but, as long as the query isn't a monster, it has
Click to expand...
worked
ok
for me.

Duane Hookom is a guru of crosstab reports and you may want
to check his more general approaches at:
http://www.invisibleinc.com/divFiles.cfm?divDivID=4
http://www.rogersaccesslibrary.com/Otherdownload.asp? SampleName='Cross%20Tab'
--
Marsh
MVP [MS Access]



G wrote:

Thank you, Marsh.
I have a pre-built report that runs off a query.
I have purchased developer books and that is where this
code comes from. However, I am still experimenting
Click to expand...
with
it.
I have a ways to go, I know *S*

The problem I have is this:
1. The report will be run for different customers each time.
2. Each customer has several different account names
(column headings)
3. The source is a crosstab query.

Do you have a better solution for this? These will be
showing different accounts and their data, kinda like
Click to expand...
it
is
done for different months.


-----Original Message-----
G wrote:

I am having difficulty adding controls to a report.
Click to expand...
I
am
able to create the report as follows:
Set rpt = CreateReport
With rpt
.RecordSource = "qryCalculateMktValue"
.Caption = "LNW"
.Visible = True
End With

' Restore new report.
DoCmd.Restore
But the following does not work:
with CreateReportControl(reportname as string, controltype
as integer)
It not only does nothing, it will not let me set anything,
like .name
what am I doing wrong?


It sounds like you're due for my Create... lecture ;- )

From the tone of your question, I get the impression that
you are not using that code to build yourself a
Click to expand...
design
aid
kind of wizard. If you're thinking of doing this
Click to expand...
kind
of
thing at runtime, then you're on the wrong track.

For several very good reasons, it's a really bad idea to be
creating heavy duty objects such as forms or reports
Click to expand...
on
the
fly. Instead, you should have a prebuilt report that can
take care of your various situations and then set control
and/or grouplevel properties to adjust as needed at runtime
(usually in the report's Open event).

Besides, without seeing the actual line of code that you're
trying to use, no one's going to be able to figure out
what's wrong with it.
.
Click to expand...
Click to expand...

.
Click to expand...
 
I don't care much for the Solutions solution. It is slow, inflexible and
prone to errors. My only thought on your code that breaks is the field might
be Null which would require
lngRowTotal = lngRowTotal + Nz(Me("txtDet" + Format$(intX)),0)
 
Why would you need 2 column, 3 column,.. reports for the same data? Do you
have different widths of paper?
 
the user has requested that this report have crosstab
headings (i.e. different account names) with totals and
percentages of total. She has also requested that
according to how many columns there would be, that the
font size and layout be adjusted to be presentable (ie
larger fonts). Now she has changed the max of entities
from 7 to whatever and would also like the page and paper
orientation changed depending on the # of entities.

Client A might have 2 entity names, and Client B might
have 8 entity names.
What I was thinking is that since they asked me to NOT do
much coding, if it would be easier to put up a standard
report so that if the user clicked on Client A, the report
for clients with 2 entities would come up. and the same
for Client B, the report for clients with 8 entities would
come up.

But now I have gone back to the original where it makes a
report based on the crosstab, programmatically adding
fields to the report,

Any advice on this would be greatly appreciated.

Thank you in advance for all your help. Sorry to be such a
problem with this.

G
-----Original Message-----
Why would you need 2 column, 3 column,.. reports for the same data? Do you
have different widths of paper?

--
Duane Hookom
MS Access MVP
--

G said:
Duane,
I have more questions regarding this issue. Should I
repost so there is a newer date with the newer issues?

What I need to know, is this the only way to accomplish
changing the report based on the column headers?
Would making a separate report for each column header
count work? ie report#2 = 2 columns of data, report#3 = 3
columns of data.

Where I am working at, they do not want much coding done.
But I don't see any way around it.

G
-----Original Message-----
There is a demo for creating crosstab reports with dynamic column headings
at http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution will
create unique column headings based on the customer
Click to expand...
since
each customer
could have a unique set of account names.

--
Duane Hookom
MS Access MVP
--

Thank you, Marsh.
I have a pre-built report that runs off a query.
I have purchased developer books and that is where this
code comes from. However, I am still experimenting
Click to expand...
with
it.
I have a ways to go, I know *S*

The problem I have is this:
1. The report will be run for different customers
Click to expand...
each
time.
2. Each customer has several different account names
(column headings)
3. The source is a crosstab query.

Do you have a better solution for this? These will be
showing different accounts and their data, kinda like it is
done for different months.

Yes, I had it in the wrong area......

Once again, thank you.

G


-----Original Message-----
G wrote:

I am having difficulty adding controls to a
Click to expand...
report. I
am
able to create the report as follows:
Set rpt = CreateReport
With rpt
.RecordSource = "qryCalculateMktValue"
.Caption = "LNW"
.Visible = True
End With

' Restore new report.
DoCmd.Restore
But the following does not work:
with CreateReportControl(reportname as string, controltype
as integer)
It not only does nothing, it will not let me set anything,
like .name
what am I doing wrong?


It sounds like you're due for my Create... lecture ;-)

From the tone of your question, I get the impression that
you are not using that code to build yourself a
Click to expand...
design
aid
kind of wizard. If you're thinking of doing this
Click to expand...
kind
of
thing at runtime, then you're on the wrong track.

For several very good reasons, it's a really bad
Click to expand...
idea
to be
creating heavy duty objects such as forms or reports on the
fly. Instead, you should have a prebuilt report
Click to expand...
that
can
take care of your various situations and then set control
and/or grouplevel properties to adjust as needed at runtime
(usually in the report's Open event).

Besides, without seeing the actual line of code that you're
trying to use, no one's going to be able to figure out
what's wrong with it.
--
Marsh
MVP [MS Access]
.



.
Click to expand...
Click to expand...


.
Click to expand...
 
I can't believe "they do not want much coding done." and yet have
requirements that add absolutely no value. Certainly this can't be done
without a fair amount of code. Programmatically adding fields takes a lot
more code than the sample I suggested.

--
Duane Hookom
MS Access MVP


G said:
the user has requested that this report have crosstab
headings (i.e. different account names) with totals and
percentages of total. She has also requested that
according to how many columns there would be, that the
font size and layout be adjusted to be presentable (ie
larger fonts). Now she has changed the max of entities
from 7 to whatever and would also like the page and paper
orientation changed depending on the # of entities.

Client A might have 2 entity names, and Client B might
have 8 entity names.
What I was thinking is that since they asked me to NOT do
much coding, if it would be easier to put up a standard
report so that if the user clicked on Client A, the report
for clients with 2 entities would come up. and the same
for Client B, the report for clients with 8 entities would
come up.

But now I have gone back to the original where it makes a
report based on the crosstab, programmatically adding
fields to the report,

Any advice on this would be greatly appreciated.

Thank you in advance for all your help. Sorry to be such a
problem with this.

G
-----Original Message-----
Why would you need 2 column, 3 column,.. reports for the same data? Do you
have different widths of paper?

--
Duane Hookom
MS Access MVP
--

G said:
Duane,
I have more questions regarding this issue. Should I
repost so there is a newer date with the newer issues?

What I need to know, is this the only way to accomplish
changing the report based on the column headers?
Would making a separate report for each column header
count work? ie report#2 = 2 columns of data, report#3 = 3
columns of data.

Where I am working at, they do not want much coding done.
But I don't see any way around it.

G
-----Original Message-----
There is a demo for creating crosstab reports with
dynamic column headings
at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
This solution will
create unique column headings based on the customer since
each customer
could have a unique set of account names.

--
Duane Hookom
MS Access MVP
--

Thank you, Marsh.
I have a pre-built report that runs off a query.
I have purchased developer books and that is where this
code comes from. However, I am still experimenting with
it.
I have a ways to go, I know *S*

The problem I have is this:
1. The report will be run for different customers each
time.
2. Each customer has several different account names
(column headings)
3. The source is a crosstab query.

Do you have a better solution for this? These will be
showing different accounts and their data, kinda like
it is
done for different months.

Yes, I had it in the wrong area......

Once again, thank you.

G


-----Original Message-----
G wrote:

I am having difficulty adding controls to a report. I
am
able to create the report as follows:
Set rpt = CreateReport
With rpt
.RecordSource = "qryCalculateMktValue"
.Caption = "LNW"
.Visible = True
End With

' Restore new report.
DoCmd.Restore
But the following does not work:
with CreateReportControl(reportname as string,
controltype
as integer)
It not only does nothing, it will not let me set
anything,
like .name
what am I doing wrong?


It sounds like you're due for my Create... lecture ;-)

From the tone of your question, I get the impression
that
you are not using that code to build yourself a design
aid
kind of wizard. If you're thinking of doing this kind
of
thing at runtime, then you're on the wrong track.

For several very good reasons, it's a really bad idea
to be
creating heavy duty objects such as forms or reports
on the
fly. Instead, you should have a prebuilt report that
can
take care of your various situations and then set
control
and/or grouplevel properties to adjust as needed at
runtime
(usually in the report's Open event).

Besides, without seeing the actual line of code that
you're
trying to use, no one's going to be able to figure out
what's wrong with it.
--
Marsh
MVP [MS Access]
.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top