ISSUE IS RESOLVED! JW was a great help with this problem. JW was very patient
with me and persistant on getting this fixed. Thank you JW!!! I couldn't have
figured it out without you. My wife will be glad to get me away from the PC
for a while now. Here is the completed code for the entire form in case
someone else needs to see it.


Private Sub CommandButton1_Click()

End Sub

Private Sub cboInOut_Change()

End Sub

Private Sub cboType_Change()

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DVDCollection")
If Me.cmdAdd.Caption = "Update" Then
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(UCase(ws.Cells(iRow, 1).Value)) = _
UCase(Me.txtTitle.Value) Then
ws.Cells(iRow, 2).Value = Me.cboType.Value
ws.Cells(iRow, 3).Value = Me.cboInOut.Value
ws.Cells(iRow, 4).Value = Me.txtLoanto.Value
If Me.cboInOut.Value = "On Shelf" Then _
ws.Cells(iRow, 4).Value = ""
Me.txtTitle.Value = ""
Me.cboType.Value = ""
Me.cboInOut.Value = ""
Me.txtLoanto.Value = ""
Me.txtSearchbox.SetFocus
Me.cmdAdd.Caption = "Save"
End If
Next iRow
Else
For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If CStr(UCase(ws.Cells(iRow, 1).Value)) = _
UCase(Me.txtTitle.Value) Then
MsgBox "DVD Title already exists!!!"
'clear the data
Me.txtTitle.Value = ""
Me.cboType.Value = ""
Me.cboInOut.Value = ""
Me.txtLoanto.Value = ""
Me.txtTitle.SetFocus
Exit Sub
End If
Next iRow
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtTitle.Value
ws.Cells(iRow, 2).Value = Me.cboType.Value
ws.Cells(iRow, 3).Value = Me.cboInOut.Value
ws.Cells(iRow, 4).Value = Me.txtLoanto.Value
'clear the data
Me.txtTitle.Value = ""
Me.cboType.Value = ""
Me.cboInOut.Value = ""
Me.txtLoanto.Value = ""
Me.txtTitle.SetFocus
End If
End Sub


Private Sub cmdSearch_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DVDCollection")

For iRow = 2 To ws.Cells(2000, 1).End(xlUp).Row
If UCase(ws.Cells(iRow, 1)) = UCase(Me.txtSearchbox.Value) Then
Me.cmdAdd.Caption = "Update"
Me.txtTitle.Value = ws.Cells(iRow, 1).Value
Me.cboType.Value = ws.Cells(iRow, 2).Value
Me.cboInOut.Value = ws.Cells(iRow, 3).Value
Me.txtLoanto.Value = ws.Cells(iRow, 4).Value
Me.txtSearchbox.Value = ""
Exit For
End If
Next iRow
If UCase(ws.Cells(iRow, 1)) = _
Me.txtTitle.Value Then
Me.txtSearchbox.Value = ""
MsgBox "DVD Title not found!!!"
Me.txtSearchbox.SetFocus
End If
Exit Sub

End Sub

Private Sub UserForm_Click()

End Sub