programmatically create query based Report in Access using VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Am.It - 26 Jan 2006 23:33 GMT

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

Reply to this Message

david@epsomdotcomdotau - 27 Jan 2006 06:13 GMT

I see that you don't have a report header section. Perhaps
you can use RunCommand to use the menu item to enable
report headers or footers? If not, you will have to save
the report design as the report design template.

But yes, there is a better way. Save a template report
with most of the features you want, and just use .visible
to make unwanted features and controls invisible.

Use .left and .top to move controls to the desired position.

You can do all this in the Open and first Format events.

If you really really do want to make design changes, just
open the template in design mode and make desired changes,
then Save to a new name.

(david)

> Hi all,
> From a word macro in VBA, I am trying to create a report in
[quoted text clipped - 38 lines]
> Thanks,
> Am.It

Reply to this Message

Am.It - 27 Jan 2006 15:41 GMT

Hi David,
Thanks a lot for your solution. I can definitely use the
RunCommand to enable the report headers & footers. But I will not be
able to create a template since the whole task needs to be automated.

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) should 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 stuck
over there. So here I would not be able to neither use any template
nor create a new template since I guess, it would make my task more
cumbersome.

I was really wondering whether there is any way to go for that
"AutoReport - Tabular" feature in VBA.

But I appreciate your suggestions and any further help would bring me
closer to the solution as well.

Thanks,
Am.It

> I see that you don't have a report header section. Perhaps
> you can use RunCommand to use the menu item to enable
[quoted text clipped - 57 lines]
> > Thanks,
> > Am.It

Reply to this Message

david@epsomdotcomdotau - 28 Jan 2006 01:09 GMT

> "AutoReport - Tabular" feature in VBA.

That is a wizard. Yes, it may be possible to call the
wizard directly, if it is installed. You should post as
a new question if you are interested in using the wizards.

(david)

> Hi David,
> Thanks a lot for your solution. I can definitely use the
[quoted text clipped - 90 lines]
> > > Thanks,
> > > Am.It

Reply to this Message