INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Can delete record via table but not via form?

Can delete record via table but not via form?

(OP)
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...

Quote:

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

RE: Can delete record via table but not via form?

(OP)
.. 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...

CODE

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

RE: Can delete record via table but not via form?

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close