Automate changes in textbox control sources

[Ron_dK]

Hi,

I have a form with a large number of text boxes.
Most of these textboxes are used to display calculated (date) data, i.e :

Controlsource of textbox1 : =DCount(" [Survey-Input-ID]","[Qry-analyze-input]","[Pname] like 'S' and [Survey-conducted] between #1/1/2005# and #1/31/2005# and [Etype] like 'N' ")

Controlsource of textbox 12 :
=DCount(" [Survey-Input-ID]","[Qry-analyze-input]","[Pname] like 'A' and [Survey-conducted] between #11/1/2005# and #11/30/2005# and [Etype] like 'A' ")

I need to change the date(s) in these expressions to get all the 2006 data.
So 1/1/2005 should be 1/1/2006 etc.

Is there a way to automate the date changes in all textboxes in one go, instead of changing dates one by one. ?

Would appreciate any pointer.

Ron

 

[Bat17]

not tested but I think this should work

Dim ctl As Control
For Each ctl In Me.Controls
    With ctl
        Select Case .ControlType
            Case acTextBox
                ctl.ControlSource = Replace(ctl.ControlSource, "/2005#", "/2006#")
        End Select
    End With
Next ctl

Peter

 

[Ron_dK]

Thanks for that Peter, that works.

There is only one but....
I have added the code to the On load event of the form. Opening the form gives me indeed 2006 data, but when I open the form in design view and go to the relevant textbox code it still shows :

=DCount(" [Survey-Input-ID]","[Qry-analyze-input]","[Pname] like 'S' and [Survey-conducted] between #2/1/2005# and #2/28/2005# and [Etype] like 'N' ")

I figured that the code would change the year to 2006 permanently.
Is there a way to do that.


Cheers, Ron

 

[Bat17]

try hitting the save button after the code has run. If that does not work I think we will have to do it from a module opening the form in design mode.

Peter

 

[Bat17]

looks like we nned to do it from a seperate module

Sub UpdateYear()
Dim ctl As Control
DoCmd.OpenForm "Form1", acDesign
'Loop through each control on the form to get its value.
For Each ctl In Forms("form1").Controls
    With ctl
        Select Case .ControlType
            Case acTextBox
               ctl.ControlSource = Replace(ctl.ControlSource, "/2005#", "/2006#")
        End Select
    End With
Next ctl
DoCmd.Close acForm, "form1", acSaveYes
End Sub

HTH

Peter

 

[Ron_dK]

I added the module as a new one.
I placed a button on another form and run the module thru the on click event.
Hit the button et voila, all expressions now include 2006 !

Thanks for that Peter.

Cheers, Ron

 

[Bat17]

Glad you have it sorted
Peter