Separate Forms or All-In-One?

[Rob]

I've got a design problem (at least I think it is) that
I'm hoping someone can give me some guidance on. Say I've
got a database I'm using to tracking some sort of generic
incidents. I've got a member table and an incident
table. I want a one-to-many relationship between them
where there can be multiple incidents for each member in
the member table. The primary key field for the member
table is a combination of three other fields in the member
table (date of birth, contract number, and member number)
and relates to a foreign key in the incident table.

Here's my problem. I want to use a form to enter data.
I'd like to have a form based on the incident table and a
subform tying back to the member table. So the first
thing a typist would do on the incident form would be to
enter data into the member subform. After they've entered
the data (name, contract#, member#, and date of birth) I
want to check to see if the member already exists. If
they do, don't add a new record in the member table for
that member, just refer to the old record and proceed on
to enter the Incident data on the rest of the form. If
the member doesn't exist yet in the database, then add a
new record for the member and then proceed on to the
incident section of the form.

Am I going about this the right way? Should I be using a
combo box to produce a list of members that are already in
the member table that the typist could choose from? If
so, what would I do when the member wasn't in the list?
I'm stuck as to where I should proceed from here. Break
up the form into separate forms? Any help would be
appreciated.

 

[Kevin Sprinkel]

Some thoughts:

- While multiple field keys work, it's normally simpler,
and I think so in this case, to have a single AutoNumber
primary key to ID unique records.
- A one-to-many relationship would normally be implemented
with a form based on the one side, and a continuous
subform representing the many side, the primary key of the
one side linked to the foreign key of the many.
- Yes, you can use a combo box to present an alphabetical
list of available members to the user, along with the
other fields as additional columns. Once the member is
selected, you can display these other fields in form
controls using the Columns property. See VBA Help for
assistance.
- If you set the Limit to List property of the combo box
to Yes, you can trigger the OnNotInList event if the user
enters a new name. If you do a Google Search on this, you
will find code on how to do this.

HTH
Kevin Sprinkel