Unbinding all a Reports ControlSources

  • Thread starter Thread starter kagard
  • Start date Start date
K

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
 
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
 
Perfect! Seems obvious now, but my foggy Friday afternoon brain
couldn't come up with it. Thanks, Doug.
 
Perfect! Seems obvious now, but my foggy Friday afternoon brain
couldn't come up with it. Thanks, Doug.
 
Back
Top