Relationships, duplicate values

[J_Murphy via AccessMonster.com]

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

 

[John W. Vinson]

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

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?