Creating "unbound" fields then late binding to recordset?

[user9837]

I would like to create a report in which the record source is created via ADO code and then set as the record source for the report. The problem I am running into is how to add fields to the report since the recordset is bound to the report at run-time and not design time. Is there way I can manually add the field and make sure the name matches what will be provided in the recordset? Thank you.

 

[vbaInet]

What you're trying to say is you would want to use one report as a base for differing recordsets?

Setting the Control Sources of the controls wouldn't be much of a problem, your problem will be setting the width of the controls appropriately. Also you would have to deal with differing number of fields from the recordset.

In my opinion, create individual reports.

 

[user9837]

Actually, I am fine with creating individuals reports. The problem is the fields are not known at design time since the reports are based on dynamic recordsets. Essentially I want to use stored procedures called via ADO code as the source for the reports.

 

[vbaInet]

What you're venturing into is similar to a report designer module. This isn't an easy task. As previously mentioned, your main problem would be setting the width of the controls so that the text fits.

The idea is to drop many textboxes and labels onto your report and name them appropriately - txt1, txt2, txt3... etc, lbl1, lbl2, lbl3 etc... Iterate through the list of fields in your recordset and using Controls() collection you can set the Control Source of the textbox (starting from txt1). Then hide the rest.

 

[user9837]

I see what your saying. Indeed it looks to be a bit tricky! It may be better to create some views and then base the reports directly on the views. Thanks for your example and help!

 

[vbaInet]

Yes quite tricky. There are other factors to consider to get the widths right too.

I think the views approach might be more suitable. Good luck!