Set the subreport recordsource from a control (2 Viewers)

[FCrocco]

I create a long routine process where the user creates a table and, at the final phase, he fills a form and press a button to print it as a report. It's working fine but know I want to print in the report the table created as a subreport. Each time the routine is carried on a new table with a different name is created.

I've not been able to define the subreport recordsource to exhibit the new table created in the process.

I tried different codes but without success.
Any help. Thanks

 

[theDBguy]

Hi. Welcome to AWF!

Not sure why the name of the table has to be different each time but is the structure always the same?

 

[The_Doc_Man]

The recordsource of the sub-report should be accessible from code in the main report as

Me.subreportcontrolname.report.recordsource= "SELECT A, B, C, ... FROM X ;"

You have to know the name of the control that will contain the report but you don't actually have to name a query or table on the left-hand side. You DO need to supply something useful like a query on the right-hand side though.

From that error, it might just be a matter of timing. The subreport has to exist - which it does when you define it in design mode. The trick will be that the new recordsource field names have to somehow align with the sub-report field definitions.

 

[FCrocco]

Thanks theDBguy and The_Doc_Man.

Yes, each new table has a different number of fields. The table is created as a DAO.Database and each field as variant using TableDefs.

Thinking on what The_Doc_Man said, maybe I will need to find another way to join the table to be printed together with my report. Impossible to have a new table always with the same structure.

 

[MajP]

I've not been able to define the subreport recordsource to exhibit the new table created in the process

That code does not have anything to do with setting the recordsource of the subreport?

That code is setting the control source of [Title Label] on sub report RepChild. And what is NewControlSource? That needs to be a string with the name of a control.

Also this sounds like a very poor design. You may want to explain why you have such a kludged solution. There is probably a much better solution.

 

[theDBguy]

Yes, each new table has a different number of fields. The table is created as a DAO.Database and each field as variant using TableDefs.

Not sure how useful this approach would be for you, but you can also simply assign the name of the new table as the record source of your subreport.

 

[The_Doc_Man]

Impossible to have a new table always with the same structure.

If that is indeed your situation, you are looking at a problem indeed. I might suggest a "divide and conquer" approach. Your sub-report could be a normal report that is embedded via OLE techniques somewhere in the body of the main report. That would get you past the problem of having essentially two different reports in a single file.

It will NOT get you past the problem that Access generally aligns fields by name, and if you say the structures will change, that implies the names will also change (at the very least by being present or NOT being present). You cannot make a coherent report with a variable-structure data source very easily.

The problem is simple. Access cannot design anything. The foundation of Access was created long before we had AI modules. It is true that you can get Access to generate an automatically formatted report. However, "automatic" reports are based on a simple linear enumeration of the fields you named, one field per line of the detail area. About as imaginative as a stack of cafeteria plates. Worse, I'm not sure you could trigger the report layout wizard from code. If you want more than a really bleak report layout, you have to do the designing manually.

 

[FCrocco]

Yes, theDBguy. That's exactly what I haven't been able to code. How to name/define the subreport record source with the name of the new table?
At the end, I thought, it's only a syntax problem.

 

[FCrocco]

Ok, The_Doc_Man. I will study a little how to work with OLE techniques.
Thanks.

 

[theDBguy]

Yes, theDBguy. That's exactly what I haven't been able to code. How to name/define the subreport record source with the name of the new table?
At the end, I thought, it's only a syntax problem.

It could be a syntax problem or a timing one. A simpler approach may be to assign a query as the record source for the subreport and just modify its definition before opening the report.

 

[FCrocco]

I believe it could be a good strategy. And I thought to create a sql query with all * fields of the table. Doesn't matter how many fields it has. Then create a Recordset and populate it. However, it's like The_Doc_Man stressed: The Access isn't able to format it inside the subreport.
A mess again.

 

[theDBguy]

I believe it could be a good strategy. And I thought to create a sql query with all * fields of the table. Doesn't matter how many fields it has. Then create a Recordset and populate it. However, it's like The_Doc_Man stressed: The Access isn't able to format it inside the subreport.
A mess again.

Format how?

 

[The_Doc_Man]

Note that there IS such a thing as a tabular view (called Datasheet View) for queries. THAT doesn't care about having a specific layout. It just makes something that looks like (but is not) a spreadsheet. It works with any names you give it. And THAT could be embedded in your report. You would be able to impose a limited amount of field formatting when building the query. Since we are talking "report" you would not expect it to be able to interact with the user, though.