Simple update query - odd behavior

[smollenk]

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.

 

[pbaldy]

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?

 

[isladogs]

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

 

[smollenk]

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?

 

[smollenk]

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

 

[smollenk]

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

 

[isladogs]

You can click the Thanks button

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

 

[pbaldy]

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

You just did.

 

[Deleted member 147267]

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