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:=xlColumns, 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