Help completing a table record

[aca00js]

I am trying to complete a record to save in my table. My system is saved through form view. With a new button button been pressed which works through a macro to make a new record.

The trouble is I have three subforms which control access to my other 3 tables (Officer, Offender and Victim). What I want to do is take the key fields from these other 3 tables (two of these are hidden from the user (OffenderID and VIctimId, which are Autonumber fields.) to complete the record for my main table.

At the moment I can only add the fields that are gotten from the incident form. What would be best is to allow the user to search each of the subforms which all contain a subform of there own (this contains a datasheet of the respective tables, which display all the current records), So when the user types in the officer collar number it will display that officer in its datasheet. Then the user could select this officer and there details will be displayed in the form textboxes. The same should happen for the VIctim and Offender, but the search will be done by their name. So when you try to save an Incident record. It takes the Victim ID, Offender Id and Officer Id of the records that are currently displayed in the forms textboxes.

Sorry for such a long question. Hope you can help.

 

[Pat Hartman]

You have several many-to-many relationships. Many incidents may be investigated by many officiers. Many incidents may be related to many victims. And finally many incidents can be related to many offenders.

You are trying to implement these realtionshps as 1-to-many and doing it backwards to boot.

You need at a minimum 7 tables. You might consider combining officier and offender into a single table since they are both people. Victims I think are not necessarily people but if a person is always the designated victim even when the crime is against an animal or business then you could combine them also.

tblIncident:
IncidentID (autonumber primary key)
etc.

tblOfficier:
OfficierID (autonumber primary key)
LastName
FirstName
etc.

tblVictim:
VictimID (autonumber primary key)
LastName
FirstName
etc.

tblOffender:
OffenderID (autonumber primary key)
LastName
FirstName
etc.

tblIncidentOfficierRelation:
IncidentID (primary key field 1, foreign key to tblIncident)
OfficierID (primary key field 2, foreign key to tblOfficier)

tblIncidentVictimRelation:
IncidentID (primary key field 1, foreign key to tblIncident)
VictimID (primary key field 2, foreign key to tblVictim)

tblIncidentOffenderRelation:
IncidentID (primary key field 1, foreign key to tblIncident)
OffenderID (primary key field 2, foreign key to tblOffender)

Incident should have an autonumber primary key and the incidentID should be placed in the related IncidentOfficier, IncidentVictim, and IncidentOffender tables.