By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Query based Report in Access using VBA

P: n/a
Hi all,
From a word macro in VBA, I am trying to create a report in
access DB. The report is based on a query with a parameter. I am using

Set rpt = Application.CreateReport
rpt.RecordSource = <<Query_Name>>

Once I create the report, I m trying to create labels to display the
Report title in the Report Header section.

Set ctl = database.CreateReportControl(rpt.Name, acLabel, acHeader)

But this statement is failing with an error that section number is
invalid.

Then I am trying to get the column headers as labels in Pageheader
section of the report.
Set ctl = DB.CreateReportControl(repName, acLabel, acPageHeader)
ctl.Properties("Caption") = "Id"
This is not failing but the the label is not at all visible in the
design of the Report.

Then I m trying to use a textbox bound to the fields in the recordset.
I m able to get the values visible in the report.
Set ctl = qDB.CreateReportControl(repName, acTextBox, acDetail,
ColumnName:="Id")

Can anbody please tell me if there is any other better alternative to
create a simple query based report than the one I have adopted.

If not, how do i make those labels visible in the Report Header
section to set the title of the report.

I am pretty new to access and just wondering whats being wrong here.

Is there any way in VBA to go for "AutoReport: Tabular" report
????????

Any help would greatly be appreciated.

Thanks,
Am.It

Jan 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Yes, my suggestion is that you create the Access database, and the report,
in Access itself, and then just execute it from your Word VBA -- no Access
developer in his/her right mind would create a report with code in
preference to creating it using the very excellent (and, arguably, best ever
in any product) report generator in Access.

Larry Linson
Microsoft Access MVP

<am************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi all,
From a word macro in VBA, I am trying to create a report in
access DB. The report is based on a query with a parameter. I am using

Set rpt = Application.CreateReport
rpt.RecordSource = <<Query_Name>>

Once I create the report, I m trying to create labels to display the
Report title in the Report Header section.

Set ctl = database.CreateReportControl(rpt.Name, acLabel, acHeader)

But this statement is failing with an error that section number is
invalid.

Then I am trying to get the column headers as labels in Pageheader
section of the report.
Set ctl = DB.CreateReportControl(repName, acLabel, acPageHeader)
ctl.Properties("Caption") = "Id"
This is not failing but the the label is not at all visible in the
design of the Report.

Then I m trying to use a textbox bound to the fields in the recordset.
I m able to get the values visible in the report.
Set ctl = qDB.CreateReportControl(repName, acTextBox, acDetail,
ColumnName:="Id")

Can anbody please tell me if there is any other better alternative to
create a simple query based report than the one I have adopted.

If not, how do i make those labels visible in the Report Header
section to set the title of the report.

I am pretty new to access and just wondering whats being wrong here.

Is there any way in VBA to go for "AutoReport: Tabular" report
????????

Any help would greatly be appreciated.

Thanks,
Am.It

Jan 26 '06 #2

P: n/a
Hi Larry,

Probably, you were not in a position to understand my need here Or I
have failed to make it clearer. Ofcourse, no one would like to code a
task which could simply be done by manually using the user interface in
a product.

My need is that, I need to programmatically read a bunch of word
documents in a folder and populate the values of critical fields of
those documents in the db records, corresponding to each document, in
an access database. So for the first document, if a database is not
existing in the folder, then the word macro(which should be dowloadable
as a tool from the intranet) create the database and upload those
values.

Once the db is populated, the word macro is supposed to create queries
and a report based on a query with a db field value as the parameter.
So the end user dont have to do anything other than just clicking on
the report control which would ask for the parameter value for hte
query and at the end, user should be able to get a report.

I am done with every thing except for the report part and am seeking
solutions for my part of the problem.Probably now, it makes sense for
you to know why I m struggling with the code generation of the report.

Thanks,
Am.It

Larry Linson wrote:
Yes, my suggestion is that you create the Access database, and the report,
in Access itself, and then just execute it from your Word VBA -- no Access
developer in his/her right mind would create a report with code in
preference to creating it using the very excellent (and, arguably, best ever
in any product) report generator in Access.

Larry Linson
Microsoft Access MVP

<am************@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi all,
From a word macro in VBA, I am trying to create a report in
access DB. The report is based on a query with a parameter. I am using

Set rpt = Application.CreateReport
rpt.RecordSource = <<Query_Name>>

Once I create the report, I m trying to create labels to display the
Report title in the Report Header section.

Set ctl = database.CreateReportControl(rpt.Name, acLabel, acHeader)

But this statement is failing with an error that section number is
invalid.

Then I am trying to get the column headers as labels in Pageheader
section of the report.
Set ctl = DB.CreateReportControl(repName, acLabel, acPageHeader)
ctl.Properties("Caption") = "Id"
This is not failing but the the label is not at all visible in the
design of the Report.

Then I m trying to use a textbox bound to the fields in the recordset.
I m able to get the values visible in the report.
Set ctl = qDB.CreateReportControl(repName, acTextBox, acDetail,
ColumnName:="Id")

Can anbody please tell me if there is any other better alternative to
create a simple query based report than the one I have adopted.

If not, how do i make those labels visible in the Report Header
section to set the title of the report.

I am pretty new to access and just wondering whats being wrong here.

Is there any way in VBA to go for "AutoReport: Tabular" report
????????

Any help would greatly be appreciated.

Thanks,
Am.It


Jan 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.