sanity check pleeeease!

[Guest]

I have a training database.

To keep things simple, I have three tables as thus:

tble_supervisor
SupervisorID (PK)
Name

tble_spvrnoteslink
SupervisorID (FK)
NotesID (FK)

tble_notes
NotesID (PK)
SupervisorID
Date
Time
Message
DelNote (checkbox)

Description thus:

The purpose is to allow trainers / supervisors to send each other notes.

From the main menu, I have a button for opening the supervisor form. It is a
list in continuous form and each record has four buttons.

One button allows a supervisor to send a message to other supervisors. The
second button allows supervisor to view all messages sent by other
supervisors.

These received messages have a checkbox. What I am thinking is that I can
tick the checkbox and when I close the form the message will get deleted.

The problem is that I realised I am trying to delete a record from
"tble_notes" without deleting the appropriate records from
"tble_spvrnoteslink" first.

Should the delete checkbox be in "tble_spvrnoteslink" rather than
"tble_notes" ??

I hope this doesn't sound confusing.

 

[Guest]

Hi

Have you set the delete to casscade in the relationship window.

Once you have done this (if you trust the supervisors) you chould turn off
the warning in the OnClick event and then turn then back on after the delete

 

[Douglas J. Steele]

If you put DelNote in tble_spvrnoteslink, then you'd only be deleting the
record linking a given supervisor to a given note.

If your intent is to delete the note, not simply the fact that Supervisor A
is associated with Note 1, then you need to delete all entries related to
the note from tble_spvrnoteslink before you can delete it from tble_notes.
You can do this using a SQL DELETE statement ("DELETE * FROM
tble_spvrnoteslink WHERE NotesID = " & Me.txtNotesID), or you can set
Cascade Delete to automatically delete related records from
tble_spvrnoteslink whenever you delete a record from tble_notes.

 

[Guest]

thanks for the reply.

The cascade delete wasn't required but I have messed around with it a bit
and I have it all working