Update database using Forms

Last Post 29 Dec 2004 07:58 AM by kohli290866. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

28 Dec 2004 09:15 AM
Hello all,
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.
New Member
New Member

29 Dec 2004 07:58 AM
I am not much familiar with Access as the front end, but such
tasks via ASP/Web page or VB ... can be easily achieved,
if you write a simple SQL procedure on the SQL side,
and execute it with the desired value ... all should be well.

New Member
New Member

11 Jan 2005 01:39 PM
Sounds like an Access question, so you may not get very quick answers here on this SQL Server forum.

I suspect that there is a form event you can trap, such as "BeforeInsert," that allows you to set certain column values in the junction table before adding the related record.


Acceptable Use Policy