Hello All, I’m trying to construct a project with my limited ability, that will be used as a scheduling tool for machined parts manufacturing. The program needs to consider different phases of the build and be able to insert hours keyed off one phase ending then the next phase starting. I.e. Design then layout. It also needs to be a rolling time scale as the year progresses. I.e. in week 26 be able to look out to week 52. I want to have a combo box that the person can select the number of build weeks up to 25 and divided the hours accordingly. Also the whole process needs to start after the design complete date.
I have completed part of the code but realize that there has to be a more efficient way of doing this. I’m probably way off the mark on my approach. Any suggestions would be greatly appreciated.
This is the output I will need to see in 26 week blocks
I have completed part of the code but realize that there has to be a more efficient way of doing this. I’m probably way off the mark on my approach. Any suggestions would be greatly appreciated.
This is the output I will need to see in 26 week blocks
Code:
__________________Week Numbers
________________1____2___3___4____5
Est Hr
DESIGN__ 120 40 40 40 0 0
LAYOUT__ 80 0 40 40 0 0
CNC PROG 60 0 40 20 0 0
CNC MILL 80 0 0 40 40 0
GRIND___ 25 0 0 25 0 0
WIRE____ 40 0 0 40 0 0
EDM_____ 40 0 0 0 40 0
ASSY____ 40 0 0 0 40 0
TUNE-UP_ 20 0 0 0 20 0
TOTAL_HR 505 40 120 205 87 0
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
[code]
Code so far:
Option Compare Database
Option Explicit
Private Sub Command6_Click()
'Var holder for week information'
Dim Phase1 As Integer
Dim Phase2 As Integer
Dim Phase3 As Integer
Dim Phase4 As Integer
Dim Phase5 As Integer
Dim Phase6 As Integer
Dim Phase7 As Integer
Dim Phase8 As Integer
Dim Phase9 As Integer
Dim Phase10 As Integer
Dim Phase11 As Integer
Dim Phase12 As Integer
Dim Phase13 As Integer
Dim Phase14 As Integer
Dim Phase15 As Integer
Dim Phase16 As Integer
Dim Phase17 As Integer
'Massage Box Var'
Dim iResponse As Integer
'Week Number Conversion'
Dim WkNum As Integer
Dim strToday
strToday = Format(Now(), "ww")
Me.WeekNum = strToday
'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 = 40
Phase2 = (Me.DesignHr.Value - 40)
Me.Build_Week1 = Phase1
Me.Build_Week2 = Phase2
Else
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 121) Then
Phase1 = 40
Phase2 = 40
Phase3 = (Me.DesignHr.Value - 80)
Me.Build_Week1 = Phase1
Me.Build_Week2 = Phase2
Me.Build_Week3 = Phase3
Else
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 161) Then
Phase1 = 40
Phase2 = 40
Phase3 = 40
Phase4 = (Me.DesignHr.Value - 120)
Me.Build_Week1 = Phase1
Me.Build_Week2 = Phase2
Me.Build_Week3 = Phase3
Me.Build_Week4 = Phase4
Else
If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 201) Then
Phase1 = 40
Phase2 = 40
Phase3 = 40
Phase4 = 40
Phase5 = (Me.DesignHr.Value - 160)
Me.Build_Week1 = Phase1
Me.Build_Week2 = Phase2
Me.Build_Week3 = Phase3
Me.Build_Week4 = Phase4
Me.Build_Week5 = Phase5
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 OF Design Code==============================='
'Var holder for week information'
Dim layPhase1 As Integer
Dim layPhase2 As Integer
Dim layPhase3 As Integer
Dim layPhase4 As Integer
Dim layPhase5 As Integer
Dim layPhase6 As Integer
Dim layPhase7 As Integer
Dim layPhase8 As Integer
Dim layPhase9 As Integer
Dim layPhase10 As Integer
Dim layPhase11 As Integer
Dim layPhase12 As Integer
Dim layPhase13 As Integer
Dim layPhase14 As Integer
Dim layPhase15 As Integer
Dim layPhase16 As Integer
Dim layPhase17 As Integer
'Massage Box Var'
Dim iResponselay As Integer
'Layout Schedule'
If (Me.LayoutHr.Value > 0 And Me.LayoutHr.Value < 41) Then
layPhase2 = Me.LayoutHr.Value
Me.LayoutWk2 = layPhase2
Else
If (Me.LayoutHr.Value > 40 And Me.LayoutHr.Value < 80) Then
layPhase2 = 40
layPhase3 = (Me.LayoutHr.Value - 40)
Me.LayoutWk2 = layPhase2
Me.LayoutWk3 = layPhase3
Else
If (Me.LayoutHr.Value > 0 And Me.LayoutHr.Value < 121) Then
layPhase2 = 40
layPhase3 = 40
layPhase4 = (Me.LayoutHr.Value - 80)
Me.LayoutWk2 = layPhase2
Me.LayoutWk3 = layPhase3
Me.LayoutWk4 = layPhase4
Else
If (Me.LayoutHr.Value > 0 And Me.LayoutHr.Value < 161) Then
layPhase2 = 40
layPhase3 = 40
layPhase4 = 40
layPhase5 = (Me.LayoutHr.Value - 120)
Me.LayoutWk2 = layPhase2
Me.LayoutWk3 = layPhase3
Me.LayoutWk4 = layPhase4
Me.LayoutWk5 = layPhase5
Else
If (Me.LayoutHr.Value > 0 And Me.LayoutHr.Value < 201) Then
layPhase2 = 40
layPhase3 = 40
layPhase4 = 40
layPhase5 = 40
layPhase6 = (Me.LayoutHr.Value - 160)
Me.LayoutWk2 = layPhase2
Me.LayoutWk3 = layPhase3
Me.LayoutWk4 = layPhase4
Me.LayoutWk5 = layPhase5
Me.LayoutWk6 = layPhase6
Else
If (Me.LayoutHr.Value > 200) Then
Me.LayoutHr = 0
iResponse = MsgBox("Can not Schedule over 200 Hours", vbOK)
End If
End If
End If
End If
End If
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.Lab_BuildWk1.Caption = Format(Now(), "ww")
Me.LabBuildWk2.Caption = Format(Now(), "ww") + 1
Me.LabBuildWk3.Caption = Format(Now(), "ww") + 2
Me.LabBuildWk4.Caption = Format(Now(), "ww") + 3
Me.LabBuildWk5.Caption = Format(Now(), "ww") + 4
Me.LabBuildWk6.Caption = Format(Now(), "ww") + 5
Me.LabBuildWk7.Caption = Format(Now(), "ww") + 6
Me.LabBuildWk8.Caption = Format(Now(), "ww") + 7
Me.LabBuildWk9.Caption = Format(Now(), "ww") + 8
Me.LabBuildWk10.Caption = Format(Now(), "ww") + 9
Me.LabBuildWk11.Caption = Format(Now(), "ww") + 10
Me.LabBuildWk12.Caption = Format(Now(), "ww") + 11
Me.LabBuildWk13.Caption = Format(Now(), "ww") + 12
Me.LabBuildWk14.Caption = Format(Now(), "ww") + 13
Me.LabBuildWk15.Caption = Format(Now(), "ww") + 14
Me.LabBuildWk16.Caption = Format(Now(), "ww") + 15
Me.LabBuildWk17.Caption = Format(Now(), "ww") + 16
End Sub
