Unbinding all a Reports ControlSources

[kagard]

Greetings:

I have a complex report that I want to use with a completely different
data source. How can I delete the control source from every text box
on the report without selecting each of the 100+ fields and changing
them manually?

I tried, unsuccessfully, to loop through all the controls on the
report with a for each loop looking for text boxes and then setting
their control source to "", but VBA didn't want to let me access the
control source property when the report wasn't open.

TIA

Keith

 

[Douglas J Steele]

So open the report in design view and make your changes.

Sub UnbindReport(NameOfReport As String)
On Error GoTo EH\

Dim rpt As Report
Dim ctl As Control

DoCmd.OpenReport NameOfReport, View:=acViewDesign, WindowMode:=acHidden
Set rpt = Reports(NameOfReport)
For Each ctl In rpt.Controls
If ctl.ControlType = acTextBox
ctl.ControlSource = vbNullString
End If
Next ctl
DoCmd.Close acReport, NameOfReport, acSaveYes

Cleanup:
Set ctl = Nothing
Set rpt = Nothing
Exit Sub

EH:
MsgBox Err.Number & ": " & Err.Description
Resume Cleanup

End Sub


"kagard" wrote in message

Greetings:

I have a complex report that I want to use with a completely different
data source. How can I delete the control source from every text box
on the report without selecting each of the 100+ fields and changing
them manually?

I tried, unsuccessfully, to loop through all the controls on the
report with a for each loop looking for text boxes and then setting
their control source to "", but VBA didn't want to let me access the
control source property when the report wasn't open.

TIA

Keith

 

[kagard]

Perfect! Seems obvious now, but my foggy Friday afternoon brain
couldn't come up with it. Thanks, Doug.

 

[kagard]

Perfect! Seems obvious now, but my foggy Friday afternoon brain
couldn't come up with it. Thanks, Doug.