Marlene, in your case, I would give to the user the opportunity to change the amount, if it differs from the saved.
In an unbound form, with the corresponding fields and a button called "cmdSubmit", there could be a procedure like this:
Code:
Private Sub cmdSubmit_Click()
Dim strMsg As String
Dim rs As Recordset
'At this point need a validation code for properly filled fields of the form."
'For example:
'If Not IsReadyToSave then '"IsReadyToSave" could be a user defined function for required fields validation.
'MsgBox "You have to fill properly all required fields!", vbExclamation, "Financial Record"
'Exit Sub
'End If
On Error GoTo ErrH
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MainTable " _
& "WHERE FYear='" & Me.FYear & "' AND FPeriod='" & Me.FPeriod _
& "' AND CLng(FDate)=" & CLng(Me.FDate), dbOpenDynaset)
With rs
If (.BOF And .EOF) Then
'New record.
.AddNew
!FYear = Me.FYear
!FPeriod = Me.FPeriod
!FDate = Me.FDate
!FAmount = Me.FAmount
.Update
MsgBox "Success!" & vbCrLf & vbCrLf & "You have a new Financial Record!", vbInformation, "New Financial Record"
Else
'Record exist.
If !FAmount = Me.FAmount Then
'All fields have the same values.
MsgBox "This record already exists.", vbInformation, "Financial Records"
Else
'There is record with only a different amount. Give to user the chance to update the amount."
strMsg = "A record with the same date properties and an amount of " _
& Format(!FAmount, "Currency") & " already exists." & vbCrLf & vbCrLf _
& "Whould you like to update the amount to " & Format(Me.FAmount, "Currency") & "?"
'Ask the user for the update.
If MsgBox(strMsg, vbQuestion + vbYesNoCancel + vbDefaultButton2, "Update Financial Record") = vbYes Then
.Edit
!FAmount = Me.FAmount
.Update
MsgBox "Financial Record updated successfully!", vbInformation, "Update Financial Record"
End If
End If
End If
End With
'At this point prepare the form for the next data entry. Clear contents of fields etc.
ExitHere:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub
ErrH:
MsgBox "Error: " & Err & vbCrLf & vbCrLf & Err.Description, vbExclamation, "Financial Record"
Resume ExitHere
End Sub
Hope helps.
Cheers,
John
PS.: I don't know if the & "' AND CLng(FDate)=" & CLng(Me.FDate) works properly in your system(s) as it works in mine.