Cant set controlsource on-the-fly

[Razor]

Hi,

In my report, I have 11 textboxes named "txt1"
through "txt11" whose ControlSource is being set
dynamically as shown in the code below.

However, I get Run-time error 2191:
"You can't set the controlsource property after printing
has started."

********************************
Private Sub SubProductHeader_Format(Cancel As Integer,
FormatCount As Integer)
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "SELECT * FROM tblDisplayFields WHERE
SubProductCode = '" & Me.Sub_Product_Code & "' ORDER BY
ListOrder;"
SetLblFalse
SetTxtFalse

Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly,
dbReadOnly)

Do Until rs.EOF
Dim intCounter As Integer
intCounter = 1
Me("lbl" & intCounter).Visible = True
Me("lbl" & intCounter).Caption = rs!FieldName

With Me("txt" & intCounter)
.Visible = True
.ControlSource = rs!FieldName '<-- offending line
End With

intCounter = intCounter + 1
rs.MoveNext
Loop

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

Private Sub SetTxtFalse()
Dim intCounter As Integer
For intCounter = 1 To 11
Me("txt" & intCounter).Visible = False
Next intCounter
End Sub

Private Sub SetLblFalse()
Dim intCounter As Integer
For intCounter = 1 To 11
Me("lbl" & intCounter).Visible = False
Next intCounter
End Sub
***********************************

Please advise!

Thanks,
Razor

 

[Marshall Barton]

In my report, I have 11 textboxes named "txt1"
through "txt11" whose ControlSource is being set
dynamically as shown in the code below.

However, I get Run-time error 2191:
"You can't set the controlsource property after printing
has started."

********************************
Private Sub SubProductHeader_Format(

[]

You can only set the ControlSource property in the report's
Open event.

 

[Razor]

Marsh,
Is there a workaround to solve my bigger problem of
reusing txtboxes depending on the changing subproductcode?
Thanks,
Razor

-----Original Message-----

In my report, I have 11 textboxes named "txt1"
through "txt11" whose ControlSource is being set
dynamically as shown in the code below.

However, I get Run-time error 2191:
"You can't set the controlsource property after printing
has started."

********************************
Private Sub SubProductHeader_Format(

[]

You can only set the ControlSource property in the report's
Open event.

 

[Marshall Barton]

Is there a workaround to solve my bigger problem of
reusing txtboxes depending on the changing subproductcode?

Sorry, but I don't understand what you mean by that.

Maybe you want to include all the text boxes needed for all
the codes and just make the ones you want to see Visible and
the others invisible?
--
Marsh
MVP [MS Access]

-----Original Message-----

In my report, I have 11 textboxes named "txt1"
through "txt11" whose ControlSource is being set
dynamically as shown in the code below.

However, I get Run-time error 2191:
"You can't set the controlsource property after printing
has started."

********************************
Private Sub SubProductHeader_Format(

[]

You can only set the ControlSource property in the
report's Open event.