Refrencing a variable

[Chris1977]

I am not a programmer by trade so i'm hoping some of you will be able to give me a hand.

I have a text field called txtdaysworked. In this field the workers weekly schedule is as follows XMTWTFX and so on depending on what days they have off. Apart from this there is a series of 7 text boxes, each corresponding to the days of the week and the hours the employee worked for that week. So for example there is txtSunHours, txtMonHours etc.

What i want is for each instance of XMTWTFX the txtMonHours, txtTueHours, txtWedHours, txtThuHours, txtFriHours fields will fill with a value of 8 for hrs worked. Obviously if they worked SMTWTXX i'd want all fields except txtFriHours and txtSatHours to fill with a value of 8.

I'm sure there is an easy way to do this such as declaring a variable for the days worked box and on every instance of that variable filling the approporiate text boxes but not being a programmer that is something i am fairly new at. so if you guys can help me with the code and where to put it i'd really appreciate it.

thanks,
Chris

 

[kraxmo]

A couple of ways:

1. Set the Default Value property of the form's underlying RecordSource table columns = 8

2. Set each control's Default Value property = 8.

3. Run the following code:

    Function x()
        On Error Resume Next
        Dim ctl As Control
        For Each ctl In Me.Controls
            If Right(ctl.Properties(1).Value, 5) = "Hours"  Then
                ctl = 8
            End If
        Next
    End Function

This function reads through all of the current form's controls at run time. It assumes that all control names that end with "Hours" are valid. Jim Kraxberger
Developing Access solutions since 1995

 

[Chris1977]

Thanks for the reply Jim.

I also thought of the default value option but each employee may have a different value in their txtDaysWorked field. If you work Mon-Fri you have XMTWTFX but if you work WED-SUN you have SXXWTFS.

What i want to have happen is when i load the attendance form and the txtDaysWorked field says, for example, XMTWTFX, the txtSunHours, txtMonHours etc will automatically fill in with a value of 8.......and this will change based on the value in the txtdaysworked field. Would declaring that field as a string make this any easier to refrence and complete?

thanks

 

[kraxmo]

Chris,

You probably need to use a SELECT CASE statement to properly format the fields every time the employee changes:

Sub Form_Current()
    Dim intDay As Integer
    Dim intHrs As Integer

    'Format each employee's daily work hours
    For intDay = 1 To 7 
        'Determine hours by day position code
        If Mid(Me!txtDaysWorked, intDay, 1) = "X" Then
            intHrs = 0 'No work
        Else
            intHrs = 8 'Workday
        End If

        'Apply hours to specific workday control
        Select Case intDay
            Case 1 'Sunday
                Me!txtSunHours = intHrs
            Case 2 'Monday
                Me!txtMonHours = intHrs
            Case 3 'Tuesday
                Me!txtTueHours = intHrs
            Case 4 'Wednesday
                Me!txtWedHours = intHrs
            Case 5 'Thursday
                Me!txtThuHours = intHrs
            Case 6 'Friday
                Me!txtFriHours = intHrs
            Case 7 'Saturday
                Me!txtSatHours = intHrs
        End Select
    Next intDay
End Sub

Jim Kraxberger
Developing Access solutions since 1995