Error 2191 - You can't set the recordsource property after printing has started

[BJS]

I have some code, which opens a report containing a subreport. The recordsource for the subreport is dynamically set with the on_open event of the subreport.

On the click of a button on my form, the report opens and the subreport displays the correct information as per the recordsource.

BUT....

Now I have added one line of code after the report is opened:

This code works by itself...the subreport displays correct information when the report is opened

DoCmd.OpenReport "rptReturnToWorkNotice", acViewPreview

As soon as I add this line of code following the above line of code, I get "Runtime Error 2191 - You can't set the recordsource property after printing has started"

DoCmd.SendObject acSendReport, "rptMainReport", "Snapshot Format", Me.Email, , , Me.Subject & " - " & Me.ename, Me.Body, 0

I tried using the SendObject method in different places, but no luck.
Any ideas????

Thanks in advance,
BJS

 

[BJS]

As I continue to struggle with this, I have so far determined that it has to do with the SendObject method not working if I dynamically set the recordsource for the subreport.

The SendObject method works if I do not set the recordsource for the subreport dynamically.

Any ideas yet....anyone???

 

[BJS]

I got it working....after searching the internet for this error message; someone came up with this explanation:

"Access is probably firing the
Open event of the subreport twice.

The first occurance fires *before* the main report opens, so any attempt to
reference anything in the main report fails.

The second occurance fires after the report is already in progress (as
non-sensical as that sounds), and so attempts to assign the recordsource in
this 2nd instance of Report_Open fails."

The solution was to create a global variable as a flag to indicate the main report was opened. Then on the On_Open Event of the subform, test the variable to see if the report was already open, in which case the recordsource for the subreport was already set. By doing this, it does not try to set the recordsource for the subreport again, and has no issue executing the SendObject method.

I hope this helps others who may run across this same error message.
I am a happy camper!

BJS

 

[BJS]

I got it working....after searching the internet for this error message; someone came up with this explanation:

"Access is probably firing the
Open event of the subreport twice.

The first occurance fires *before* the main report opens, so any attempt to
reference anything in the main report fails.

The second occurance fires after the report is already in progress (as
non-sensical as that sounds), and so attempts to assign the recordsource in
this 2nd instance of Report_Open fails."

The solution was to create a global variable as a flag to indicate the main report was opened. Then on the On_Open Event of the subform, test the variable to see if the report was already open, in which case the recordsource for the subreport was already set. By doing this, it does not try to set the recordsource for the subreport again, and has no issue executing the SendObject method.

I hope this helps others who may run across this same error message.
I am a happy camper!

BJS

 

[associates]

Hi BJS,

I hope you still visit this forum or anyone might be able to help me. I have the same problem as BJS with the following error

runtime error '2191' - you can't set the control source property in print preview or after printing has started.

and tried to put his solution into code. I got confused with declaring the global variable in VBA. Because it didn't work.

Here is my code for the Main Report

Option Compare Database
Public myOpenFlag as boolean

Private Sub Report_Open(Cancel As Integer)
 myOpenFlag = True
 msgbox "Pass Main Report"
End Sub

Here is the code for Sub-Report

Private Sub Report_Open(Cancel As Integer)

 If myOpenFlag = True Then
    MsgBox "openFlag true"
 Else
    MsgBox "openFlag false"
 End If
 
 YearBase$ = Form_CHW_Startup.CB_Year.Value
 YearValue = Val(Mid$(YearBase$, 3, 2))
 YearInit$ = Left$(YearBase$, 2)
 
 If openFlag = False Then
 CurrentYear$ = YearInit$ + Right$("00" + Trim(Str$(YearValue)), 2) + "/" + Right$(Trim(Str$(YearValue + 1)), 2)
 Me.TX_Year1.Caption = CurrentYear$
 Me.TB_Year1.ControlSource = CurrentYear$
 Me.TB_Year1_Sum.ControlSource = "=sum([" + CurrentYear$ + "])"

 YearValue = YearValue + 1
 CurrentYear$ = YearInit$ + Right$("00" + Trim(Str$(YearValue)), 2) + "/" + Right$(Trim(Str$(YearValue + 1)), 2)
 Me.TX_Year2.Caption = CurrentYear$
 Me.TB_Year2.ControlSource = CurrentYear$
 Me.TB_Year2_Sum.ControlSource = "=sum([" + CurrentYear$ + "])"

I got "Pass Main Report" messagebox but then I got "openFlag false" messagebox when it should be "openFlag true". I think my global variable doesn't work.

Can anyone help me out please?

Thank you in advance

 

[The_Doc_Man]

This is a guess.

Once the report starts printing, all reports and subreports are opened because of the desire (by Access) to protect data via locking. Well, when you attempt to explicitly open the sub-form with what appears to be a filter, you can't - because it is locked. EITHER leave the sub-form unbound and dynamically bind it - or don't impose a filter from code. The issues is likely to be a binding-time inconsistency.