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

 

[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.

 

[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