Detect duplicate record before entering

[sierra467]

I have a subform that collects the following data to put in a record in tblClass:

StudentID, Trimester, SubcatID, WorkGrade, SkillGrade

Before the record is written for the first time OR edited/updated to the table, I want to search tblClass and determine if the new/updated info will create a duplicate record.

In this specific case, a duplicate record will be defiened by a record where the only fields being considered would be StudentID, Trimester and SubcatID. The fields WorkGrade and SkillGrade should not be considered.

The code I came up with was the following and it was put in the BeforeUpdate:

    Dim conn As ADODB.Connection    'Connection Object
    Dim rst As ADODB.Recordset         'Recordset Object
    Dim strSQL As String                   'SQL statement for open statement
    
'   Create object variables
    Set conn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    
'    Create sql to search for records in tblClass that match
    'studentID, Trimester, and Subcatagory in the form record being added
    strSQL = "SELECT * " & _
             "FROM tblClass" & _
             "WHERE fldStudentID = " & StudentID & " AND " & _
                   "fldTrimester = '" & Trimester & "' AND " & _
                   "fldSubcatID = " & SubCatID & ";"
            
'   Open recordset 
    rst.Open strSQL, conn, adOpenKeyset, adLockOptimistic
    
    If rst.RecordCount >= 1 Then
    '   record already exists in tblClass
        msgbox "Record already exists!",, "Duplicate Record Error"
        Me.cboSubcatID.SetFocus
        Cancel = True
    End If

'   Close and disassociate object variables
    rst.Close
    conn.Close
    Set conn = Nothing
    Set rst = Nothing

This code worked great except when I went to edit an existing record. When I went to change a grade (WorkGrade or SkillGrade) on an existing record, it told me that I could not enter the record because the record already existed (ie, the record I had open and was editing). I am not sure if it is my code that I need to edit or if it is the placement of the code I need to change.

Any suggestions would be great.

 

[RuralGuy]

I would probably add a little code to the beginning of your BeforeUpdate routine:

If Me.StudentID.Value <> Me.StudentID.OldValue OR _
   Me.Trimester.Value  <> Me.Trimester.OldValue OR _
   Me.SubCatID.Value <> Me.SubCatID.OldValue Then

...All of your original code
.
.
.
...
End If

 

[sierra467]

Tried what you suggested, but that did not work because it bypasses the duplication check on a new entry - Value is what is entered by the user and OldValue for the controls = Null.

 

[RuralGuy]

Hmmm, you're right. Let's try:

If Me.StudentID.Value <> Nz(Me.StudentID.OldValue,0) OR _
   Me.Trimester.Value  <> Nz(Me.Trimester.OldValue,0) OR _
   Me.SubCatID.Value <> Nz(Me.SubCatID.OldValue,0) Then

...All of your original code
.
.
.
...
End If

 

[sierra467]

Thanks for the responce,
What will that do???? I have never seen that function and I looked it up on the web and still did not really understand what it does. ( I don not have time to try it right now, but will over the weekend) . Thanks.