I trying to develop a incident reporting application the security personnel
can use to report incidents. I have a SQL Server 2000 back end and an Access 2000 front.
I created three tables, Incident, Suspect and IncidentSuspect (junction table).
I added the suspect and IncidentSuspect tables to make the database in proper form.
I want the user to be able to enter an incident in to a form, and then add a suspect(s).
The problem I'm having is how do I update the junction table so each incident will reference
the right suspect? Right now I have a form to enter incidents, then I have a button the user
can click on to open a form to enter the suspect(s). Whenever I go back to that a particular
incident, I want just that suspect involved in the incident to show, but it will show all the
contents in the suspect table. My other issue is how to update the junction table
using the incident ID in the incident form and the suspect ID in the Suspect form
automatically. Right now I have to go into the database and manually update the
junction table with the right IncidentID and Suspect ID. Is there a way to update the
junction table automatically using the Incident ID and the Suspect ID from their forms
or something? Any ideas will be greatly appreciated.