Design Roadblock - Input Forms

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.

=?Utf-8?B?Um9i?= wrote in
:

I'd like to have a form based on the incident table and a subform
tying back to the member table.

....

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?


There are any number of ways of picking a record before editing it.

You certainly can use an unbound list box or combo, and use its AfterUpdate
event to move or filter the form to the correct member record.

Another is to create a dedicated search form, with suitable buttons for
searching by name or department or whatever. This way you can make sure
that the user has looked for someone before creating a new record.

The best thing to do for ideas is to look around all the databases you use
at work, in the library, on the internet. Look especially for the ones you
don't notice, because they are the ones that are working really well. You
only have to stop and think about an interface if it's wrong. When you have
an idea about what you like, it's usually not too hard to work out how to
implement it -- and there is always this group if you can't get it to go!

Incidentally, as this is a db design group, I was not clear about your
table design. A field like MemberNumber is nearly always a unique
identifier -- is there some reason that this cannot be a PKey on its own?

Hope that helps


Tim F

I think you are going about it backward, Rob. You should have a form that is
bound to the Member table. It will display records for all the members in
the database. You can add a combo box that will search the form to locate
specific members -- there is a wizard that will create that combo for you.
Then you would add a subform to the members form and it would be bound to
the incidents table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Rob" wrote in message
...
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.

Tim,

Thanks for your suggestions. The name "MemberNumber" is deceptive. Member number is just a number (usually 1-4) to designate the member's position under a Contract Number. So Contract A may have members 1,2, and 3 under it. Member number alone isn't unique. I probably should've referenced it as a "Relation Number". We identify members with a combination of Contract #, Member #, and DOB.

-Rob


"Tim Ferguson" wrote:

=?Utf-8?B?Um9i?= wrote in
:

I'd like to have a form based on the incident table and a subform
tying back to the member table.

....

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?


There are any number of ways of picking a record before editing it.

You certainly can use an unbound list box or combo, and use its AfterUpdate
event to move or filter the form to the correct member record.

Another is to create a dedicated search form, with suitable buttons for
searching by name or department or whatever. This way you can make sure
that the user has looked for someone before creating a new record.

The best thing to do for ideas is to look around all the databases you use
at work, in the library, on the internet. Look especially for the ones you
don't notice, because they are the ones that are working really well. You
only have to stop and think about an interface if it's wrong. When you have
an idea about what you like, it's usually not too hard to work out how to
implement it -- and there is always this group if you can't get it to go!

Incidentally, as this is a db design group, I was not clear about your
table design. A field like MemberNumber is nearly always a unique
identifier -- is there some reason that this cannot be a PKey on its own?

Hope that helps


Tim F

Lynn,

Thanks for the suggestions. I'm going to try out your suggestion today. I'll let you know how it goes!

"Lynn Trapp" wrote:

I think you are going about it backward, Rob. You should have a form that is
bound to the Member table. It will display records for all the members in
the database. You can add a combo box that will search the form to locate
specific members -- there is a wizard that will create that combo for you.
Then you would add a subform to the members form and it would be bound to
the incidents table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Rob" wrote in message
...
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.