Design Problem

[B Bach via AccessMonster.com]

Hello. Listed below are three tables that I am trying to solve a duplicate
value problem. The FRMID is the primary key. I use the Provider table as a
lookup. The problem I am having is when I try to enter data into subforms
linked within the Provider_tbl form I get a duplicate entry error. I get
the duplicate error only after I entered data into the Provider table,
subform_page1-Survey_tbl, and subform_page2-Survey_tbl. I can't go any
further than subform_page2-Survey_tbl.
I have fooled around with the relationship and redefining the index but
nothing seems to work.

1) Survey_tbl -- FRMID number Yes(No Duplicates)
2) Provider_tbl2---FRMID number Yes(No Duplicates)
----Provider_ID number (Duplicates OK )
3)Provider_lookup----Provider_ID number Yes (No Duplicates)

My forms are created as follows:
Provider_frm
subform_page1-Survey_tbl (based off from table Survey_tbl)
subform_page2-Survey_tbl
subform_page3-Survey_tbl
subform_page4-Survey_tbl
subform_page5-Survey_tbl
subform_page6-Survey_tbl
subform_page7-Survey_tbl

 

[mnature]

Why do you have the FRMID number (Yes(No Duplicates)) in two different
tables? If this is being used as a primary key, perhaps you could combine
the two tables into one table. Otherwise, rename one of the primary keys to
something else, and just link that over to the FRMID in the other table.
Generally speaking, it is usually better to have unique names for fields.

 

[B Bach via AccessMonster.com]

I combined the tables like you suggested. I am still having problems with
adding records to the subforms. The duplicate error pops up after I enter
data into subform_2 and click on subform_3.

Why do you have the FRMID number (Yes(No Duplicates)) in two different
tables? If this is being used as a primary key, perhaps you could combine
the two tables into one table. Otherwise, rename one of the primary keys to
something else, and just link that over to the FRMID in the other table.
Generally speaking, it is usually better to have unique names for fields.

Hello. Listed below are three tables that I am trying to solve a duplicate
value problem. The FRMID is the primary key. I use the Provider table as a

[quoted text clipped - 20 lines]

subform_page6-Survey_tbl
subform_page7-Survey_tbl

 

[mnature]

In your relationships, have you set the link of Provider_ID number from
Provider_tbl2 to Provider_lookup to "Cascade Update Related Fields" ?
Because it sounds like the database is trying to update the Provider_ID
number with a duplicate value.

I combined the tables like you suggested. I am still having problems with
adding records to the subforms. The duplicate error pops up after I enter
data into subform_2 and click on subform_3.

Why do you have the FRMID number (Yes(No Duplicates)) in two different
tables? If this is being used as a primary key, perhaps you could combine
the two tables into one table. Otherwise, rename one of the primary keys to
something else, and just link that over to the FRMID in the other table.
Generally speaking, it is usually better to have unique names for fields.

Hello. Listed below are three tables that I am trying to solve a duplicate
value problem. The FRMID is the primary key. I use the Provider table as a

[quoted text clipped - 20 lines]

subform_page6-Survey_tbl
subform_page7-Survey_tbl

 

[TC]

You need to list the following details for each of your tables:

- the table name;
- the name of each field in the table (do not need the types or
lengths);
- which field(s) are the primary key field(s) for that table.

Then we can comment.

HTH,
TC