Edit a Record, Check for duplication prior updating to the table

[gurmet]

Hi All

I have been looking around for help, and finally post this problem.

I created a form to edit a record. Before i can click save button on the edit form i need to check if the data that have been edit exists in the table. But if i m calling the same record and updating the same record without changing anything it should allowed to save.

Example: I m creating a table to save all types of courses (course_id, course_code, course_description)

On the Edit Course form, when click Save button, in the code screen i check for
(1) Is null (incase the user try to erase the value in course_code and try to save a blank code.
(2) Next i need to check if the new code is exists in the table, if exits do not allow to save reminder that duplicate code.
(3) If the code is same, which mean if i change the course description, it should allow to save.

My Code:

Private Sub btnSave_Click()
On Error GoTo Err_btnSave_Click

Dim criteria As String
criteria = "[course_code] = '" & Me.course_code.Value & "'"
' Comments: Checking all the fields are mandatory during creation of Course.
If IsNull(Me![course_code]) Then
MsgBox "Mandatory Field: Course Code."
ElseIf DCount("course_code", "s_courses", _
criteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Duplicate code."
ElseIf IsNull(Me![course_name]) Then
MsgBox "Mandatory Field: Course Name."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close
End If

Exit_btnSave_Click:
Exit Sub

Err_btnSave_Click:
MsgBox Err.description
Resume Exit_btnSave_Click

End Sub




Please help.
Gurmet

[gurmet]

Hi Everybody
As i have dateline to complete this, i have been searching and chit chat with my friend and found a solution. Just to share with others on how this was resolved:

(1) Create a query to check

SELECT s_courses.course_id
FROM s_courses
WHERE s_courses.course_code=Forms!s_courses_edit!course_ code And s_courses.course_id<>Forms!s_courses_edit!course_i d;


(2) Create a additional function to include the above query in the code to check if the existing count

Private Function CheckExistingCourse() As Integer
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim existingCourseCount As Integer
Dim queryDefs As Variant

Set db = CurrentDb()
Set queryDefs = db.queryDefs("course_edit_q1")
queryDefs(0) = course_code
queryDefs(1) = course_id

Set rs = queryDefs.OpenRecordset(dbOpenSnapshot)
On Error Resume Next
rs.MoveLast
existingCourseCount = rs.RecordCount
rs.Close
Set db = Nothing
CheckExistingCourse = existingCourseCount
End Function


(3) Modify the save function.

Private Sub btnSave_Click()
On Error GoTo Err_btnSave_Click

' Using private function to check
existingCourseCount = CheckExistingCourse

' Validate input during editing a record before updating
If IsNull(Me![course_code]) Then
MsgBox "Mandatory Field: Course Code."
ElseIf IsNull(Me![course_name]) Then
MsgBox "Mandatory Field: Course Name."
ElseIf existingCourseCount > 0 Then
MsgBox "The course code already exits!!"
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close
End If

Exit_btnSave_Click:
Exit Sub

Err_btnSave_Click:
MsgBox Err.description
Resume Exit_btnSave_Click

End Sub

At last, this is resolved, and hope it helps others.

Regards
Gurmet