Key violation (1 Viewer)

[BeeJayEff]

This must be sooo obvious; why can I not find what's going on ?
When I execute the following, I get a key violation error.

strSQLString = "UPDATE OfferForSale " & _
    "SET OfferForSale.SoldFlag = " & True & _
    ", OfferForSale.fkSalesRecordID = " & SalesRecordID & _
    ", OfferForSale.HighestBid = " & mSold(mintNumberOfItems).SoldFor & _
    " WHERE OfferID = " & lngOfferForSale & ";"
.
.
DoCmd.RunSQL strSQLString

If I remove the SalesRecordID update, the update works :

strSQLString = "UPDATE OfferForSale " & _
    "SET OfferForSale.SoldFlag = " & True & _
    ", OfferForSale.HighestBid = " & mSold(mintNumberOfItems).SoldFor & _
    " WHERE OfferID = " & lngOfferForSale & ";"
.
.
DoCmd.RunSQL strSQLString

OfferForSale.fkSalesRecordID is a long integer, while SalesRecordID is the primary key of a record newly added to the SalesRecords table. It has been saved by means of Me.Dirty=False and I can check that the new record, with that ID, is indeed in the table before executing this SQL.

Just had a thought - the SalesRecords table is in my front-end while I am testing, whereas the OfferForSale table is on the back-end server. Could that be anything to do with it ?

 

[The_Doc_Man]

Key violation errors almost always refer to either (a) having two records with the same key when that key was declared as unique, or (b) missing a corresponding parent record when trying to enter a new child record. I note that the field in question is marked as a foreign key in the table you are updating, which means it is a PK in some other table. If there is a relationship in place, you might be creating two entries (if that item being updated is actually a JOIN query)l\

 

[gemma-the-husky]

I expect the backend is the problem.

check to see how the relationship is defined in the back end. If the SalesRecords table is in the backend, that will be the one being used.

 

[BeeJayEff]

Hi and thanks for the responses. On further reflection I think it must be because the OfferForSale table (with the foreign key) is in the back-end, whereas the SalesRecords table (containing the primary key) is in the front-end, and logically such a relationship should not be allowed as other front-ends will not necessarily have the SalesRecords table.
Unfortunately I cannot now get to the database until the middle of next week, but I shall try then to move the table into the b-e and set up the appropriate relationship there.

Thanks again.