Can delete record via table but not via form?

[1DMF]

hi,

I have a main form with a sub form.

If I try to delete a record from the sub form i get an error...

The delete statement conflicted with the reference constraint...FK_Broker_Fees_Contacts

If I open the table where the sub form gets its records and try to delete the same record it deletes just fine.

This tells me the SQL FK constraints are not the problem, but the form is?

I have 3 tables...

Contacts (ContactID,...)
AR_Fees (ContactID, ...)
Broker_Fees (ContactID, ARID, ...)

(underline = PK, bold = FK)

Basically the Contacts table is where all main records reside with sub typing of (AR or Broker)

The AR_Fees has PK of ContactID which is also FK to Contacts table.

The Broker_Fees table has PK of ContactID which is also FK to Contacts table and FK ARID which is really a pseudo name for the ContactID in the AR_Fees table.

I then have a form bound to AR_Fees with a sub form (datasheet) which displays all links Broker_Fees records.

However, I seem unable to delete a record in Broker_Fees from the sub form, yet can delete a record directly from the table.

All the tables are linked to back-end SQL so the FK / PK constraints reside there.

All insert/update specifications are set to 'No Action'

Why am I getting this error, deleting records in a table that has FK's shouldn't cause a problem, there are no other tables related to the Broker_Fees using its PK and an FK anywhere?

If there was an issue deleting a record from Broker_Fees, SQL would moan and it doesn't, it's only an attempt via the form and sub form when the error occurs?

What is causing this problem?

Thanks,
1DMF.





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

.. I ran the query that powers the sub form and tried to delete the record direct from the query and got the same error.

the query is as follows...

SELECT Broker_Fees.ContactID, Broker_Fees.ARID, Broker_Fees.Monthly_Fee, Broker_Fees.Other_Fee, Broker_Fees.Notes, Contacts.FirstName, Contacts.LastName, Contacts.[Leaving Date], Contacts.[Membership Date]
FROM Broker_Fees INNER JOIN Contacts ON Broker_Fees.ContactID = Contacts.ContactID
WHERE (((Contacts.[Leaving Date])>=DateAdd("m",-3,Now()) Or (Contacts.[Leaving Date]) Is Null))
ORDER BY Contacts.[Membership Date];

It's as though it thinks I want to delete the record from Contacts also which I most certainly do NOT!!!!

Is there a way round this?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

I solved the problem by removing allow deletions on the sub form and using a dblclick event and a new class method...

Private Sub Form_DblClick(Cancel As Integer)

    If vbYes = MsgBox("Delete selected fees record, are you sure?", vbYesNo) Then
        Dim oC As New clsCharges
        If Not oC.GetAR(Me.ARID) Then
            MsgBox oC.ErrorMsg
        Else
            If Not oC.DelController(Me.ContactID) Then
                MsgBox oC.ErrorMsg
            Else
                MsgBox "Controller fees record deleted!"
            End If
        End If
    End If
    
    Set oC = Nothing
    Call Me.Parent.UpdateView
    
End Sub

OK I had to set my parent form UpdateView (MVC) method to public to enable the main form to refresh and remove the '#Deleted' from the screen in the sub form and correctly reflect the delete record, but it works great, so mustn't grumble!


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music