error 2191, cant set control source property in print preview

[hangbill]

Greetings
Dont have much coding experience and am hoping that there is a relatively straight forward solution. Have been trying to get it resolved for some time now, without success.

Using Access 2003.
Have a sub report, based on a crosstab. Some of the controls on the sub report are unbound and they get their values from an On Open event.

This works fine when I open the sub report on its own. But when I try open the main report, which contains the subreport, get an error 2191 ie "you cant set the control source property in print preview or after printing has started".

The On Open code is

Private Sub Report_Open(Cancel As Integer)
Dim IntI As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("select * from QryRxAudit")

rs.MoveLast
rs.MoveFirst
For IntI = 1 To rs.RecordCount
Me("Col" & IntI).ControlSource = rs("RxAuditID")
Me("TotalCol" & IntI).ControlSource = "=Sum([" & rs("RxAuditID") & "])" & "/" & ("SumTotalOfNumberAuditID")
rs.MoveNext
Next IntI
End Sub

Hope you can help
Thank you.

 

[dhookom]

I think there are better solutions for creating reports from crosstabs. Consider the Crosstab.mdb in the download from

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[hangbill]

Thank you Duane, I'll check it out.

I've already done a lot of work and would prefer for this project not to redo the work. Is there a solution for the way I've currently got it setup?

 

[dhookom]

I would try setting the sql property of QryRxAudit to return a specific group of field names. So, rather than change the control sources in the report, you are aliasing the field names in the query. This is similar to my solution on the web page.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[hangbill]

I'll give it a go, thanks.

But what is causing the error in my set-up? The subreport works fine when opened on its own but when its imbedded in the main report, get the 2191 error. Is it an Access design error that sends the report to preview before it has run all its code, including the code in the On Open event of the subreport?

 

[dhookom]

That's a limitation of subreports. I don't know if there is a way around it. And, to be completely honest, I don't have the time to experiment with different alternatives other than the ones that I know will work which I have suggested.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[hangbill]

Thank you Duane, I will work with your suggestions