Private Sub cmdAdd_Click()
 Dim lRow As Long
 Dim lPart As Long
 Dim ws As Worksheet
 Set ws = Worksheets("PartsData")
 
'find first empty row in database
 ''lRow = ws.Cells(Rows.Count, 1) _
 '' .End(xlUp).Offset(1, 0).Row
 
'revised code to avoid problems with
 'Excel lists and tables in newer versions
 lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
 SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

 lPart = Me.cboPart.ListIndex
 
'check for a part number
 If Trim(Me.cboPart.Value) = "" Then
 Me.cboPart.SetFocus
 MsgBox "Please enter a part number"
 End If
 If Trim(Me.cboLocation.Value) = "" Then
 Me.cboLocation.SetFocus
 MsgBox "Please enter the Location"
 Exit Sub
 End If
 
'copy the data to the database
 With ws
 .Cells(lRow, 1).Value = Me.txtDate.Value
 .Cells(lRow, 2).Value = Me.cboLocation.Value
 .Cells(lRow, 4).Value = Me.cboPart.Value
 .Cells(lRow, 5).Value = Me.cboPart.List(lPart, 1)
 .Cells(lRow, 9).Value = Me.txtQty.Value
 .Cells(lRow, 11).Value = Me.txtQty2.Value
 .Cells(lRow, 7).Value = Me.txtQty3.Value
 .Cells(lRow, 8).Value = Me.txtQty4.Value
 'new addition


 End With
 
'clear the data
 Me.cboPart.Value = ""
 Me.cboLocation.Value = ""
 Me.cboLocation2.Value = ""
 Me.txtDate.Value = Format(Date, "Medium Date")
 Me.txtQty.Value = ""
 Me.cboPart.SetFocus
 
End Sub