Use VBA to Replace/Edit Control Source of Text Boxes on Report

You can build a routine that does this. Copy/paste the code below into a standard module, then call it like this from the Immediate window:

?ChangeSource("YourOldTableName", "YourNewTableName", "YourReportName")

This would also work to change the ReportName, even though it references TableName. Just to be sure, make a copy of your report before trying this ... I don't use functions as ControlSources (and you really shouldn't either, you'll run into trouble as more security comes down the pike) but this should work.

Function ChangeSource(OldTableName As String, NewTableName As String, ReportName As String)

Dim rpt As Report
Dim ctl As Control

DoCmd.OpenReport ReportName, acViewDesign

Set rpt = Reports(ReportName)

On Error Resume Next
For Each ctl In rpt.Controls
  If Left(ctl.ControlSource, 8) = "=Dlookup" Then
    Replace ctl.ControlSource, OldTableName, NewTableName
  End If
Next

DoCmd.Close acReport, ReportName, acSaveYes

End Function

Since I am trying to replace my OldReportName with a NewReportName (rather than a table name), do I still use the same format that you've provided.  Thanks  George

Yes, as I mention you can just substitute the old ReportName for the OldTableName:

ChangeSource "YourOldReportName", "YourNewReportName", "NameOfReportWhereYouWantChanges"

The FIRST argument is the name of the Report that you want to FIND in your DLOOKUP calls ...
The SECOND argument is the name you want to insert into your DLOOKUP calls ...

The LAST argument would be the name of the report where you want to make these changes ...

Thanks.  That helps a lot.  I wasn't sure whether I had to mention the table name and that clears it up.  
I almost have it ready to try and I'm a little confused where to type the "?ChangeSource...." in the open window.  I know this sound stupid, but do I type it below the "End Function", do I include the question mark (?), etc.  I've never done that before.  George

This is what I have so far.  I think I'm getting pretty close.   I'm running it from a command button using "CALL", but it's not working.  

Function ChangeSource(rptSched45Days_v1 As String, rptSched45Days_v2 As String)
Dim rpt As Report
Dim ctl As Control

DoCmd.OpenReport "rptSched45Days_v2", acViewDesign

Set rpt = Reports("rptSched45Days_v2")

On Error Resume Next
For Each ctl In rpt.Controls
  If Left(ctl.ControlSource, 12) = "=IIf(Dlookup" Then
    Replace ctl.ControlSource, "rptSched45Days_v1", "rptSched45Days_v2"
  End If
Next

DoCmd.Close acReport, "rptSched45Days_v2", acSaveYes

End Function

Define "not working" ... is your report being opened? If so, then you may need to verify if the If - Then line is being handled correctly ... you can do this by placing a breakpoint in the code before this line, then stepping through the code. Note that since you've got On Error Resume Next, the If statement is pretty much always run, but will only have an effect if the Replace actually succeeds.

Note also that since you're not using the Input parameters, you can remove them:

Function ChangeSource()

I added those in so this would be more of a generic code snippet ... if you're going to hardcode the values of the Report names, then there's no need for them.

And, you can call this in the Immediate Window, which is part of the VBE environment ... in the VBE window, click View - Immediate Window and you'll see a new window popup ... you can run code in that window by typing in the name, like this:

?ChangeSource

If you're running a Sub, then omit the ? ...

The "IF" statement is working.  I used a MsgBox to verify that it was finding each one.   I think my problem is in the Replace line (see below).  I thought it would find each reference in the control source (e.g. Reports!rptSched45Days_v1!Now1) and replace the "rptSched45Days_v1" with "rptSched45Days_v2", but it's not replacing it.  

For Each ctl In rpt.Controls
  If Left(ctl.ControlSource, 12) = "=IIf(Dlookup" Then
    Replace ctl.ControlSource, "rptSched45Days_v1", "rptSched45Days_v2"
  End If
Next

Do you know if there is a "Reference" that needs to be activated to make the Replace Function work.  I have played with this all weekend and it appears that my Replace Function is not working at all.  Thank you.  George

You might try replacing the entire string, i.e.:

Replace ctl.ControlSource, "Reports!rptSched45Days_v1!Now1", Reports!rptSched45Days_v2!Now1"

I just gave that a try and it still doesn't work.  I tested the REPLACE Function on a separate form and it works fine.  I am completely baffled why the REPLACE won't work in the ControlSource change.  There must be something simple that I'm overlooking.  

Is there another way to edit/replace in design view on a Report's text boxes?  Thank you.  George

Hi gwflyer, I know it's a little late, but I was just struggling with this problem myself and found a solution that worked for me.  First of all, if you remove the "On Error MoveNext" from the function above, you'll see that it's getting an error referring to the control.  I don't pretend to know why.

The good news is that there's an undocumented way to export a form or report as a simple text file, do a standard Find/Replace, and re-import it:

In the Immediate window type:
Application.SaveAsText acReport, "Reports!rptSched45Days_v1", "c:\rptText.txt"
and hit <Return>.  You'll now have the file c:\rptText.txt on your c-drive - open it in Notepad and do your find/replace.

In the Immediate window again, type:
Application.LoadFromText acReport, "Reports!rptSched45Days_v1", "c:\rptText.txt"
and hit <Return>.  THIS WILL REPLACE THE ORIGINAL FORM, so rename it if you need to.  It helps to keep a copy of the original .txt file, too, in case you need to start all over.

Hope that helps!

<you'll see that it's getting an error referring to the control.  I don't pretend to know why.>

You get an error because not every Control has a ControlSource property (for example, Labels).

SaveAsText and LoadFromText work well, but I'd be VERY leery of making changes while these are in Text form ... while the changes you suggest won't cause any problems, if that Replace function changes something else and you re-import, you can hopelessly corrupt your project (hence the reason why these are undocumented functions).

gwFlyer: Can you zip and post this database online? You can post it to www.ee-stuff.com if you have no other place. Also, make sure to post a link back here.

I really appreciate any help I can get with this problem.  I definitely need to find a solution.  I tried to save my report as a text file, but always get the error message:  "You canceled the previous operation".

As requested by LSMConsulting, I've uploaded my database to ee-stuff at the following link:
https://filedb.experts-exchange.com/incoming/ee-stuff/5259-ReplaceProblem.zip 

Thank you very much for your continued support and assistance.  George
 
 

You are fantastic!!!   It worked like a charm.  My boss just got back to work today and the timing could not be better.  Thank you very much.  George