| Private Sub cmdSubmit_Click() | ||||
|---|---|---|---|---|
| Dim iRow As Long | ||||
| Dim ws As Worksheet | ||||
| Dim ws2 As Worksheet | ||||
| Set ws = Worksheets("Prod") | ||||
| Set ws2 = Worksheets("Inventory") | ||||
| 'find first empty row in database | ||||
| iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 | ||||
| 'checks for values in important positions (Could cause erros and crash program) | ||||
| If Trim(Me.tbDate.Value) = "" Then | ||||
| Me.tbDate.SetFocus | ||||
| MsgBox "Please complete the form" | ||||
| Exit Sub | ||||
| End If | ||||
| If Trim(Me.tbStartTime.Value) = "HH:MM" Then | ||||
| Me.tbStartTime.SetFocus | ||||
| MsgBox "Enter a Start Time" | ||||
| Exit Sub | ||||
| End If | ||||
| If Trim(Me.tbEndTime.Value) = "HH:MM" Then | ||||
| Me.tbEndTime.SetFocus | ||||
| MsgBox "Enter a End Time" | ||||
| Exit Sub | ||||
| End If | ||||
| If Trim(Me.tbFGSpools.Value) = "" Then | ||||
| Me.tbFGSpools.SetFocus | ||||
| MsgBox "If no spools were used enter '0'" | ||||
| Exit Sub | ||||
| End If | ||||
| If Trim(Me.tbJSpools.Value) = "" Then | ||||
| Me.tbJSpools.SetFocus | ||||
| MsgBox "If no spools were used enter '0'" | ||||
| Exit Sub | ||||
| End If | ||||
| 'copy the data to the database | ||||
| ws.Cells(iRow, 4).Value = Me.tbDate.Value | ||||
| ws.Cells(iRow, 5).Value = Me.tbSerial.Value | ||||
| ws.Cells(iRow, 6).Value = Me.cbQuality.Value | ||||
| 'The row calulates the total process time in minutes | ||||
| ws.Cells(iRow, 8).Value = DateDiff("n", Me.tbStartTime.Value, Me.tbEndTime.Value) | ||||
| ws.Cells(iRow, 19).Value = Me.tbLiner.Value | ||||
| ws.Cells(iRow, 20).Value = Me.tbFGTape1.Value | ||||
| ws.Cells(iRow, 21).Value = Me.tbJTape1.Value | ||||
| 'Subtracts how many FG spools from the inventory sheet | ||||
| If Me.tbFGSpools.Value >= 1 Then | ||||
| ws2.Range("B2") = Range("B2") - Me.tbFGSpools.Value | ||||
| Else: ws2.Range("B2") = Range("B2") | ||||
| End If | ||||
| 'Subtracts how many J spools from the inventory sheet | ||||
| If Me.tbJSpools.Value >= 1 Then | ||||
| ws2.Range("B3") = (Range("B3") - Me.tbJSpools.Value) | ||||
| Else: ws2.Range("B3") = Range("B3") | ||||
| End If | ||||
| 'Message box to tell you data has been added | ||||
| MsgBox "Data added", vbOKOnly + vbInformation, "Data Added" | ||||
| 'clear the data | ||||
| Me.tbDate.Value = "" | ||||
| Me.tbSerial.Value = "" | ||||
| Me.cbQuality.Value = "" | ||||
| Me.tbStartTime.Value = "" | ||||
| Me.tbEndTime.Value = "" | ||||
| Me.tbLiner.Value = "" | ||||
| Me.tbFGTape1.Value = "" | ||||
| Me.tbJTape1.Value = "" | ||||
| Me.tbJSpools.Value = "" | ||||
| Me.tbFGSpools.Value = "" | ||||
| Me.tbDate.SetFocus | ||||
| Dim X | ||||
| X = 1 | ||||
| 'Subtracts 1 from the liner count | ||||
| ws2.Range("B1") = Range("B1") - X | ||||
| End Sub |