Separate Forms or All-In-One?

[Guest]

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.

 

[PC Datasheet]

Rob,

Suggest you need the following tables:

TblMemberID
MemberID
etc

TblContract
ContractID
etc

TblIncident
IncidentID
ContractID
etc

TblMemberIncident
MemberIncidentID
IncidentID
MemberID


Connect the "ID" fields with same name for your relationships.

You need a main form based on TblIncident with a combobox for choosing
ContractID and a continuous subform based on TblMemberIncident with a combobox
for choosing MemberID. In the main form's subform control properties, set the
LinkMaster and LinkChild fields to IncidentID and IncidentID will be
automatically filled in the subform.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

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.