Query based Report in Access using VBA

[amith.srinivas@gmail.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.Cre ateReport
rpt.RecordSourc e = <<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.Create ReportControl(r pt.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.CreateReport Control(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.CreateRepor tControl(repNam e, 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

[Larry Linson]

Re: Query based Report in Access using VBA

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

<amith.srinivas @gmail.com> wrote in message
news:1138318724 .197398.130400@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> 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.Cre ateReport
> rpt.RecordSourc e = <<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.Create ReportControl(r pt.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.CreateReport Control(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.CreateRepor tControl(repNam e, 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
>[/color]

[amith.srinivas@gmail.com]

Re: Query based Report in Access using VBA

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 programmaticall y 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.Probabl y 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:[color=blue]
> 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
>
> <amith.srinivas @gmail.com> wrote in message
> news:1138318724 .197398.130400@ g49g2000cwa.goo glegroups.com.. .[color=green]
> > 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.Cre ateReport
> > rpt.RecordSourc e = <<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.Create ReportControl(r pt.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.CreateReport Control(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.CreateRepor tControl(repNam e, 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
> >[/color][/color]