Another update query

[Niniel]

Hello,

This is my code:

UPDATE tblSpeakers INNER JOIN [tblRSC-Speakers] ON tblSpeakers.SpeakerID = [tblRSC-Speakers].SpeakerID SET [tblRSC-Speakers].SpeakerID = 404
WHERE ((([tblRSC-Speakers].RSCID)=[forms]![frmRSC].[RSCID]));

What I want it to do is take tblRSC-Speakers and change the SpeakerID to 404 for every record where RSCID equals the currently active RSCID as shown on my form.
For some reason, this only works partially - the first record is changed, but not the other [I have 2 records in my test setup]. First I was asked if I wanted to update 2 records, I clicked OK and was then presented with an error message that said one record failed to update due to key violations.
I don't see where there could be any key violations, but obviously, something is wrong, so I would be grateful if somebody could help me fix this.

Also, how can this be done in code so that I wouldn't need a separate update query?
Right now I'm just calling the query:

Dim stDocName As String
stDocName = "uqryCanceled"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thank you.

 

[Niniel]

I can simplify my query, but it still works, or rather fails to work, the same way.

UPDATE [tblRSC-Speakers] SET [tblRSC-Speakers].SpeakerID = 404
WHERE ((([tblRSC-Speakers].RSCID)=[forms]![frmRSC].[RSCID]));

 

[Niniel]

Oh, I know why it won't work - the operation would create 2 identical entries in another table, tblDisclosures, where SpeakerID and DisclosureDate together form the primary key.

Hm, maybe I should first delete the records, and then append one for the "Canceled" speaker.

 

[Niniel]

Got it!

If anybody's interested, here's the code [I'm not an expert; I'm just modifying code somebody was kind enough to write for me ] -

Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

If Me.Cancelled = True Then
Me.Cancelled1.Visible = True
Me.Cancelled2.Visible = True
strSQL = "Delete * from [tblRSC-Speakers] " & "where [RSCID] = " & Me.[RSCID]
cmd.CommandText = strSQL
cmd.Execute

strSQL = "INSERT INTO [tblRSC-Speakers] (RSCID, SpeakerID) SELECT tblRSC.RSCID, tblSpeakers.SpeakerID FROM tblRSC, tblSpeakers WHERE [SpeakerID]=404 and [RSCID] = " & Me.[RSCID]
cmd.CommandText = strSQL
cmd.Execute

Me.[sfrmRSC-Speakers1].Requery

Set cmd = Nothing