Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ndn_24_7
Starting Member
23 Posts |
 Posted - 2004-12-27 : 13:15:01
|
| 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). The incident table is an old table with information still in it. 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 clicks 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 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-12-30 : 14:03:13
|
| So, just to confirm this is what you want, the 3-table design you have created means that one incident can have multiple suspects AND one suspect can be involved with many incidents. Otherwise, you don't need a junction table, just an incident key in the suspect table.On the AfterSave action in the Suspect Form, you can issue your own commands to update the junction table. I prefer to do all my updating in module code, issuing the SQL commands via ADO. Remember that you'll have to take into consideration the possibility that somebody did not just ADD a new record, but perhaps EDITED one, and so you may need to do some fancy comparisons of old values to new to determine if you need to delete any previously inserted rows from the junction table.To synchronize data between two forms, put in the Form_Load section of the second form logic that will retrieve the needed keys from the first form, put them into a SQL statement as the RecordSource for the form and issue a Requery.---------------------------------------------------------------------------------Infoneering: Information Technology solutions engineered to professional standards. |
 |
|
|
|
|
|
|
|
Updating database using Forms