add or change a record

[Roland87]

Hello,

I have a bit of a snag, a "you cannot add or change a record because a related record is required in table";

I have two tables, one that's called "custinfo", and the other "discounts".
Each table has "cfID" as an autonumber field, and both are primary keys in each table.

I have a field in "discounts": "name2" which I filled with numbers, and then used the lookup wizard to display the name of each customer (the field is "clname" in the "custinfo" table). Thus, a one to many relationship between "cfID" in "custinfo", and "name2" in "discounts". So far so good.

Now, when I create a form with fields from both tables, and I try to create a new record, access gives me "you cannot add or change a record because a related record is required in table"..

Can anybody help me out? thanks.

 

[Pat Hartman]

Start by removing the table level lookup. It causes nothing but trouble.
tblCustInfo has autonumber primary key - cfID
tblDiscounts has autonumber primary key - DisID
tblDiscounts has foreign key - cfID that points to cfID in tboCustInfo

On your discounts form, add a combo to lookup cfID. The bound column will be the numeric cfID but the combo will display the customer name from tblCustInfo. The wizard will help you build the combo.

 

[Roland87]

Pat, this does not make any sense to me. Do you mean to join cfID in CustInfo to another cfID in Discounts with a one to many relationship??

What do you mean by foreign key, is that different from a primary key? and should it not be the other way around, i.e. the primary key in CustInfo that points to Discounts?

Please clarify.

 

[jdraw]

PMFJI, Pat has provided clear, concise instructions. If you have difficulty with primary key/foreign key, suggest you use Google or other tool to research the terms. There are plenty of online sites with definitions and examples.

As for the "you cannot add or change a record because a related record is required in table" consider a 1:many set up with People and Hobbies where 1 person can have many hobbies. You could Not say Person X has Hobby Skiing, before you have a Person X. You must have an entry in the 1 side table, before you can associate/assign a record in the Many side.

See example at http://www.maakal.com/maakalDB/ERD_Examples2.htm

 

[Pat Hartman]

Roland,
An explaination of relationships is too big a topic for this forum so as jdraw suggested, some reading and education is in order.

In summary, the "child" table contains the primary key of the "parent" table and the PK of the parent table when it is stored in a child table for the purpose of linking is called a FK (foreign key). Think of your christmas card/ gift list. You have a table with the names of your friends and relatives. Some of them have children. How many columns do you define to hold the names of children? The answer is 0 in a properly normalized schema. You would create a second table to hold the names and birthdates, etc for the children. Each child would have his own record. So now the question is - how do I know which "parent" each child belongs to? The answer is you put the ID of the parent record into the child record. For our purposes in this simplistic example, a child has only one parent not two or more. You can't put the IDs of the child records in the parent record because there are "many" of them but you can put the ID of the "parent" record in the child table because the child has only one "parent"

If you wanted to carry this example to its fully normalized end, you would need to implement a many-to-many relationship which would require adding a junction table to do the linking for us. But, that's for later on

 

[Roland87]

Pat, thank you for your help, I have redesigned the relationships, and I think I understand a lot more about access now. It is the structure of the relationships that calls in for such a message. I think I have it working now, so I appreciate your help on this.