ACCESS VBA - "DoCmd.RunCommand acCmdNewObjectAutoReport" doesn't add all fields to Report

Question

Hi,

I have been trying to create an access report from an existing table using below VBA code. It creates the report successfully, But the issue is it doesn't add all the fields from the table to the report created. It is adding only 15 fields. I am very new to dealing with access reports. Any help would be appreciated.

DoCmd.SelectObject acTable, "Data_Failures", True
DoCmd.RunCommand acCmdNewObjectAutoReport
DoCmd.Close , , acSaveYes

Answer 1

As a general rule that "automatic" creating of a report is a quick and dirty option.

As a general rule?

You want to create the report BEFORE you start using code. So, create the report, lay it out the way you want. Add the fields, set page layout landscape or normal. This goes "on and on" for some time.

Once you have the report the way you want, you save it, and now can launch that report anytime you want.

docmd.OpenReport "myReportName", acViewNormal

So, that option I think only grabs the first 15 fields. it not really a "program" feature, or something that developers would use in code (not really sure why they have that option). But it gives you ZERO control over the layout - and I think it only pulls the first 15 fields anyway (or puts additional fields on the next page). All in all - it not really a useful nor practical option.

So, create the report BEFORE you start using and writing code. Get the report the way you want and then save it. Once you have that great looking report, then you can certainly say have a button or code in a form or such some to launch + display the report. But allowing the "default" create of the report with that option simple does not give you much if any control over the results and things like only 15 first controls etc. are of course "minor" to the overall limitations of such an approach.

Worse, is each time you run that command, a whole new report is created - and once again you have little to next control over the results.

So, build the report first - use the access UI part, and then after you get the report JUST the way YOU want, save it, and then use that along with the openReport command as per above.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Thanks for your input Albert @Albert Kallal . But my requirement here is very simple that I just want whole data from a table into the report and doesn't require any greater control over it apart from creating the report with all the data from the table . And Yes, I am using this command to create report every time but in different access dbs and that's no concern for me.

I want to create the report dynamically every time in diferent access dbs and so need to do it from the code where the source(existing table) for the report changes in different dbs. It will be time taking to add dynamically 100s of columns even through the code by looping through the columns of table and adding one by one to the report.

Request to advise me if there's any way to get the whole data from table to report without us creating each field in the report again just like "DoCmd.RunCommand acCmdNewObjectAutoReport" did but without limitation.

Thanks in Advance.

Regards,
Manoj