Forms - Finding Existing Records??

[SherryLynn]

I have a Form with two Subforms. One of the Subforms also has a Subform. The parent form contains Family Information. The first Subform contains Victim/Child Information and the second Subform contains Offender information. The Offender subform contains a Subform with Statistical information related to that Offender. Each Victim can have multiple Offenders and each Offender can be related to multiple Victims. Here's my problem. I want to be able to select an existing Offender for a Victim, yet be able to attach new Statistical information relating to that connection (victim-offender). How can I set this up to bring up the existing Offender record and connect it to this Victim? The Offender table will have over 2000 names, so a list box won't quite do it (I would be scrolling forever). Does anyone have any wonderful ideas? I am fairly new to Access97 and am not sure how to go about this. I really appreciate any help offered.

Thanks, Sherry

 

[DaveSH]

Sherry,

First I must say that reading your description made me kind of sad as I know there are probably tragic stories behind the data here.

Anyway ...

You have a many to many relationship between victims and offenders which in database design is represented by (2) 1 to many relationships.

I would suggest:

Table Victim
Fields
VictimID - Primary Key
Name
- Other Info -


Table Offender
Fields
OffenderID - PrimaryKey
Name
- Other Info -

Table Offenses
Fields
OffenseID - PK
VictimID
OffenderID
Description
- Other Info -

Create 1 to many relationships as follows :
-Victims.VictimID and Offenses.VictimID
-Offenders.OffenderID and Offenses.OffenderID

These fields would be the basis for your parent child relationships between your form and subforms.

The details of the offense will be stored in the description or other fields.

The deatails will then be available to be displayed by querying by offender or by victim.

On your Offense subform you can place an offenderID field and a button which opens an unbound "lookup" form. A simple lookup form could be something like placing 2 comboboxes that list letters of the alphabet and then another that lists offender names that begin with that letter. Or you can use Command Buttons with letters or any other way to limit your list. Once the offender is chosen, you can have a command button close the form and place the value in the Offender ID field.

Suppose you have a subform on the main form that lists of victims in the family. Then you can have another subform that lists offenses. YOu can have a third subform that is a single form that displays the offender detail information for the selected offense. So if you were to click on a different offense for the same victim, the third subform will update with the offenders detailed info.

I dont know how you want to display your information, so I will stop here, but If I can provide any specific help, you can e-mail me.

This can be hard to describe, so I will stop here. If you need some more help, please repost or you can e-mail me.

If this is volunteer work or for the benefit of children, I would be happy to help if you need some more help.

I hope this is helpful. Dave
gallagherd1@rcn.com