Thanks. The code below is what I have so far as a Save command Button. It forces the user to enter the Loan Number and Investor Number which are the criteria. The third is a list box. Even if these two criteria are met for the duplicate record it would be fine. How would I insert the clone method into this code which generates on a save command click. Thanks for the help in advance.
Code:
Private Sub Save_Record_Click()
Dim SQL As String
If IsNull(Me!LoanNumber.Value) Then
MsgBox "Please Enter Loan Number", vbOKOnly, "Required Field Missing"
Cancel = True
GoTo Err_Save_Record_Click
End If
If IsNull(Me!InvestorLoanNumber.Value) Then
MsgBox "Please Enter Investor Loan Number", vbOKOnly, "Required Field Missing"
Cancel = True
GoTo Err_Save_Record_Click
End If
If IsNull(Me!LoanBalance.Value) Then
MsgBox "Please Enter Current Principal Balance Amount", vbOKOnly, "Required Field Missing"
Cancel = True
GoTo Err_Save_Record_Click
End If
If IsNull(Me!OrigDate.Value) Then
MsgBox "Please Enter Origination Date", vbOKOnly, "Required Field Missing"
Cancel = True
GoTo Err_Save_Record_Click
End If
If IsNull(Me!CustomerName.Value) Then
MsgBox "Please Enter Customer Name", vbOKOnly, "Required Field Missing"
Cancel = True
GoTo Err_Save_Record_Click
End If
SQL = "Update Investor_Request set Status = 'Closed' where Loan_Number = " & Me![Loan Number] & _
" AND status = 'Outstanding'"
CurrentDb.Execute SQL, dbFailOnError
On Error GoTo Err_Save_Record_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Err_Save_Record_Click:
Select Case Err
Case 3022
MsgBox "A record with this Loan Number already exists.", vbOKOnly, "Record Exists"
Case Else
Exit Sub
End Select
End Sub