S
shawnews
Ok...I'll first describe briefly what I've done.
Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.
Example tables:
Table Name: Names
Primary Key: NameID - autonumber
Table Name: Products
Primary Key: ProductID - autonumber
TableName: Suppliers
Primary Key: SupplierID - autonumber
The relationships are set as being one-to-one and the join type is set to
"1".
So...to be more clear..what is happening...let's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.
Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.
I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?
I appreciate any help you can provide.
Abe
Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.
Example tables:
Table Name: Names
Primary Key: NameID - autonumber
Table Name: Products
Primary Key: ProductID - autonumber
TableName: Suppliers
Primary Key: SupplierID - autonumber
The relationships are set as being one-to-one and the join type is set to
"1".
So...to be more clear..what is happening...let's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.
Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.
I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?
I appreciate any help you can provide.
Abe