Loop through specific controls

[wrightyrx7]

Hi all,

Not sure how to do this but i think i first need to find out how to loop though specific controls on my form.

Basically i am trying to produce the description that our company uses for a working pattern.

Basically the description starts with the total hours for the week followed by "_"

As an example lets say someone works:
37 hours 30 minutes a week
Monday 7 hours 30 minutes
Tuesday 7 hours 30 minutes
Wednesday 7 hours 30 minutes
Thursday 7 hours 30 minutes
Friday 7 hours 30 minutes

would be 37.30_M-F7.30

or

15 hours a week
Monday 1 hours
Tuesday 2 hours
Wednesday 3 hours
Thursday 4 hours
Friday 5 hours

would be: 15_M1T2W3TH4F5


So if there are consecutive days with the same number of hours there is a "-" with the day from and day to followed by the hours.
13 hours a week
Monday 2 hours
Tuesday 2 hours
Wednesday 3 hours
Thursday 3 hours
Friday 3 hours

would be: 13_M-T2W-F3

So i need to think of a way to loop through the below to create the description

Any help would be great

 

[Ranman256]

This is not a good design for a database.
data should run vertical, not horizontal
dow, starttime, endtime
MON, 730, 5
TUE, 730, 5
etc.

if this is a 'temp' table for data entry, then just go to the box by name
msgbox txtMon

to cycle thru controls on a form:

for each ctl in controls
     if Typename(ctl)= "textbox" then msgbox ctl.name
next

 

[Uncle Gizmo]

I did a YouTube video recently about looping through controls. it's this one here:- Loop Through a Set of Controls you might find it useful...

 

[Uncle Gizmo]

Actually I don't think a control loop is what you need, I think you need a "For Next Loop", something like For x = 1 to 7 where the 1 to 7 would interrogate the Monday to Sunday text boxes in ascending order. Some mechanism for linking the "For Next Loop" to the text boxes would be required, probably store a number in the tag of the textbox.... So on the first iteration of the for next loop you would keep the value from textbox "1" and then compare it to textbox "2" and then the result(s) of that logic decide how you build up your string ...... Wouldn't really know how to go about it until I started.

 

[Mark_]

OP, you don't need to loop through controls as you have specific ones you want to check. You will need to have three variables declared for the form; SavedBeg, SavedEnd, and SavedDur. The first two would be strings, the last would need to match the type you use for holding durations.

You would default what ever control you are using to show this text (referenced below as Me.ShowText) to be your total duration followed by your underscore.

Me.ShowText = SumOfControls & "_"

Your logic for each checking each control is basically the same;

If Me.Monday_Duration <> "" Then                    'If we have a duration for Monday
   If Me.Monday_Duration = SavedDur Then       'AND its the same as the last saved duration
      SavedEnd = "M"                                              'Update the ending date to be this day.
   Else                                                                       'If its NOT the same, update the text to show
      IF SavedEnd =  "" Then                                   'either for just a day
         Me.ShowText = Me.ShowText & SavedBeg & Saved Dur
      Else                                                                    'OR with the start day AND the last day
         Me.ShowText = Me.ShowText & SavedBeg & "-" &SavedEnd & SavedDur
      End If
      SavedBeg = "M"                                              'Go ahead and save today for the next go
      SavedEnd = ""                                                   'Clear out the end day
      SavedDur = Me.Monday_Duration                 'and use the current duration for the next pass.
ELSE				          ' if NO duration,
      IF SavedEnd =  "" Then                                   'Either print the last one for one day
         Me.ShowText = Me.ShowText & SavedBeg & Saved Dur
      Else                                                                    'OR with the start day AND the last day
         Me.ShowText = Me.ShowText & SavedBeg & "-" &SavedEnd & SavedDur
      End If
      SavedBeg = ""                                                   'and empty out all saved variables 
      SavedEnd = ""
      SavedDur = ""
End If

Yes, you can make a function that you pass the current day and duration to so you don't have a bunch of redundant code, but this should get you started before you clean it up.

Hope this helps!

NOTE: Sorry, but its not letting me indent the above to make it look nice... Sorry.

 

[Uncle Gizmo]

I've been working on a slightly different approach to the one shown by Mark:-


'Store the iteration hours
'Store the iteration day
'
'(1) First iteration
'Compare the current iteration value with the "Stored" iteration hours
'On the first iteration the variable will be empty and there will be no match
'
'(2) Second iteration
'Compare the current iteration hours with the "Stored" iteration hours
'
'If they are the same (13 hours a week)
'And blnHyphenRequired Flag = false
'Concatenate "Stored" day (M)
'set Flag blnHyphenRequired = True
'(Hours update will be the same)
'
'If they are different (15 hours a week)
'Concatenate "Stored" day and "Stored" hours (M1)
'
'Update the iteration hours
'Update the iteration day
'
'(3) Third iteration
'Compare the current iteration hours with the previous "Stored" hours
'
'If they are the same (13 hours a week)
'Concatenate iteration day and a hyphen (M-)
'
'If they are different (15 hours a week)
'Concatenate iteration day (M1T) or (M-T)
'
'Update the iteration hours
'Update the iteration day

Private Function fY()
Dim Ctrl As Control
Dim strBuilder As String
Dim strSaveFirstHrs As String
Dim strSaveFirstDay As String
Dim strSavePrevious As String
Dim strSaveHrs As String
Dim strSaveDay As String

Dim X As Integer

For X = 1 To 7

      For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
            If Ctrl.Tag = X Then
                If Ctrl.Tag = 1 Then                                'Save the 1st Days details
                    strSaveFirstHrs = Ctrl.Value
                    strSaveFirstDay = Ctrl.Controls(0).Caption
                Else                                                'Not the 1st day, so work out what to Concatenate
                    If strSaveFirstHrs = Ctrl.Value Then            'The Hours are the same
                        strBuilder = strBuilder & strSaveFirstDay & " - "
                        strSaveFirstHrs = ""
                        strSavePrevious = Ctrl.Value
                    Else
                        If strSavePrevious = Ctrl.Value Then
                            strSaveDay = Ctrl.Controls(0).Caption
                            strSaveHrs = Ctrl.Value
                        Else
                            'Otherwise concatenate them
                            strBuilder = strBuilder & strSaveDay & " < " & strSaveHrs
                        End If

                    End If
                End If
            strSavePrevious = Ctrl.Value
            End If
         End If
    Next
Next X

Me.txtConcat = strBuilder


End Function

It still needs a bit of work..... The Logical description doesn't cover all the options and it was getting a bit convoluted... My next step would be to simplify it a bit

 

[wrightyrx7]

Thank you all for your replied. It has helped me to figure out the code i needed to get the required 'Description'.

I think the code I ended up with is a Frankenstein of both sets of code provided haha.

Would not of figured out without you guys.

Thank you again