Update database using Forms

Question

Update database using Forms

Grasshopper

Points: 12

December 28, 2004 at 12:07 pm

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alice Sanders SSC Enthusiast Points: 107 More actions · Answer 1

I had a similar situation where I needed to created front end in MS Access and have the back end in SQL. I created a main form that contained standard information such as a project id, project title, etc. Then, for the supplemental pieces I created a sub form. This allowed the user to see all of the common information for a project, but they could enter the data for the specific section in the sub form.

For example, in the Suspect table, create a Suspect ID and an Incident ID. The Incident table will contain an Incident ID. For the Suspect table, the Suspect ID will have an identity seed set and the application id will not have the identity seed set. Then, create a main form for the incident report containing the report title, etc. Basically, any standard information for the report. The main form will use the Incident table. Then, create a sub form using the Suspect table, if this table will contain your specified info for the report, be sure to include the Suspect ID and Incident ID on the sub form. You can make these fields invisible. On the main form, when you add the sub form, be certain to have the child field and the master field be the Incident ID. When you enter info into the sub form they suspect id will automatically be created and the Incident id will be populated since it is pulled from the main form.

I hope this makes sense. If not, please let me know.

smknox Hall of Fame Points: 3275 More actions · Answer 2

Yonoah:

You need to create a form with a subform. The form your user is opening to enter the suspect may be appropriate to use as a subform.

The recordsource for the main form would be the Incident table. Make sure your IncidentID is in the recordsource.

The recordsource for the subform would be:

SELECT IncidentSuspect.IncidentID, IncidentSuspect.SuspectID, Suspect.SuspectID AS SomeAlias, Suspect.OtherColumns FROM IncidentSuspect INNER JOIN Suspect

ON IncidentSuspect.SuspectID=Suspect.SuspectID;

In properties for the subform, set "Default view" to Datasheet. You don't need to display (and probably shouldn't) any of the ID columns on the subform.

Place the subform on the main form.

Open properties of the subform. For "Link Child Fields" enter: IncidentID and for "Link Master Fields" enter the same (IncidentID).

Now you should be able to enter as many suspects as you like in the subform and Access will handle all the rest.

SMK

Yonoah McKinney Grasshopper Points: 12 More actions · Answer 3

Thank you for all your help. This worked perfectlly. I'm now able to tie a single incident to multiple suspects. Thank again