Simple update query - odd behavior (1 Viewer)

I think there is something really obvious I am missing but I can't figure it out - I've been teaching myself vba and I'm still pretty novice.

I am trying to update a table using a sql query in vba and I'd like to update multiple rows at the same time with client IDs that are within some vector of values. My code is below:

' Run a pre-defined insertquery to add a row to the Client table (1 row per client)
DoCmd.RunSQL insertquery

' Find out what the autogenerated clientKey is for this newly entered row in the Client table
Dim newclientKey As Integer
newclientKey = DMax("clientKey", "Client")

' In a separate table (linkClientConsultation) use this newclientKey to replace old clientKey values equal to 36 or 38. These numbers are drawn from commands on the form called Me.clientAkey.Value and Me.clientBkey.Value
updatequery = "UPDATE linkClientConsultation SET clientKey = " & newclientKey & " WHERE clientKey IN (" & Me.clientBkey.Value & "," & Me.clientAkey.Value & ");"
DoCmd.RunSQL updatequery

The Debug.Print version of the query looks like this:
UPDATE linkClientConsultation SET clientKey = 41 WHERE clientKey IN (36,38);

However, while I'd expect this to replace the value of clientKey in the linkClientConsultation where it equals 36 or 38 with 41, it only replaces one of the two values - always the first of the two numbers but not both. Is this a syntax thing? Or a order of operations thing?

Sorry if this is a repeat.

The syntax looks fine to me. You're saying there's a record with an ID of 38 and it doesn't get updated? If you paste that SQL into a new query and run it as a test, does it update that record?

First of all green text isn't very easy to read!

Is clientkey a Number datatype PK field in the linkClientConsultation table?
If so it has to be unique so only one record can be updated

Similarly if its a non PK field but indexed with Allow Duplicates = No

In either case Access should show an error message about a key violation

Yeah, it doesn't get updated. Only the 36 does.

I ran it was a new query (which I hadn't thought of doing before so thank you for that trouble-shooting tip). It gives this error:

Microsoft Access didn't update 0 field(s) due to a type conversion failure, 1 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations. Do you want to continue running this type of action query anyway?

Ah - @isladogs - I forgot about that. It one of two columns that comprise the primary key. Maybe that is it...

Thank you both for your help! How do I give "Thanks" on here?

You can click the Thanks button

Please also mark the thread as solved - drop down marked Thread Tools

smollenk said:

Thank you both for your help! How do I give "Thanks" on here?

Click to expand...

You just did.

By any chance you left out code that would have shown that you turn off warnings before attempting to run this query? I ask because you made no mention of the message until you attempted the same with an actual query. Maybe research DoCmd.Execute or take a look at
http://allenbrowne.com/ser-60.html