Primary Key conflict

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two tables tblEquip and tblDeals that have the same primary key fiel
called DealCode.

I am trying to create a new record in the tblEquip that is based on an
existing DealCode number from the tblDeals. To select the DealCode in the
form I have created a combo box in a form that contains the following code in
the Row Source:

SELECT tblDeals.DealCode FROM tblDeals;

The combo box works fine whenever I am selecting DealCodes in records
already entered in the tblEquip, but when I try to create a new record with
the form I get an error message telling me that I am trying to insert a
duplicate record in the tblDeals.

I can see how this is happening based on the row source... but how should I
modify my code so that I can use the tblDeals to see the DealCode as the
source "for selection purposes", but write that code in the tblEquip and
avoid the tblDeal key violation message?

Thank you,

Trauton
 
Hi,

I have two tables tblEquip and tblDeals that have the same primary key fiel
called DealCode.

I am trying to create a new record in the tblEquip that is based on an
existing DealCode number from the tblDeals. To select the DealCode in the
form I have created a combo box in a form that contains the following code in
the Row Source:

SELECT tblDeals.DealCode FROM tblDeals;

The combo box works fine whenever I am selecting DealCodes in records
already entered in the tblEquip, but when I try to create a new record with
the form I get an error message telling me that I am trying to insert a
duplicate record in the tblDeals.
Click to expand...

I think that your primary key should NOT be DealCode in tblEquip.

Will a given Equip record pertain to ONE AND ONLY ONE Deal?
Will each Deal involve ONE AND ONLY ONE piece of Equip?

If both these are true, why use two tables? You could just include both the
Equip and Deal fields in a single table, if there's only one of each!

John W. Vinson [MVP]
 
Hi John,

Thank you for taking the time to answer my question. I was afraid that may
be causing the conflict. I will add a different primary key and try again.

The reason I have two tables is because not all records in the tblDeals will
have tblEquip information, and since the tblDeals is large I thought I would
be wasting space by just adding those fields there -though I'm sure it would
have made my life easier ;-).

I appreciate your help!

Trauton
 
Hi John,

Thank you for taking the time to answer my question. I was afraid that may
be causing the conflict. I will add a different primary key and try again.

The reason I have two tables is because not all records in the tblDeals will
have tblEquip information, and since the tblDeals is large I thought I would
be wasting space by just adding those fields there -though I'm sure it would
have made my life easier ;-).
Click to expand...

This may be a valid reason for the one-to-one. That being the case, why not
use a Form based on tblDeals, and use a Subform for tblEquip? If you use the
primary key of each table as its Master and Child Link Field you'll get it
filled in automatically.

John W. Vinson [MVP]
 
John,

Thanks! Your suggestion worked, I added a different primary field to the
tblEquip and I am now able to add new records just as I wanted.

There is only one minor glitch that I don't quite understand, The same
DealCode field in the form has an AfterUpdate code Event Procedure that
populates other fields from the Form based on the information from the
tblDeals. These fields are being populated after I tab away from the DealCode
field, however I get a pop up message that reads:

"Run-time error '-214735567 (80020009)
Field can not be updated"

Yet the fields ARE being updated. Any suggestions on how to resolve this
error?

Trauton
 
John,

The Subform ended up being a much better, simpler solution that gave me
exactly what I needed. A valuable option which I had not explored.

Thank you very much!!

Trauton
 
Back
Top