Sub procedure won't use a variable to loop

Alc

Registered User.
Local time
Yesterday, 19:31
Joined
Mar 23, 2007
Messages
2,421
The following sub procedure has a problem with the line of code in red.

I have created a grid (12 rows by 31 columns) on a form and I'm using it to display bookings. Since the start period for each item being booked varies, which month comes first - and so, comes at the top of the form - will also vary. This means the number of cells in each row needs to change to suit the month label for the row. All of this is working just fine.

The problem I have is with the lines of code in red, in the following sub procedure. In brief, this code:
1) Loops through all controls on a form
2) Checks if a control is a textbox
3) Checks if the textbox name contains an identifying letter
4) Where the value in the textbox is within a certain date range, highlights the box in yellow

As I say, all is working well, apart from that one part in red.

For example, if I pass in a value of liMaxDay = 28 (for February), the code will highlight up to day 24 for that month and no higher. However, if I change the line and hardcode in 28, instead of using the variable, all boxes up to 28 get highlighted.
I've used a message box to check that liMaxDay is set to the correct value, both before and after the problem lines.
If I remove the red lines completely, all text boxes up to 31 are highlighted, which is obviously not what I need.

I'm hoping I'm missing something obvious here (as is my wont).

Any ideas?
Code:
 Sub Format_Selected_Dates(liMaxDay, strRefLetter As String, liStartDay As Integer, liEndDay As Integer)
    Dim ctl As Control
    Dim strEndLetter As String
    
    If liStartDay <= liEndDay Then
        For Each ctl In Me.Controls
            If Right(Left(ctl.name, 4), 1) = strRefLetter Then
                If ctl.ControlType = acTextBox Then
                    If ctl.Value >= liStartDay Then
                        If ctl.Value <= liEndDay Then
                            If ctl.ForeColor <> vbWhite Then
                                Call Format_Selected_Date(ctl)
                            End If
                        End If
                    End If
                End If
            End If
        Next ctl
    Else
        For Each ctl In Me.Controls
            If Right(Left(ctl.name, 4), 1) = strRefLetter Then
                If ctl.ControlType = acTextBox Then
                    If ctl.Value >= liStartDay Then
                      [COLOR=red]  [B]If ctl.Value <= liMaxDay Then[/B][/COLOR]
                            Call Format_Selected_Date(ctl)
                        [COLOR=red][B]End If
[/B][/COLOR]                   End If
                End If
            End If
        Next ctl
        strEndLetter = Increment_Letter(strRefLetter)
        For Each ctl In Me.Controls
            If Right(Left(ctl.name, 4), 1) = strEndLetter Then
                If ctl.ControlType = acTextBox Then
                    If ctl.Value <= liEndDay Then
                        Call Format_Selected_Date(ctl)
                    End If
                End If
            End If
        Next ctl
    End If
End Sub
 
on your function qualify liMaxDay:

Sub Format_Selected_Dates(liMaxDay As Integer, strRefLetter As String, liStartDay As Integer, liEndDay As Integer)
 
I see three places I would look further.
1) In the signature of the procedure the liMaxDay parameter is not strongly typed . . .
Code:
Sub Format_Selected_Dates([COLOR="Red"]liMaxDay[/COLOR], strRefLetter As String, liStartDay As Integer, liEndDay As Integer)
. . . so that might not be the type of data you think it is.

2) Do you need to do this qualification, as you do in the first loop, before you make the function call . . .
Code:
                            If ctl.ForeColor <> vbWhite Then

3) Should this loop leverage liMaxDay, not liEndDay?
Code:
        strEndLetter = Increment_Letter(strRefLetter)
        For Each ctl In Me.Controls
            If Right(Left(ctl.name, 4), 1) = strEndLetter Then
                If ctl.ControlType = acTextBox Then
                    If ctl.Value <= [COLOR="Red"]liEndDay[/COLOR] Then
                        Call Format_Selected_Date(ctl)
                    End If
                End If
            End If
        Next ctl
    End If
End Sub
 
Why don't people use ByVal & ByRef in front of their parameters >:O. Call me OCD or RCD but it zaps my brain every time I seem them missing. lol
 
Thanks, gentlemen.

Once again, it was picked up immediately by far keener minds than mine.
aAding "as Integer" to
Code:
 Sub Format_Selected_Dates(liMaxDay [B][U]As Integer[/U][/B], strRefLetter As String, liStartDay As Integer, liEndDay As Integer)
was exactly what was required.

Time for a break, I think.
 

Users who are viewing this thread

Back
Top Bottom