I have a form with text boxes that represent the 52 weeks of the year. I would like to put hourly data representing a workweek of 40 hr in the appropriate box based on a start date determined by the user. My question is it possible to change the field name based on the date selected. In my case week1 of the build may start in week 12 of the year and the rest of the build with key off that start point.
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
Phase1 = Me.DesignHr.Value
Me.Build_Week1 = Phase1 (I would like to be able to change this based on date seleted i.e Me.Build_Week12)
Example of data
YearWeek-------------12-------13-------14
BuildWeek-------------1--------2--------3
DesignHr--------------40-------40-------10
CNCHr-----------------0---------0-------40
Code so far:
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
Phase1 = Me.DesignHr.Value
Me.Build_Week1 = Phase1 (I would like to be able to change this based on date seleted i.e Me.Build_Week12)
Example of data
YearWeek-------------12-------13-------14
BuildWeek-------------1--------2--------3
DesignHr--------------40-------40-------10
CNCHr-----------------0---------0-------40
Code so far:
Code:
Option Compare Database
Option Explicit
Private Sub Command6_Click()
'Var holder for Hour information'
Dim Phase1 As Integer
'Massage Box Var'
Dim iResponse As Integer
'Week Number Conversion'
Dim DesignComplete As Integer
DesignComplete = Format(Me.DesignCompDate.Value(), "ww")
Me.WeekNum = DesignComplete
'Design Schedule up to 200 hours'
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
Phase1 = Me.DesignHr.Value
Me.Build_Week1 = Phase1
Else
If (Me.DesignHr.Value > 40 And Me.DesignHr.Value < 80) Then
Phase1 = Me.DesignHr
Me.Build_Week1 = 40
Me.Build_Week2 = Phase1 - 40
Else
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 121) Then
Phase1 = Me.DesignHr
Me.Build_Week1 = 40
Me.Build_Week2 = 40
Me.Build_Week3 = Phase1 - 80
Else
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 161) Then
Phase1 = Me.DesignHr
Me.Build_Week1 = 40
Me.Build_Week2 = 40
Me.Build_Week3 = 40
Me.Build_Week4 = Phase1 - 120
Else
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 201) Then
Phase1 = Me.DesignHr
Me.Build_Week1 = 40
Me.Build_Week2 = 40
Me.Build_Week3 = 40
Me.Build_Week4 = 40
Me.Build_Week5 = Phase1 - 160
Else
If (Me.DesignHr.Value > 200) Then
Me.DesignHr = 0
iResponse = MsgBox("Can not Schedule over 200 Hours", vbOK)
End If
End If
End If
End If
End If
End If
End Sub
'================================================================================='
'================================END OF Design Code==============================='