Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2908
  • Last Modified:

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

I have about 250+ text boxes on a report that contain DLOOKUP or DSUM which reference the report name.  Sample:

=IIf(DLookUp("[DTyp]","[Date_t]","[Date] = #" & Format(Reports!rptSched45Days_v1!Now1,"dd-mmm-yy") & "#")='T',"T",IIf(DLookUp("[DTyp]","[Date_t]","[Date] = #" & Format(Reports!rptSched45Days_v1!Now1,"dd-mmm-yy") & "#")='H',"H",""))

My problem is that when I make a copy of the report and name it "rptSched45Days_v2", is there a way to use VBA (or some other method) to change the referenced report name in the "Control Source" for all 250+ text boxes on the report to match the new report name.  I hate to think about doing each "Control Source" one at a time.  I've researched this in EE and can't find any other questions of this nature.    As always, I appreciate your expert help.  Thank you.  George
Note:  There is no module to do an Edit>Replace, these are text boxes on the report.
0
gwflyer
Asked:
gwflyer
  • 8
  • 7
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
gwflyerAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gwflyerAuthor Commented:
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
0
 
gwflyerAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ? ...
0
 
gwflyerAuthor Commented:
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
0
 
gwflyerAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might try replacing the entire string, i.e.:

Replace ctl.ControlSource, "Reports!rptSched45Days_v1!Now1", Reports!rptSched45Days_v2!Now1"
0
 
gwflyerAuthor Commented:
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

0
 
grendel777Commented:
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!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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.
0
 
gwflyerAuthor Commented:
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
 
 
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must propogate your change back to the .ControlSource property. Sorry, I missed that in my original function.

Change the function as below:

Function ChangeSource()

  Dim rpt      As Report
  Dim ctl      As Control

  DoCmd.OpenReport "rptSched45Days_v1", acViewDesign

  Set rpt = Reports("rptSched45Days_v1")
  str1 = "rptSched45Days_v1"
  str2 = "rptSched45Days_v2"

  On Error Resume Next
    For Each ctl In rpt.Controls
        If ctl.ControlType = acTextBox Then
            If Left(ctl.ControlSource, 12) = "=IIf(DLookUp" Then
              '/-------------  Change is below
              ctl.ControlSource = Replace(ctl.ControlSource, str1, str2)
            End If 'If Left(ctl.ControlSource, 12) = "=IIf(DLookUp"
        End If 'If ctl.ControlType = acTextBox
    Next

  DoCmd.Close acReport, "rptSched45Days_v2", acSaveYes

  Set rpt = Nothing
  Set ctl = Nothing
End Function
0
 
gwflyerAuthor Commented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now