New saga in my continuing effort to automate data retreival and now Report creation through VBA

[puforee]

After I have loaded a table/s from my spreadsheets I need to create a Form for each one and a Report for each one. The form was easy with the help of this forum. The report is a little more difficult. The report will have 16 to 20 columns and when automatically created it runs out of horizontal space and drops a column or two.

I have manually created reports ensuring all the columns fit. I now want to save one of these as an empty, report but with all the controls, named Rptemp1 as an example. I then want to use VB to create the report from one of the new Tables using Rptemp1 as a template. I understand this can be done but I am not sure how to Identify the table for the report and identify the template and then save the new report with the table (name)&"_"&"Report".

Can anyone point me in the right direction for this. Google only gives me hints but not enough for me to put it all together.

Thanks,

 

[SkipVought]

Hi,

it runs out of horizontal space

...meaning???

Report can't fit on one sheet of paper? Or what?

Also noticed you did not post this in forum703???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[puforee]

Hi Skip,
Actually I put this here because I am looking for code but it could go under reports also.

And, the reason I want to code a create report using a template is because...
When in design view, I select a table and then use the Create Ribbon tab, Reports, Report button. It does create the report but does not include the right most 6 columns. I need them all.

So, I created the Report using Create....Reports....Report Wizard. This created a report with all columns (Table fields). I then adjusted fonts and field sizes so they all fit. The report will print on 11 X 17.

Now back to the code and template. I want to save the new report as Rptemp1 with no Record Source. Then, when I import a new table I will be able, through VBA, to assign the Record Source to Rptemp1 and, since the table name is known by the code, I can save the new report as table name Rpt.

So, this is what this is all about.

Thanks,

 

[MajP]

I do not understand what you are doing. Does the new table that you will import have the same field names? It sounds as if the new table is the same data. If so, you do only need the one report. You can pick then make a way to select the correct recordsource when the report opens. But my next question is if this is the same data (maybe updated), why are you making a new table and not appending to the existing table. Why have multiple tables if they contain the same set of data?

 

[dhookom]

I'm not sure why you would use code to create the report. It would be like building an automated door opener when you only need to open the door a couple times.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016