Duplicate record notice

[steve111]

HI ,

I Have a formed called assembly parts numbers
and in it a subform called parts list
when I used the drop down box in the assemble parts number form it tells me I am creating a duplicate but I am not and it don't bring down the description , drawing number etc for the table allparts
yet when I select part number in the parts list subform it works ok
yet both are set up the same

completely lost with it

and help appreciated
steve

 

[GinaWhipp]

In actuality you are because of the way you have the Part Number to ID set up. They are Primary Keys which means you can only have one value of each per table. Perhaps you meant to set it up to be a Foreign Key thereby allowing multiple entries of the same part in the table.

As a side note... I'm not sure I even understand what you are trying to do and how these tables are suppose to relate to one another. Perhaps you could explain what it is you doing so we can make sure you have set this up to work the way you want.

 

[steve111]

hi ,

in the table allparts it cannot have duplicate partnumbers
in the assembly part number table , I also cannot have duplicate part numbers as these are high level numbers with parts attached to them via the parts list attached to them
the parts list ( subform)can have duplicate numbers

what I cannot understand is why I cannot override a assembly part number if I want to, if I do not duplicate that number in the assembly part number table

steve

 

[GinaWhipp]

Then it sounds like you need a *joiner* table so you can have a many-to-many. It's a PK why would you want to override. The purpose of a PK is to uniquely identify the record, period. It doesn't change... okay so maybe the better question is... is this your first database? Or are you not understanding the purpose of a PK?

 

[steve111]

hi ,

I have this working on another databases without a problem.
if I put the wrong number in and then want to change it there and then I can , if I want to change it later I cannot, but surely if it is not a duplicate number why will it not let me change it
also why will it not bring down the description , drawing number , automatically like it does on the subform

if I remove the Pk but still put ( no duplicates ) it still will not let me change it .

steve

 

[steve111]

what I can do is change it ok in the table. so really confused

 

[GinaWhipp]

If you have this working on another computer with no problem then upload that database because this one is not set up like that one. O compare them because something is different.

Right because if that number appears in the table once it will not let it go in again.

You really need to explain exactly what you are trying to do... what business model does this apply to.

 

[steve111]

ok

for example

I have to make a car wheel . the allparts table has all the parts in it to make any type of wheel and there is no duplicate part in it let say " 1111" , " 2222" " 3333" are in it

then I want to make a wheel for "ford" the assemble number for that wheel is 1111 and the description is" ford wheel" it has 4 parts to make that wheel , nuts tyre, valve , balance weights " so I select 1111 from the allparts file and that goes into the assemble parts table



now I want to make another wheel for " bmw" I want to use assembly number " 2222" and 3 parts used in the ford wheel plus one differnet part for the balance weight this works ok

now lets say I tried to create another wheel for Volvo and selected assembly number 1111 if would say sorry you cannot create a duplicate assembly part number ( this is correct)
now let say I want to change the assembly number 1111 to 3333 it will not let me

I don't understand this as 3333 I not in the table assembly part table only "1111", and " 2222" is


that's what I cannot understand

 

[GinaWhipp]

Got it!

That is correct *Part Number* in *assemble parts table* should be a FK (Foreign Key) not a PK (Primary Key). That will eliminate that error.

As for changing it, well, since you have it set a PK to linked to the *parts* table it is also trying to change it there, hence, the error. Again, changing that to a FK will eliminate that error as well.

 

[steve111]

hi ,

if I remove the pk and just put no duplicates if will still not let me change it

 

[GinaWhipp]

It can't be *No duplicates*, it's a FK in that table there will be duplicates.

 

[steve111]

the table is indexed and with Yes (No Duplicates)
and the field is not the PK

 

[GinaWhipp]

You must change the FK to No indexing in the assemble parts table. You must allow duplicates. Are you not understanding what I am explaining?

 

[steve111]

hi ,

I must be confused

I cannot allow duplicates , I cannot allow someone to create assembly part number 1111 , with 4 parts attached to it , then maybe 6 months later someone creates another BOM with 1111 with a different 4 parts attached to it

steve

 

[GinaWhipp]

Then maybe what you need is BoM table where Users select a pre-assembled product. Because what you are doing now will not allow what you want without making it no duplicates.

 

[steve111]

hi

I managed to find the problem why it did not let change the assemble detail number when it was not a duplicate and still keep this field as no duplicates

the "allow value list edit " was set to NO

 

[GinaWhipp]

Hmm, that is not the same thing... perhaps I should have asked for the exact reading of the message. In any event, glad you figured it out.