For... Each... Next

[leclair]

Hello,
I am really hoping to receive help in this area.

Here is some background info.

I have a form based on a query.

The Query is based on a table with 18 task fields all having 18 different dates. The dates are used to determine if a particular task has been completed or not. There are 18 tasks. Each record is a person that has 18 tasks to complete. All tasks must be completed within a years time. If not the person is considered "Overdue"

Query is using the dates within the 18 separate task fields and calculating the number of days to today's date.


Now the form I am using has for each record a person that has 18 task fields within textboxes that are populated with the results from the calculations of the above query. I am looking to use a For...Each...Next statement to cycle through all 18 textboxes (for each record) and determine if any of the 18 textboxes (for each record) are above 365 days. If so I want to use a label that will state "Overdue". If any of the 18 textboxes (for each record) are over 365 days then the label still states "Overdue"
If all 18 textboxes (for each record) are under 365 days then I want the label to state "Current"

This is an example of the code I am trying to use:

Private Sub Form_Load()
Dim myTextBox As TextBox

For Each myTextBox In Controls
If myTextBox > 365 Then
labelCurrent.Caption = "Overdue"
Else: labelCurrent.Caption = "Current"
End If
Exit For
Next

End Sub

I can't get this to work!! I have tried other combinations but to no avail. I am a new VB programmer and I am trying to learn. Is there anyone that can help me with this problem? Also, if you need more info I am glad to provide it. Thank

Mike

 

[vb5prgrmr]

Try This

Option Explicit

Private Sub Command1_Click()

Dim T As Control

For Each T In Me.Controls
If TypeOf T Is TextBox Then
If Val(T.Text) > 365 Then
Label1.Caption = "Overdue"
Exit Sub
End If
End If
Next

Label1.Caption = "Current"

End Sub

now if you have a label for each textbox then it may be easier to have an array of textboxes and labels like this

Dim I as Integer

For I = 0 to 17

If Val(Text1(I).Text) > 365 Then

Label1(I).BackColor = vbRed
Label1(I).Caption = "OverDue"

Else

Label1(I).BackColor = vbGreen
Label1(I).Caption = "Current"

End If

Next I

 

[leclair]

I Get this Runtime Error (2185)

'You can't reference a property or a method for a control unless the control has the focus'

I goto debug and it references this line...

If Val(T.Text) > 365 Then

What do you think??

Mike

 

[vb5prgrmr]

Are you in Access???

Try
T.SetFocus

 

[leclair]

Yes I am in Access.

I tried what you recommended and I get another Runtime Error (91)

'Object Variable or With Block Variable not set'

and it references to
T.Setfocus

 

[vb5prgrmr]

Here is the code (IN ACCESS) that I used to test buttons for your problem.


Private Sub Command8_Click()

On Error GoTo ErrH

Dim T As Control

For Each T In Me.Controls
If TypeOf T Is TextBox Then
T.SetFocus
If Val(T.Text) > 365 Then
Label10.Caption = "Over Due"
Exit Sub
End If
End If
Next

Label10.Caption = "Current"

Exit Sub
ErrH:

MsgBox Err.Description

End Sub

This worked fine for me and I am using Access 2000 I don't know what else to do for you.

Good Luck

 

[leclair]

vb5prgrmr

how do I take your code and allow it to look through the text boxes of a subform from the main form...I attached your code to a click event first going to the next record, and then executing your code. That form (which is now the subform of another form) has all the code associating with it but when I use the Nav Bar of the main form the code you have given to me will not execute as it is pertaining to the subform not the main form. I hope you understand my issue...also your code works great!!!! just need help tweaking it to work within a Parent Child form relationship

 

[vb5prgrmr]

Ok, I have a solution, finally.

Add a module to your database and place something like this in it then call from the click event of your button.

Public Function IsLonger() As Boolean

Dim T As Control, F As Form

Set F = Form_Form2 'This is your sub form

For Each T In F.Controls

If TypeOf T Is TextBox Then

T.SetFocus

If Val(T.Text) > 365 Then

IsLonger = True
Exit Function

End If

End If

Next

End Function


So In your command buttons click event you could place code somewhat simular to this

Dim Test As Boolean

Test = IsLonger

If Test = True Then

Label1.BackColor = vbRed 'must set Back Style = Normal
Label1.Caption = "Overdue"

Else

Label1.BackColor = vbGreen
Label1.Caption = "Current"

End If




I hope this helps

 

[PaulF]

I cannot remember, but isn't there something funny in Access, where if you use the term A = Text1.Text it needs focus but if you say A = Text1 is doesn't. Maybe It is something else in the back of my mind but see if it does anything. Good luck

 

[Bstrick75]

Try using the Immediate If function. I know this will work for reports but I'm pretty sure it should work in your form. Under properties, find the section for the defult value.

Set it to :
=IIF([txtDate]>365,"Overdue",[txtDate.Value])

Do that for each of your 18 fields
Let me know if that works