Find & Replace in Report control source

[azu_daioh]

I have a report with over 300 unbound text box with the following
control source:
=DCount("[EntryDate]","[ID Flag Imported]","[FO_ID]=1 And
Year([EntryDate])=[GetYear] And Month([EntryDate])=4")


Now I need to change [ID Flag Imported] to [ID Flag]

Is there an easier way to change all the unbound text boxes control
source without doing it one at a time? I tried one of the codes I
found here but I couldnt get it to work

-----------------
Dim ctl As Control, strReportName As String
strReportName = "rptReportName"
DoCmd.OpenReport strReportName,acViewDesign,,,acHidden
For Each ctl In Reports(strReportName).Controls
If InStr(ctl.ControlSource, "Forms!Form1") > 0 Then
ctl.ControlSource = Replace(ctl.ControlSource, "Forms!Form1",
"Forms!Form2")
End If
Next
DoCmd.Close acReport, strReportName, acSaveYes
---------------------

The name of my report is "Monthly ID Flag count by FO"

so I replaced "rptReportName" by "Monthly ID Flag count by FO"
replaced "Forms!Form1" with "[ID Flag Imported]"
replaced "Forms!Form2" with "[ID Flag]"

and I pasted the code after I clicked the "code" tool from the report
design view

Please help. I have 2 reports I need to replace the control source and
the other is another 300+ text boxes.

Thank you,
Sharon

 

[azu_daioh]

I'm getting this error message:

Run Time error 438, Object doesnt support this property or method

 

[Douglas J. Steele]

Take a look at these 3rd party products (arranged in order of price)

Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/
Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html

 

[azu_daioh]

Thanks. But unfortunately, our admin does not allow us to install
anything at work.

 

[Brendan Reynolds]

The code you posted earlier looks to me like it should work. Perhaps if you
post your modified version, and describe what happens when you run it,
someone might be able to see what the problem is.

 

[mdstrobel]

(e-mail address removed)

It has only been a little over six years since your original post, but I came across the same issue with a recent database that required fairly extensive rewritting of report control sources. What I discovered is that any control that does not have a "ControlSource" property caused the code to error out. I simply added an additional if-then statement to limit the For-Next loop to evaluate contols of the "acTextBox " type.

Here is what worked for me.

Public Sub Find_and_Replace_Report(ByRef vReport, vOldString, vNewString)
Dim ctl As Control
Dim strReportName As String
Dim vNumControls As Integer

strReportName = vReport

DoCmd.OpenReport strReportName, acViewDesign, , , acHidden

For Each ctl In Reports(strReportName).Controls

If ctl.ControlType = acTextBox Then
If InStr(1, ctl.ControlSource, vOldString, 1) > 0 Then
ctl.ControlSource = Replace(ctl.ControlSource, vOldString, vNewString)
End If
End If
Next

DoCmd.Close acReport, strReportName, acSaveYes

End Sub

Probably too late for your specific issue, but hopefully this will help others...

- mark