allow duplicate values!

[ninja_imp]

i have a sub form which on the click of a button sets 4 fields

Private Sub Command8_Click()
LastPaymentDate.Value = Date
NextPaymentDate.Value = Date + 7
AmountPaid.Value = [Form_Frm_Customer]![Payments]
[Form_Frm_Customer]![Payments Due By].Value = Date + 7
End Sub


It updates all the fields fine but i keep getting the error 'the changes you requested were not successful because they would create duplicate values'

I have checked the table and all fields are set to allow duplicates.

Cant figure it out.

 

[MStef]

What is the PRIMARY KEY of this table???

 

[ninja_imp]

It is [CustRef] - which is a 'many to one' linked to the main table of Customers.

 

[MStef]

Primary Key can not be duplicated.

 

[ninja_imp]

i have tried it with no primary keys - and setting all to allow duplicates but still getting the error!

its driving me crazy

 

[MStef]

Send a short example of your mdb.

 

[ninja_imp]

here it is - the main one is the 'Frm_Customers' in this is the sub form for marking payment date which springs the error.

 

[KeithG]

I took the primary key out of tbl_CustomerBilling and set the index to allow duplicates and it worked for me

 

[ninja_imp]

with the stripped down it works on mine so it must be conflicting with another sub form - which i had to remove from the stripped attached version.

 

[ninja_imp]

could it be an issue with my relationships?

Picture attached

 

[KeithG]

I see what your problem is!! You have a one to one relationship on field CustRef between tbl_Customers and tbl_CustomerBilling. Changing this relationship to a one to many, with tbl_CustomerBilling on the many side will fix your problem.

 

[KeithG]

I see what your problem is!! You have a one to one relationship on field CustRef between tbl_Customers and tbl_CustomerBilling. Changing this relationship to a one to many, with tbl_CustomerBilling on the many side will fix your problem.

 

[KeithG]

I see what your problem is!! You have a one to one relationship on field CustRef between tbl_Customers and tbl_CustomerBilling. Changing this relationship to a one to many, with tbl_CustomerBilling on the many side will fix your problem.

 

[ansentry]

Your sample is attached, I have added a Primary Key (BillingID) to Tbl_CustomerBilling. Your relationship is now 1-Many.

 

[KeithG]

What you need to do is create a BillingID or BillRef field in the Billing table and make this field the primary key instead of CustRef

 

[ninja_imp]

i have taken on what have both said - created new primary key as [billing_id] and in the relationship set it to 'custref' to 'custref' as one to many but still get the error.

Aaargghh!

 

[KeithG]

Really, can you post a picture of your relationships now?

 

[ansentry]

Have a look at the relationships in your database;

Your have the same problem with several tables.

 

[ninja_imp]

2 of the tables i cant change from one to one - how do i change it to 'one to many'?

 

[KeithG]

The relationship's are one to one because you have the relationships on the primary key. You are probably going to have to change your Primary Keys. I am going to review your prevous relation diagram again.