append query fails due to referential integrity (1 Viewer)

[myrt]

Hi, I need your advice because I don't know how to bypass the problem. I have two tables : (1) Dett_Lav , (2) MaterialiConformità.
They are thus linked: (1) IDDett_Lav <---one - to- many ---> (2) Dett_LavID
I'd like to preserve the referential integrity.
In vba I wrote down this codethat does work if I delete the referential interity, otherwise it fails due to (foreign) key violation. I do insert the absolutely necessary Dett_LavID with the append query, so I'm not sure where lays the problem and how to bypass it.

strSQL = "INSERT INTO MaterialiConformità (MaterialeID, DettLavorazioneID) " & _
         "SELECT Art_Mate.ArticoloID, Art_Mate.MaterialeID FROM " & _
         "((Articoli INNER JOIN Art_Mate ON Articoli.IDArticolo = Art_Mate.ArticoloID) " & _
         "INNER JOIN Dett_Lavorazioni ON Articoli.IDArticolo = Dett_Lavorazioni.ArticoloID) " & _
         "WHERE Art_Mate.ArticoloID = " & strArticoloID & _
         " AND Dett_Lavorazioni.IDDett_lavorazione = " & Me.Parent.IDDett_Lavorazione

DoCmd.RunSql StrSQL

 

[JHB]

Show a printsscreen of the relationship-window.

 

[myrt]

Show a printsscreen of the relationship-window.

Here it is.

Perhaps a solution could be to drop the referential integrity beforehand and later restore it. However, this doesn't really delve into the problem. So, I'm not sure if by using such sort of patch (which, by the way, I don't have the ablity to write down), I may incur later in strange orphan data or sth similar.

 

[JHB]

Is IDMatConformità an Autonumber field?

 

[myrt]

Yes, it is

 

[myrt]

Thanks for your interest! I found the problem. I was copying MaterialeID instead of IDDettLavorazione in the MaterialiConformità.DettLavorazioneID field.
So the script of the SQL string read correctly the variables involved, however I didn't noteced that I put the wrong field name in the instruction. Thanks!!

 

[JHB]

Good you got it solved.