Reference text box value using a string variable of text box name 2

[Poduska]

I am trying to loop through controls by creating a string of the control name, each loop makes a numeric value in the name advance. Something like "Control01" becomes "Control02" etc. I then use the value of this control to compare against the current date "Date" and change checkboxes if the item is "Open", "Due" or "Late"

Somehow my concantonated string name saved in a string variable is not referencing the form field in my IF statement below.

For example I have bolded one example in the code box called "txtPlan". If I break the code before the IF statement and place ?txtplan in the debug window I get

forms!frmdranddrbfmdates!dtDRDate02Plan

Which is the actual name of the form control.

If I then add a "?" in front of the value I just returned in the debug window such as

?forms!frmdranddrbfmdates!dtDRDate02Plan

I get 2/2/08 which is the actual value in the control so I am getting to the control.

[red]How do I modify my if statement below to reference the control and return a date as above? Currently it always falls through to the last else, apparently never really making the comparison to Date[/red]


THANKS

Private Sub Form_load()

Dim c As Integer
Dim n As String
Dim txtActual As String
Dim txtPlan As String
Dim ckOpen As String
Dim ckDue As String
Dim ckLate As String


n = ""
c = 1

Do While c < 13
If c < 10 Then n = "0" & CStr(c) Else n = CStr(c)
txtActual = "Forms!frmdranddrbfmdates!dtDRDate" & n & "Actual"
[b]txtPlan = "Forms!frmdranddrbfmdates!dtDRDate" & n & "Plan"[/b]
ckDue = "Forms!frmdranddrbfmdates!ckdrduedate" & n
ckLate = "Forms!frmdranddrbfmdates!ckdrlatedate" & n
ckOpen = "Forms!frmdranddrbfmdates!ckdropendate" & n


If Not (IsNull(txtActual)) Then
    If txtActual < Date Then ckLate = -1 Else
    If txtActual < Date + 14 Then ckDue = -1 Else ckOpen = -1
End If


c = c + 1
Loop
Me.Refresh

End Sub

 

[PWise]

is this what you want

for a = 0 to 25
depug.print forms!formname("control" & a)
next

 

[TheAceMan1]

How are ya Poduska . . .

Already shown by [blue]pwise[/blue], you only need to concatenate the textbox name. Try this:

[blue]   Dim frm As Form, c As Integer, n As String
   Dim txtActual As String, txtPlan As String
   Dim ckOpen As String, ckDue As String, ckLate As String

   Set frm = Forms!frmdranddrbfmdates
   c = 1

   Do While c < 13
      [b]n = Format(c, "00")[/b]
      txtActual = frm!(dtDRDate & n & "Actual")
      txtPlan = frm!(dtDRDate & n & "Plan")
      ckDue = frm!(ckdrduedate & n)
      ckLate = frm!(ckdrlatedate & n)
      ckOpen = frm!(ckdropendate & n)
      
      If Not (IsNull(txtActual)) Then
          If [purple][b]Cdate([/b][/purple]txtActual[purple][b])[/b][/purple] < Date Then ckLate = -1 Else
          If [purple][b]CDate([/b][/purple]txtActual[purple][b])[/b][/purple] < Date + 14 Then ckDue = -1 Else ckOpen = -1
      End If

      c = c + 1
   Loop

   Me.Refresh
   Set frm = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Poduska]

Thanks for everyone's help.

Working with your input and a few other posts this is what I came up with that works.

The problem is I was not enclosing my Variable String control name in ()'s. I noticed that TheAceMan1 also does this and it worked. Strange is that I used ME!(txtPlan) and access removed the bang, so no dot or bang, I may never figure that logic out.

Here is my code that works for anyone's reference. It selects one of three check boxes predicated upon a Plan date and an actual(completed) date.

Sub UpdateAllDRCheckBoxes()

Dim c As Integer
Dim n As String
Dim txtActual As String
Dim txtPlan As String
Dim ckOpen As String
Dim ckDue As String
Dim ckLate As String


n = ""
c = 1


Do While c < 13
If c < 10 Then n = "0" & CStr(c) Else n = CStr(c)
txtActual = "txtDRDate" & n & "Actual"
txtPlan = "txtDRDate" & n & "Plan"
ckDue = "ckdrduedate" & n
ckLate = "ckdrlatedate" & n
ckOpen = "ckdropendate" & n

Me(ckLate) = 0
Me(ckDue) = 0
Me(ckOpen) = 0


If Not (IsNull(Me(txtPlan))) And (IsNull(Me(txtActual))) Then
    If Me(txtPlan) < Date Then
        Me(ckLate) = -1
    Else
        If Me(txtPlan) <= Date + 14 Then
        Me(ckDue) = -1
            Else
        Me(ckOpen) = -1
        End If
    End If
End If

c = c + 1
Loop

Me.Refresh

End Sub

 

[TheAceMan1]

Poduska . . .

No you have it correct, I had it wrong (copied the wrong code):

[blue]   frm!(ckdrduedate & n)
should be:
   frm(ckdrduedate & n)[/blue]

Also did you not notice:

[blue][b]   n = Format(c, "00")[/b][/blue]

?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Poduska]

I did see your

n = Format(c, "00")

and at first thought it might work like Excel where you can format a "leading zero" but not actually change the number.

I tried it and I was wrong.... it has happend before just don't tell my kids!

I think it is a much more Elegant soluction that my if-then-else. Definatly easier to read.

With Access there is always many ways to do the same thing and you wonder "Why didn't I think of that!

Thanks for the trick.