From: jpm on
Steve,
My objective is to use one report as a "template" and feed it different
recordsources that have varying numbers of fields with varying field names
which can be displayed for a user to review the information.

This is what I posted earlier:

I would like to add the controls and relate the recordsource fields to them
at run time.(when the report is programmatically loaded)
I'm understanding you to say this isn't possible. . .

If I stick a sub-form/report on a report object, can I set the source
object to an ADO recordset in a non-ADP database file?
If so, will it automatically display the recordset(create fields for display
to the user?)
Are there examples of this that anyone can share?

Much appreciated.

Jim M.

"Steve" <notmyemail(a)address.com> wrote in message
news:uM3w9vR9KHA.5476(a)TK2MSFTNGP06.phx.gbl...
> Please post what your database is about and why you want to do what you
> describe here. There are ways to crate a dynamic report you may be able to
> use.
>
> Steve
> santus(a)penn.com
>
>
> "jpm" <uh(a)Idetestspam@mars> wrote in message
> news:e0$kB1E9KHA.3840(a)TK2MSFTNGP02.phx.gbl...
>> Hello,
>>
>> I've not much experience with building reports in MsAccess. Can one
>> dynamicall add fields/controls to reports from a vba module such as that
>> linked to a form? i.e. do processing . . .set a sql string as a report's
>> recordsource, then have the fields of the recordsource added or displayed
>> in the report. I want to do this without having predefined the report
>> content/format, etc. Can someone give me information or point me to
>> information on this?
>>
>> Part of my reason for persuing this data display method is that there
>> doesn't seem to be a grid control for displaying data on a form. So, how
>> about a report that is built dynamically; can I do that?
>>
>> Thanks for any help,
>>
>> Jim M.
>>
>>
>>
>
>


From: Steve on
Jim, See if the following helps at all .......

Steve

Marshall Barton says in the next post:

If you want users to create their own reports with whatever
data and layout they might dream up, then explore providing
them with a separate playground mdb file that has links to
the tables mdb file and maybe a few queries to collect data
from related tables.


From my files:
Ad Hoc Report

I only did this because a client required this functionality. No
guarantees or rights are implied by the code shown here. It is
provided mostly for those with extra time on their hands.

Suppose a base table has many fields. It may be desirable to allow
the user to
select any fields (say up to 10 fields) in any order and have a report
set up headings, values and totals for the selected fields.

To do this I created a field selection form. This form has a check
box for every field in the base table along with a listbox to show the
fields in the order selected by the user. I also have a command
button to clear all the columns and a combobox to select and fill
saved report configurations. When a checkbox is unchecked it takes
the field out of the list. This form interacts with the following
table:

tblCostingReportColumnOrder:
ColumnName T50 --The name of the field in the base table
ListOrderNumber Int --All 0's except for 1, 2, 3, etc. of selected
fields
ReportColumnName T50 --Aliases for the ColumnName heading
ValsIndex Int --Used to order the ColumnName values
FieldType T50 --Double, Date, Text, Currency, etc.
MaxWidth Int --For text it's the field size, Y/N is 2, Currency is 10
ActualMaxWidth Int --Value computed from qryFillFlex

The 'Print Report' command button on the same form creates a SQL
string that selects fields whose ListOrderNumber > 0. This SQL string
is used to calculate the maximum characters used by each field (Note:
ActualMaxWidth >= Heading Width). The sum of these maximums is used
to decide whether to open a report in LetterPortrait, LetterLandscape,
LegalLandscape or LegalLandscapeVariableFont (shrink font to fit
page).

The report has a RecordSource of qryFillFlex. The report has all the
headings invisible and stacked on top of one another in the Page
Header. The text boxes with corresponding Control Sources are in the
Detail Section. The text boxes for totals are in the Report Footer.
Note that these must have names that look like lbl<FieldName>,
txt<FieldName> and txtTotal<FieldName> for the way it was implemented
here.

Note: This report is usually used from a search form that calls the
field selection form and also creates qryFillFlex to limit the records
to those shown on the search subform.

The Report_Open code for LetterPortrait looks like:
------------------------------------
Private Sub Report_Open(Cancel As Integer)
Dim MyDB As Database
Dim CRS As Recordset
Dim ColumnName(10) As String
Dim ReportColumnName(10) As String
Dim ActualMaxWidth(10) As Integer
Dim LabelName(10) As String
Dim TextBoxName(10) As String
Dim TotalBoxName(10) As String
Dim MaxWidthNumber(10) As Long
Dim LeftNumber(10) As Long
Dim FieldWidth(10) As Long
Dim strSQL As String
Dim lngCount As Long
Dim lngI As Long
Dim TotalWidthInTwips As Long
Dim TotalCharacters As Long
Dim Response As Variant
Dim strPrompt As String
Dim strTitle As String

strTitle = "Get Report Title"
strPrompt = "Click OK or Type in a Report Title"
Response = InputBox(strPrompt, strTitle, "Costing Report")
lblTitle.Caption = Nz(Response, "")

If IsFormOpen("frmSelectCostingReportFields") Then
If Not IsNull(Forms!frmSelectCostingReportFields!cbxCostingReports.Value)
Then
lblSavedReportName.Caption =
Forms!frmSelectCostingReportFields!cbxCostingReports.Value
End If
End If

'Obtain chosen fields and ActualMaxWidth values so that
'appropriate spacing can be chosen

'Select only the first 10 fields chosen
'Report_rptFlexCostingLetter.RecordSource =
GetstrFlexCostingReportSQL()
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblCostingReportColumnOrder WHERE "
strSQL = strSQL & "[ListOrderNumber] > 0 AND [ListOrderNumber] < 11 "
strSQL = strSQL & "ORDER BY ListOrderNumber DESC;"
Set CRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
'Get ActualMaxWidth and Names of fields chosen
If CRS.RecordCount > 0 Then
CRS.MoveLast
lngCount = CRS.RecordCount
CRS.MoveFirst
TotalWidthInTwips = Int(9.9 * 1440) '9.9 allows 1" margins for
letter portrait
For lngI = 1 To lngCount
ColumnName(lngI) = CRS("ColumnName")
ReportColumnName(lngI) = Nz(CRS("ReportColumnName"), "")
ActualMaxWidth(lngI) = CRS("ActualMaxWidth")
If ActualMaxWidth(lngI) < Len(Nz(ReportColumnName(lngI), "")) Then
ActualMaxWidth(lngI) = Len(Nz(ReportColumnName(lngI), ""))
'Require each field to occupy at least 10 characters
If ActualMaxWidth(lngI) < 10 Then ActualMaxWidth(lngI) = 10
LabelName(lngI) = "lbl" & ColumnName(lngI)
TextBoxName(lngI) = "txt" & ColumnName(lngI)
TotalBoxName(lngI) = "txtTotal" & ColumnName(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Caption")
= ReportColumnName(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Visible")
= True
MaxWidthNumber(lngI) = ActualMaxWidth(lngI)
Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Visible�")
= True
Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Visibl�e")
= True
If lngI <> lngCount Then CRS.MoveNext
Next lngI
TotalCharacters = 0
For lngI = 1 To lngCount
TotalCharacters = TotalCharacters + ActualMaxWidth(lngI)
Next lngI
For lngI = 1 To lngCount
FieldWidth(lngI) = MaxWidthNumber(lngI) * TotalWidthInTwips /
TotalCharacters
Next lngI
LeftNumber(1) = 0
If lngCount >= 2 Then
For lngI = 2 To lngCount
LeftNumber(lngI) = LeftNumber(lngI - 1) + FieldWidth(lngI - 1)
Next lngI
End If
For lngI = 1 To lngCount
Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Width")
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Width"�)
= Int(FieldWidth(lngI))
Report_rptFlexCostingLetter.Controls(TextBoxName(lngI)).Properties("Left")
= LeftNumber(lngI)
Report_rptFlexCostingLetter.Controls(LabelName(lngI)).Properties("Left")
= LeftNumber(lngI)
Report_rptFlexCostingLetter.Controls(TotalBoxName(lngI)).Properties("Left")
= LeftNumber(lngI)
Next lngI
End If

CRS.Close
Set CRS = Nothing
Set MyDB = Nothing
End Sub

"jpm" <uh(a)Idetestspam@mars> wrote in message
news:ub8lFnd9KHA.5476(a)TK2MSFTNGP06.phx.gbl...
> Steve,
> My objective is to use one report as a "template" and feed it different
> recordsources that have varying numbers of fields with varying field names
> which can be displayed for a user to review the information.
>
> This is what I posted earlier:
>
> I would like to add the controls and relate the recordsource fields to
> them
> at run time.(when the report is programmatically loaded)
> I'm understanding you to say this isn't possible. . .
>
> If I stick a sub-form/report on a report object, can I set the source
> object to an ADO recordset in a non-ADP database file?
> If so, will it automatically display the recordset(create fields for
> display
> to the user?)
> Are there examples of this that anyone can share?
>
> Much appreciated.
>
> Jim M.
>
> "Steve" <notmyemail(a)address.com> wrote in message
> news:uM3w9vR9KHA.5476(a)TK2MSFTNGP06.phx.gbl...
>> Please post what your database is about and why you want to do what you
>> describe here. There are ways to crate a dynamic report you may be able
>> to use.
>>
>> Steve
>> santus(a)penn.com
>>
>>
>> "jpm" <uh(a)Idetestspam@mars> wrote in message
>> news:e0$kB1E9KHA.3840(a)TK2MSFTNGP02.phx.gbl...
>>> Hello,
>>>
>>> I've not much experience with building reports in MsAccess. Can one
>>> dynamicall add fields/controls to reports from a vba module such as that
>>> linked to a form? i.e. do processing . . .set a sql string as a
>>> report's recordsource, then have the fields of the recordsource added or
>>> displayed in the report. I want to do this without having predefined
>>> the report content/format, etc. Can someone give me information or
>>> point me to information on this?
>>>
>>> Part of my reason for persuing this data display method is that there
>>> doesn't seem to be a grid control for displaying data on a form. So, how
>>> about a report that is built dynamically; can I do that?
>>>
>>> Thanks for any help,
>>>
>>> Jim M.
>>>
>>>
>>>
>>
>>
>
>


From: Duane Hookom on
I generally include the Ad-Hoc/query by form applet available at
http://www.rogersaccesslibrary.com/forum/dh-query-by-Form_topic12.html. It
allows users to select any number of fields and set various criteria etc.
Users are a couple clicks from pushing the results to Excel where they can
print them or whatever.

--
Duane Hookom
MS Access MVP


"jpm" <uh(a)Idetestspam@mars> wrote in message
news:ub8lFnd9KHA.5476(a)TK2MSFTNGP06.phx.gbl...
> Steve,
> My objective is to use one report as a "template" and feed it different
> recordsources that have varying numbers of fields with varying field names
> which can be displayed for a user to review the information.
>
> This is what I posted earlier:
>
> I would like to add the controls and relate the recordsource fields to
> them
> at run time.(when the report is programmatically loaded)
> I'm understanding you to say this isn't possible. . .
>
> If I stick a sub-form/report on a report object, can I set the source
> object to an ADO recordset in a non-ADP database file?
> If so, will it automatically display the recordset(create fields for
> display
> to the user?)
> Are there examples of this that anyone can share?
>
> Much appreciated.
>
> Jim M.
>
> "Steve" <notmyemail(a)address.com> wrote in message
> news:uM3w9vR9KHA.5476(a)TK2MSFTNGP06.phx.gbl...
>> Please post what your database is about and why you want to do what you
>> describe here. There are ways to crate a dynamic report you may be able
>> to use.
>>
>> Steve
>> santus(a)penn.com
>>
>>
>> "jpm" <uh(a)Idetestspam@mars> wrote in message
>> news:e0$kB1E9KHA.3840(a)TK2MSFTNGP02.phx.gbl...
>>> Hello,
>>>
>>> I've not much experience with building reports in MsAccess. Can one
>>> dynamicall add fields/controls to reports from a vba module such as that
>>> linked to a form? i.e. do processing . . .set a sql string as a
>>> report's recordsource, then have the fields of the recordsource added or
>>> displayed in the report. I want to do this without having predefined
>>> the report content/format, etc. Can someone give me information or
>>> point me to information on this?
>>>
>>> Part of my reason for persuing this data display method is that there
>>> doesn't seem to be a grid control for displaying data on a form. So, how
>>> about a report that is built dynamically; can I do that?
>>>
>>> Thanks for any help,
>>>
>>> Jim M.
>>>
>>>
>>>
>>
>>
>
>
First  |  Prev  | 
Pages: 1 2
Prev: Report summary
Next: Dynamic Reporting System