You cannot add or change a record because a related record is required

[mortkc]

When trying to add items to a database table using INSERT INTO, I am receiving the following error:

You cannot add or change a record because a related record is required in table 'items'

I am using the following code:

strSQL = "INSERT INTO Auctions(ItemID,StartBid,BidStatus,EndDate)Values(" & "'" & strItemID & "'" & "," & strStartBid & "," & strBidStatus & "," & "#" & strEndDate & "#)"

objConn.Execute strSQL

Any suggestions would be appreciated. Thanks in advance!

 

[lespaul]

My first guess would be that you are trying to insert a record into Auctions with a BidStatus that doesn't exist in the tblBidStatus or an ItemId that doesn't exist in tblItem.

If you have referential integrity enforced that means that if you have:
tblBidStatus:
StatusID 1, 2, 3
StatusDescription New, Amended, Deleted

and you are trying to insert BidStatus '4' into tblAuctions, you will get the above error message.

HTH


Leslie

 

[mortkc]

Thank you, I was trying to put ItemID in the Auctions table, but forgot to add it to the Items table. This happens to be the field that they were linked on. Thanks for your help.

 

[willir]

mortkc

Have not heard if Leslie's suggestion worked.

If you are still struggling, put a STOP command


strSQL = "INSERT INTO Auctions(ItemID,StartBid,BidStatus,EndDate)Values(" & "'" & strItemID & "'" & "," & strStartBid & "," & strBidStatus & "," & "#" & strEndDate & "#)"

[blue]STOP[/blue]

objConn.Execute strSQL

Then bring up the "immediate" or debug window, use CTRL-G.

The type...
[blue]? strSQL[/blue]

This will display your SQL sentance.

You can copy this to the query design table, and, if there is a syntax problem and after removing the special characters, it will indicate where.

Hint:
I personally tend to stay away from using quotes in quotes. Works most of the time but not always.
Hint:
strStartBid does not include quotes. If it is a text value, quotes are required.
Hint:
You seem to be missing spaces in the string. I have flagged these in red.
Hint:
Is ItemID an autonumber? You can not inset the autonumber. Since this is a text field, I assume it is okay.

DIM strQ as String, strSQL as String

strQ = Chr$(13) ' ascii character for double quote

strSQL = "INSERT INTO Auctions(ItemID[red], [/red]StartBid[red], [/red]BidStatus[red], [/red]EndDate[red]) [/red]Values(" & [blue]strQ[/blue] & strItemID & [blue]strQ[/blue] & [red]", "[/red] & [blue]strQ[/blue] & strStartBid & [red]", "[/red] & strBidStatus & [red]", "[/red] & "#" & strEndDate & "#)"

objConn.Execute strSQL


Richard