Update table value with SQL

[sbflood]

Hello,

I am trying to update a record value in a table with a user supplied value.

Here's what I'm trying to do: A user logs a test against a serial number (there are multiple tests per serial so the tests are numbered) and logs how many repetitions will be performed in that test. If a failure occurs and they have to abort the test, I want to change the number of repetitions from what was supposed to run, to what was actually run. So the user enters how many tests were actually run and the code is supposed to find the matching serial number and test number in my Test Runs table and replace the Run Scripts value with the user supplied update.

My code runs without error but the record doesn't update. I've changed my table value types from numbers to text without result, I'm thinking there might be a problem with quotation marks? So it's searching for serial "123-321" instead of 123-321? Any help would be appreciated!

My code is below:

serial = Forms("Enter Run Failure").Controls("Serial")
runNum = Forms("Enter Run Failure").Controls("Run Number")
abortNum = Forms("Enter Run Failure").Controls("txtAbort")


Dim SQL As String
SQL = "UPDATE [Test Runs] " & _
"SET [Run Scripts] = " & abortNum & " " & _
"WHERE [Serial] = " & serial & " AND [Run Scripts] = '" & runNum & "'"

If Not IsNull(Forms("Enter Run Failure").Controls("txtAbort")) Then
DoCmd.RunSQL SQL
End If

 

[CJ_London]

"WHERE [Serial] = " & ser & " AND [Run Scripts] = '" & rn & "'"

where do ser and rn come into it?

 

[MarkK]

If you use a querydef your code is much more reliable, because the querydef handles the delimiters automatically, and it's more readable, but slightly longer. Consider . . .

   const SQL as string = _
      "UPDATE [Test Runs] " & _
      "SET [Run Scripts] =  p0 " & _
      "WHERE [Serial] = p1 AND [Run Scripts] = p2"

   dim frm as form
   set frm = Forms("Enter Run Failure")

   if not isnull(frm!txtAbort) then
      with currentdb.createquerydef("", sql)
         .parameters("p0") = frm!txtAbort
         .parameters("p1") = frm!Serial
         .parameters("p2") = frm![Run Number]
         .execute dbFailOnError
         .close
      end with
   end if

 

[sbflood]

where do ser and rn come into it?

Oh my fault, I edited what the variables were called for posting purposes and forgot to change the code. It's edited now.

 

[sbflood]

If you use a querydef your code is much more reliable, because the querydef handles the delimiters automatically, and it's more readable, but slightly longer. Consider . . .

   const SQL as string = _
      "UPDATE [Test Runs] " & _
      "SET [Run Scripts] =  p0 " & _
      "WHERE [Serial] = p1 AND [Run Scripts] = p2"
 
   dim frm as form
   set frm = Forms("Enter Run Failure")
 
   if not isnull(frm!txtAbort) then
      with currentdb.createquerydef("", sql)
         .parameters("p0") = frm!txtAbort
         .parameters("p1") = frm!Serial
         .parameters("p2") = frm![Run Number]
         .execute dbFailOnError
         .close
      end with
   end if

This worked perfectly, thank you! I haven't seen querydef before, but will definitely add that to my list of things to study. Thanks again!