report record source and control source help

[scoob8254]

hi everybody

i ned to make a reports record source and the control sources for a few text boxs a bit more dynamic,

i have a report with 27 txt boxs going accross, the control source for these will change as they are named after dates, depending on a value set in a form.. they are named w1 to w27 .. w1 will ALWAYs be the value in the txtbox startgantt on the form, w2 will be the same value + 7 , and w3 will be the same value + 14 etc, so they each box is an increment of one week, so the control source for w1 would be for example 15/11/2010 and the control source for w2 would be 22/11/2010 and so on and so on in 7 day increments. the labels would just be a copy of the control source, the date

ive wrote some quick code but i never usualy use reports and im a bit lacking in experience in their use.. the code i wrote is quick and simple but i think should change the forms record source and should change the control sources and labels for the 27 txt boxs.. it obviously doesnt work

can somebody help ?

thanks in advance for any advice
scoobs

Private Sub Report_Open(Cancel As Integer)
Dim stSQL As String
Dim dCdate As Date
Dim count As Double
dCdate = [Forms]![Form1]![ganttstart]
stSQL = "Select [fldTrainee], [fldCourse], [" & dCdate & "]"
Me.w1_Label.Caption = dCdate
For i = 1 To 26
dCdate = dCdate + 7
stSQL = stSQL & ", [" & dCdate & "]"
Next i
stSQL = stSQL & " From qryGantt"
Me.RecordSource = stSQL
dCdate = [Forms]![Form1]![ganttstart] + 7
Me.w2_Label.Caption = dCdate
Me.w2.ControlSource = dCdate
dCdate = dCdate + 7
Me.w3_Label.Caption = dCdate
Me.w3.ControlSource = dCdate
dCdate = dCdate + 7
Me.w4_Label.Caption = dCdate
Me.w4.ControlSource = dCdate
dCdate = dCdate + 7
Me.w5_Label.Caption = dCdate
Me.w5.ControlSource = dCdate
dCdate = dCdate + 7
Me.w6_Label.Caption = dCdate
Me.w6.ControlSource = dCdate
dCdate = dCdate + 7
Me.w7_label.Caption = dCdate
Me.w7.ControlSource = dCdate
dCdate = dCdate + 7
Me.w8_Label.Caption = dCdate
Me.w8.ControlSource = dCdate
dCdate = dCdate + 7
Me.w9_Label.Caption = dCdate
Me.w9.ControlSource = dCdate
dCdate = dCdate + 7
Me.w10_Label.Caption = dCdate
Me.w10.ControlSource = dCdate
dCdate = dCdate + 7
Me.w11_Label.Caption = dCdate
Me.w11.ControlSource = dCdate
dCdate = dCdate + 7
Me.w12_Label.Caption = dCdate
Me.w12.ControlSource = dCdate
dCdate = dCdate + 7
Me.w13_Label.Caption = dCdate
Me.w13.ControlSource = dCdate
dCdate = dCdate + 7
Me.w14_Label.Caption = dCdate
Me.w14.ControlSource = dCdate
dCdate = dCdate + 7
Me.w15_Label.Caption = dCdate
Me.w15.ControlSource = dCdate
dCdate = dCdate + 7
Me.w16_Label.Caption = dCdate
Me.w16.ControlSource = dCdate
dCdate = dCdate + 7
Me.w17_Label.Caption = dCdate
Me.w17.ControlSource = dCdate
dCdate = dCdate + 7
Me.w18_Label.Caption = dCdate
Me.w18.ControlSource = dCdate
dCdate = dCdate + 7
Me.w19_Label.Caption = dCdate
Me.w19.ControlSource = dCdate
dCdate = dCdate + 7
Me.w20_Label.Caption = dCdate
Me.w20.ControlSource = dCdate
dCdate = dCdate + 7
Me.w21_Label.Caption = dCdate
Me.w21.ControlSource = dCdate
dCdate = dCdate + 7
Me.w22_Label.Caption = dCdate
Me.w22.ControlSource = dCdate
dCdate = dCdate + 7
Me.w23_Label.Caption = dCdate
Me.w23.ControlSource = dCdate
dCdate = dCdate + 7
Me.w24_Label.Caption = dCdate
Me.w24.ControlSource = dCdate
dCdate = dCdate + 7
Me.w25_Label.Caption = dCdate
Me.w25.ControlSource = dCdate
dCdate = dCdate + 7
Me.w26_Label.Caption = dCdate
Me.w26.ControlSource = dCdate
dCdate = dCdate + 7
End Sub

 

[scoob8254]

the albels are being updated ok. seems to be just the control source, is it because im trying to update them to dCdate which is a date ?

 

[pbaldy]

This is a shot in the dark, but see if this works:

Me.w2.ControlSource = "[" & dCdate & "]"

 

[pbaldy]

By the way, you can use a For/Next loop for all of those lines too:

Me("w" & i).ControlSource = "[" & dCdate & "]"

 

[scoob8254]

sure i had tried that last night pbaldy, but i must of messed it up somehow because that works perfect...

cheers for the idea about the control source loop, i had always planned to do that, was just worried would never get it to work

thanks again, you've saved my bacon several times now

scoobs

 

[pbaldy]

Happy to help scoobs.