False duplicate key error

[liddlem]

Hi All
I have a student enrolment form where I link students to parents.
Because there are situations where 2 (or more students) can be enrolled AND there could be more than one parent, I need to establish a relationship between each student and each parent.

I am therefore checking the TBL_FamilyRelationship by looping through two recordsets (students and parents) to see if a relationship does not already exist.

If NOT, then I insert a record into the Tbl_FamilyRelationship table.
All good and well . . . until I requery the data!
The section of code that I'm struggling with is...

For i = 1 To rs1Count
    'Compare if each STUDENT is associated with the CURRENT CMTY_Member.
    'If an existing record has been EDITED, then there is a chance that the
    ' relationship already exists, in which case, the change must be undone.
    'If a new record is being added, then a relationship does not exist yet
    
        MyStudent = rs1!ID_Student
        'The CURRENT record only - for which a relationship should not exist yet
        LinkExists = DCount("ID_Student", "QRY_ENR_FamilyRelations", "ID_Student = " & MyStudent & "AND ID_CMTY = " & MyCurrentParent)
        If LinkExists = 0 Then  'NO RECORD EXISTS YET
            'If no link exists already then Link the member to ALL the students that have been added
            MySql = "INSERT INTO dbo_CMTY_FamilyRelations (ID_Student, ID_CMTY, ID_CMTY_RelType) VALUES (" & MyStudent & ", " & MyCurrentParent & ", 500)"
            DoCmd.SetWarnings False
                DoCmd.RunSQL MySql
            DoCmd.SetWarnings True
            [COLOR="Red"]Me.Requery[/COLOR]  'Error occurs here
        Else
            If MyStudent = Me.ID_Student And MyCurrentParent = Me.ID_CMTY Then
                MsgBox "It appears that you tried to change an existing relationship such" & vbCr _
                & "that it would now create a duplicate record for the Student/Parent relationship." & vbCr _
                & "This change will be undone and the record will not be saved"
                Me.Undo
                GoTo CloseAndClean
            End If
        End If
        rs1.MoveNext
    Next

Thanks

 

[liddlem]

OK - So I thought it was simply a case of me being dumb!
I am inside a dirty record, so the refresh wont work till I save the record, so I tried replacing the 'me.requery' with the following .....none of which seem to work.

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Save acForm, "Forms![FRM_ENR_EnrolmentHdr]![FRM_ENR_FamilyRelations_Subform]"
Forms![FRM_ENR_EnrolmentHdr]![FRM_ENR_FamilyRelations_Subform].Save

'Now for the requery!
Forms![FRM_ENR_EnrolmentHdr]![FRM_ENR_FamilyRelations_Subform].Requery

 

[liddlem]

YES !!!!!!
It turns out that instead of trying to save the record, I must UNDO it and then refresh.