Relationships, duplicate values
in [Form]
|
Prev: Does any one know of a template for purchases, business card siz
Next: Hyperlinks in Access 2007 Rich Text
From: J_Murphy via AccessMonster.com on 7 Mar 2010 20:37 Hi, I'm getting the common message "The changes you requested to the table were not successful because they would create duplicate values...." I think my table relationships are causing this and don't know how to get around. I have 4 tables: tblCustomer, tblWorkOrder, tblClientBuildings and tblClientBuildContacts, the last three are related to tblCustomer by CustomerName(Primary key in tblCustomer). I have a form frmCustomer that has a subform that is a continuous form that is bound to tblClientBuilding. On this subform there's a command button that opens a popup form that is bound to tblClientBuildContacts and enables adding and editing to the table. That part is working perfect. I have a frmWorkOrders and tried to use the same form, called when a list box is filled. What I want to do is this: plunk some information, a foreign key, into frmWorkOrder. The information does get plunked into the table but when I tried to navigate to the next new record I get the message. Can I just catch the error and ignore? TIA -- Message posted via http://www.accessmonster.com
From: John W. Vinson on 7 Mar 2010 20:54
On Mon, 08 Mar 2010 01:37:26 GMT, "J_Murphy via AccessMonster.com" <u58343(a)uwe> wrote: >Hi, I'm getting the common message "The changes you requested to the table >were not successful because they would create duplicate values...." I think >my table relationships are causing this and don't know how to get around. It's not the table relationships per se; it's the fact that you have (quite properly!) a Primary Key on a table, and you're doing something to add two records with the same primary key value to a table. Since the PK is, by definition, unique within a table... you can't do so. > I have 4 tables: tblCustomer, tblWorkOrder, tblClientBuildings and >tblClientBuildContacts, the last three are related to tblCustomer by >CustomerName(Primary key in tblCustomer). Well... names make *very bad* primary keys. A PK must be unique; it should be stable (not changing over time); and ideally it should be small. People's names fail on all three counts! You should consider instead having tblCustomer with an autonumber (or manually maintained unique numeric) primary key, and fields for LastName, FirstName and so on. That way if you have two customers named Mike Smith you wouldn't need to regretfully turn the second one away because he'd break your database <g>. >I have a form frmCustomer that has >a subform that is a continuous form that is bound to tblClientBuilding. On >this subform there's a command button that opens a popup form that is bound >to tblClientBuildContacts and enables adding and editing to the table. That >part is working perfect. Why the (more complicated, harder to maintain) popup form rather than a sub-subform? Either will work, but... Also, you say that the Customer is the foreign key in all three tables. However it sounds like there should be a one to many relationship from tblClientBuilding to tblClientBuildContacts. Or is a "contact" in fact something related to a customer, and not to any particular building? >I have a frmWorkOrders and tried to use the same >form, called when a list box is filled. What I want to do is this: plunk >some information, a foreign key, into frmWorkOrder. The information does get >plunked into the table but when I tried to navigate to the next new record I >get the message. Can I just catch the error and ignore? >TIA No, you cannot; because *a primary key must be unique*. What's the Recordsource of frmWorkOrder? What's in the listbox? What do you mean by "tried to use the same form" - same as what? What field are you trying to fill with a foreign key, and where is that foreign key value coming from? -- John W. Vinson [MVP] |