form design

[Guest]

I have a database in Access 2003.
I have two linked tables - one with basic information on people, one with
information on a particular event. They are linked by ID number - the main
data ID number is auto.
I have three forms for three different uses of the database - each form only
shows the fields that are applicable for each use.
Each form is linked to a different query so that only the people fitting the
criteria show up in the form view.
Two of the forms are working fine. One of the forms is not and I can't
figure out why since I think I designed them the same. On the form that is
not working, it will not allow me to add records. If I go to the main data
table and add the information there, it will show as a record in the right
form; however, it will not allow me to add a record from the applicable form.

 

[Jeff Boyce]

Your description could be interpreted more than one way.

You say "They are linked by ID number..." -- does this mean each table has
an ID/primary key, and you've used the relationship window to connect the
two primary key IDs? If so, based on your description, this sounds like an
incorrect way to relate the tables.

By the way, in Access, a "linked" table is one that is outside of the .mdb
file that is Access' database. I suspect you are describing "related"
tables.

Before we move on to forms, the tables need to be right (and you DID post to
a tablesdbdesign newsgroup!).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes, yes, you are correct. I had a feeling it was related to the ID/primary
key issue. Yes, they are indeed "related" tables - wrong terminology.

More info -
There are currently two tables. The main table utilizes an autonumber
primary key. The records are currently primarily companies.

The second table has fields related to an event that the companies
participate in. It includes names of people who participate in the event and
information about their participation. Here, the ID number is not an
autonumber and is entered to match the company's primary key in the other
table. They are related by the ID number.

At that point, things seemed to be working. I designed two forms, one (Form
A) with the fields of more general information and one (Form B) with
information specific to the event. The forms' data sources were queries so
that when you go into a form, it will only show records that met certain
criteria. (The fields are pulled from both tables.)

Now, I want to add a third form (Form C) that will also be using fields from
both tables. I set it up like Form B, but it is not working. I want to be
able to add or insert records from Form C, but it is "greyed out."

I appreciate your assistance in helping me out.

 

[Jeff Boyce]

Why are FormA and FormB using "fields from both tables"?

A common way to design what sounds like a "parent/child" relationship is to
use a main form for the parent (?your companies?), and a subform for the
child data (?your events?).

Also, if your [Events] table "includes names of people who participate", you
may want to rethink your table structure. It sounds like your situation can
have:
* one company hosting/participating/doing something in multiple events
* one person participating in multiple events

If so, you need (to get the best use of Access' features and functions) a
table structure something like:

tblPerson
PersonID (PK)
FName
LName
DOB
(other person-specific data)

tblCompany (or Sponsor, or EventHolder or ...)
CompanyID (PK)
CompanyName
(other company-specific data)

tblEvent
EventID (PK)
CompanyID (this is a foreign key, pointing back to which company is
hosting/sponsoring/whatever...)
(other event-specific data, e.g., date, place, ...)

trelParticipants
ParticipantID (PK)
EventID (FK)
PersonID (FK)

Or have I read too much into your description? (You may wish to read up on
"normalization") Again, you'll want to get your data structure down pat
before moving on to forms...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hartman \(MVP\)]

As Jeff has already mentioned, you may want to rethink your form design.
Generally, it is best to have a separate form to update each table. That
doesn't preclude basing a form on a query that joins tables. For example,
you want to show the customer's name and address on the order form but you
don't want to update them from there. Having a single update form per table
greatly reduces the complexity of your application and also prevents you
from having to code the same business rule edits in multiple places.

One thing you mentioned was that the second table doesn't seem to have a
primary key. It is best for many reasons that I'm not going to go into for
each table to have a primary key. The PK may be comprised of "natural"
fields or it may be an autonumber but the idea is to be able to uniquely
identify each row. Access/Jet will create non-updatable queries in certain
situations where keyed and non-keyed tables are joined. That may be what
you are running into. But it is more likely that you are attempting to use
a single query to join three (or more) tables that do not participate in a
hierarchical relationship. For example A-->B-->C will produce an updatable
recordset because C is related to B and B is related to A in a hierarchical
relationship. Your design sounds more like A-->B and A-->C. If you create
a query that joins A,B, and C in this case, it will not be updatable because
B and C have no relationship with each other and the join will end up as a
Cartesian product which is sometimes called a cross join. In a Cartesian
product all the records from set A are matched to each record in set B. So
if A-->B produces 20 records and A-->C produces 5 records, a query that
joins the three tables will return 100 rows (20 * 5).