Creating Report With VBA and a Report Template

[JJSAccess]

I have a data base used to monitor and track licenses...several hundred. On occasion we will be sent a spreadsheet of data. Sometime there will be several tabs to the spreadsheet. I have VB in my data base that will import the data from each tab of the spreadsheet and save it as a table with the tab name. This works very well.

I then need a Form for each table. This is also done with the same VB that is importing the data.

Next, I need a Report for each imported table. This is where the problem occurs. I should explain here that the data on each tab of the spreadsheet can be from 16 to 20 columns wide. When I use the command to create an AutoReport based on the table the resultant is missing data. The AutoReport will not show all columns...I am guessing it cannot fit them all on a 21" wide report.

So, I created, manually, the report I need including the controls. I then removed the record source property and saved the report as template named TempRpt.

Now back to my import code. I want to create a report based on the new table/s and based on the TempRpt. All the controls will be of the same name. But, I can't put it all together. I know there is a way to Change the Record source of the report but I can't make it work. Then I would open TempRpt, (not visable), and save it as the Spreadsheet Tab/Table name. Remember, this is all happening based on code attached to a buttons click event.

By the way, the template (TempRpt) I made will actually print all 16 to 20 columns on an 11 X 17 sheet of paper. Also, note, the difference of the column counts will lead me to have several report templates and several Spreadsheets setups. I can handle this.

Help!
JJSAccess and thanks in advance.

 

[pbaldy]

My first thought is a single report with enough controls to handle the maximum number of fields possible. You can have code in the open event that sets the record source if appropriate, then opens a recordset on that source. It would read the fields and set the control sources of textboxes on the report (numbered, like txt1, txt2 so you can loop through them). That would probably require the sources to have some consistency, so you could preset the textbox widths. I do this with crosstab reports where the fields change each time they're run.

 

[JJSAccess]

Paul,
Thanks for the response. Actually in the end I will have several Report templates to ensure they match the import tables. This will be fairly stable. I want the code to modify the report to run under a button controlled code that actually imports and names the table (so table name is known). It then creates a form using the table name, with AUTOForm. But, as said above the AUTOReport does not work for me.

So, looking for code that can be run from the Button VB that can update my stored template report source to the new table name I just downloaded.

Now to ask a question....if, in my import code, I save my table name as a variable...can I refer to that variable from the Report Open even? If I can I think I can set the Report record source like you mentioned above.

I would probably open the Report template in hidden view and use its open event to set the record source to the Table name variable and then close and save the report as the table name..Report. Does this sound reasonable. My only question is, how do I end my button code (end sub) if I am opening the report with Open event code. Will it finish?

Thanks,

 

[JHB]

pbaldy has given you a solution, how such cases like yours normal is solved.
It seems that you do not want to use that solution, so I think you've to explain why you want to create forms and reports at runtime and afterwards save them?
Some printscreens could help.