SQL Update queries run fine when stepping through, but not when run.

[Phred]

Access 2007, SQL Server 2008 R2.

The Total Value below is entered into a table called dbo_SPYD. The fields are bound to a table.

The end user enters a set of values the tax assessors office publishes. They relate to one piece of property. After the numbers are entered the end user navigates to another screen where the totals are to be displayed (fields on this form are bound to table) and clicks a "Refresh Button".

Sample of numbers entered. These numbers should generate a "Reduced" from the values from the code that follows.



This set of numbers entered should generate "No Change".



This set of numbers entered should generate "Increase Error".



The following code runs vba which pulls the totals from a Domain aggregate function for each number. The code then tests the numbers against each other to generate the property message such as "Increase Error". When I step through this code it works fine. No errors are generated. The variables RS1, RS2, etc all are populated with the correct numbers. When I run Debug.Print each query runs correctly and prints the proper data. When I check the table the proper data is sometimes written to the table, sometimes not. If I eliminate the STOP and run the code from the button it does not write correctly, or at all. The data is written to an underlying table that is bound to the Second Form PYD, "Property Year Detail".

Private Sub Refresh_Click()
   
    Dim Rs1 As Variant
    Dim RS2 As Variant
    Dim RS3 As Variant
    Dim RS4 As Variant
    Dim RS5 As Variant
    Dim stSQL As String
    
    Dim stNOChange As String '(Error Message)
    Dim stReduced As String  '(Error Message)
    Dim stIncError As String '(Error Message)
    
    Dim lngPYDID As Long 'Holds Primary Key of Destination PYD table.
    
   stIncError = "Increase Error"
   stReduced = "Reduced"
   stNOChange = "No Change"
    
   DoCmd.SetWarnings False

    'KEEP THIS CODE CONTAINS ORIGINAL 5 EQUATIONS AND TEST.
    'EQUATION 1 RS1. AO Proposed AV-1
    'GRABS START VALUE1 FOR AOPROPOSEDAV

   Rs1 = DSum("total", "TotalAOProposedAVResult")
   If IsNull((Rs1) = True Or (Rs1) = 0) Then
   Exit Sub
   End If
    
   lngPYDID = Me.PropYearDetID 'This is the Primary Key of the PYD table.
   
   stSQL = "Update dbo_PropertyYearDetail" & " set AOProposedAV = " & Rs1 & " WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
   DoCmd.RunSQL stSQL
    
'END OF PROCESS FOR AOPROPOSED AV
'EQUATION 2 RS2. AO Initial AV Result-2
'Grabs Value for AO Proposed AV

    RS2 = DSum("total", "TotalAOInitialAVResult")
    If IsNull((RS2) = True Or (RS2) = 0) Then
    Exit Sub
    End If
    stSQL = "Update dbo_PropertyYearDetail" & " set AOinitialAVresult = " & RS2 & " WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    
'EQUATION 2 Test 1

    If RS2 = Rs1 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set AOresult = '" & stNOChange & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If
    
'EQUATION 2, Test 2

    If RS2 < Rs1 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set AOresult = '" & stReduced & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If
    
'EQUATION 2, Test 3

   If RS2 > Rs1 Then
   stSQL = "Update dbo_PropertyYearDetail" & " set AOresult = '" & stIncError & "' WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
   DoCmd.RunSQL stSQL
   End If
    
'EQUATION 3 RS3 AO Certified AV-3

   RS3 = DSum("total", "TotalAOCertifiedAVResult")
   If IsNull((RS3) = True Or (RS3) = 0) Then
   Exit Sub
   End If
   stSQL = "Update dbo_PropertyYearDetail" & " set AOcertifiedAV = " & RS3 & " WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
   DoCmd.RunSQL stSQL
                   
'EQUATION 3, Test 1.

'AOreReviewResult
    If RS3 = RS2 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set AOreReviewResult = '" & stNOChange & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If

'EQUATION 3, Test 2.

    If RS3 < RS2 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set AOreReviewResult = '" & stReduced & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If

'EQUATION 3 Test 3.

    If RS3 > RS2 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set AOreReviewResult = '" & stIncError & "' WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
    DoCmd.RunSQL stSQL
'    Exit Sub
    End If

'EQUATION 4, RS4 BOR Result-4

    RS4 = DSum("total", "TotalBORInitialAVResult")
    If IsNull((RS4) = True Or (RS4) = 0) Then
    Exit Sub
    End If
    stSQL = "Update dbo_PropertyYearDetail" & " set BORInitialAVResult = " & RS4 & " WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    
'EQUATION 4, Test 1.

   If RS4 = RS3 Then
   stSQL = "Update dbo_PropertyYearDetail" & " set BORResult = '" & stNOChange & "' WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
   DoCmd.RunSQL stSQL
   End If

'EQUATION 4, Test 2.

    If RS4 < RS3 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set BORResult = '" & stReduced & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If

'EQUATION 4, Test 3.

    If RS4 > RS3 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set BORResult = '" & stIncError & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If

'EQUATION 5, Test 1.

   RS5 = DSum("total", "TotalBORFinalAVResult")
   If IsNull((RS5) = True Or (RS5) = 0) Then
   Exit Sub
   End If
   stSQL = "Update dbo_PropertyYearDetail" & " set BORFinalAV = " & RS5 & " WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
   DoCmd.RunSQL stSQL
    
'EQUATION 5, Test 1.

  If RS5 = RS4 Then
  stSQL = "Update dbo_PropertyYearDetail" & " set BORreReviewResult = '" & stNOChange & "' WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
  DoCmd.RunSQL stSQL
  End If

'Equation 5, Test 2.

   If RS5 < RS4 Then
   stSQL = "Update dbo_PropertyYearDetail" & " set BORreReviewResult = '" & stReduced & "' WHERE PropYearDetID = " & lngPYDID & ";"
   Debug.Print stSQL
   DoCmd.RunSQL stSQL
   End If

'Equation 5, Test 3.

    If RS5 > RS4 Then
    stSQL = "Update dbo_PropertyYearDetail" & " set BORreReviewResult = '" & stIncError & "' WHERE PropYearDetID = " & lngPYDID & ";"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL
    End If
    
    DoCmd.SetWarnings True
            
   ' Me.Requery
    Me.Refresh
        
End Sub

Here are the results from the debug.print. All values are correct to the data entered.



Update dbo_PropertyYearDetail set AOProposedAV = 140000 WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set AOinitialAVresult = 150000 WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set AOresult = 'Increase Error' WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set AOcertifiedAV = 160000 WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set AOreReviewResult = 'Increase Error' WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set BORInitialAVResult = 170000 WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set BORResult = 'Increase Error' WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set BORFinalAV = 150000 WHERE PropYearDetID = 25;
Update dbo_PropertyYearDetail set BORreReviewResult = 'Reduced' WHERE PropYearDetID = 25;

Each runs fine when I step through. I copied one of the Update statements and pasted it in Access Query Builder and executed it and here is what I get.



I can't tell where my error is. I cant get it to execute correctly. The screen that displays the data from above code does not refresh some times.

Thanks,

Phred

[June7]

The first & is not necessary but should not cause error. The ; is also not necessary but again should not cause error.

stSQL = "UPDATE dbo_PropertyYearDetail SET BORreReviewResult = '" & stIncError & "' WHERE PropYearDetID = " & lngPYDID

The error from the query builder is due to the stSQL=

Try statement without that.

Unfortunately, I've never had to run update action on SQLServer db.

[Phred]

I just sent a message apparently I didn't click the reply button. I made all the changes you recommended. Apparently they all make a difference! The code seems to be running fine and the refresh is working. I need to test more. I will mark this solved after I test more. Your suggestion made the difference.

Thanks Phred

[orange]

Phred,

Just a comment for consideration:

When you set warnings False, you are basically telling Access not to give you messages.
This was an old practice with DoCmd.RunSQL

An alternate method is to use db.Execute yourActionSql, dbFailOnError
You do not have to do the set warnings, and Access will inform you if an error occurs -- if no error, no message.

You Dim db as DAO.Database, then set db = currentdb

YourActionSQL represents the name of your Update/DELETE/Insert SQL string.

[Phred]

Thanks Orange. I did not know this. A few Questions. I currently use MZ Tools and I plan to add error handlers when code is finalized. Would your alternate error handler replace the MZ Error Handlers or would your suggestion be incorporated within the Error Handler? How would the end user be notified if an error occurred? I had planned to build a SQL table to log all errors as they occur. So your recommendation would look like this:

Dim various variables
Dim DB as DAO.Database
set db = current db

Eliminate Dim stSQL as string.
Eliminate "stSQL =" My SQL Statement
Eliminate doCMD.RunSQL stSQL

Add

db.execute "Update dbo_PropertyYearDetail" & " set BORResult = '" & stNOChange & "' WHERE PropYearDetID = " & lngPYDID & ";"

Is this syntax correct? Where does the ",dbFailOnError" go?

Thanks, Phred

[June7]

Set db = CurrentDb

db.Execute "UPDATE dbo_PropertyYearDetail SET BORResult = '" & stNOChange & "' WHERE PropYearDetID = " & lngPYDID, dbFailOnError

or don't declare and set db variable, just:

CurrentDb.Execute ...

[ssanfu]

Eliminate Dim stSQL as string.
Eliminate "stSQL =" My SQL Statement

I don't think orange was saying to remove "stSQL = ". You can use "stSQL = " and have a command like June posted.

db.Execute stSQL,  dbFailOnError

I DO use a string variable to create my SQL strings. The reason being that when I step through the VBA code, I can see what the string looks like before it executes (whether I hover over the variable or use Debug.Print). Much easier to debug the code.

It is a lot easier to create a query in code using the variable and Debug.Print. Just before the execute command, have the debug line. Break the code on the line after the debug statement. Copy the SQL in the immediate window, then create a query, switch to SQL view and paste in the copied string. Try executing the query. If it executes, the SQL is correct - or at least it doesn't have syntax errors.
You then can see what records are returned.


I construct the SQL like this:

sSQL = "UPDATE dbo_PropertyYearDetail"
sSQL = sSQL & " SET AOProposedAV = " & Rs1 
sSQL = sSQL & " WHERE PropYearDetID = " & lngPYDID & ";"

I have the key words in caps and break the like on each clause (UPDATE, SET, WHERE).
It takes a few more lines but, for me, it is easier reading and understanding.

[orange]

Steve has interpreted my post correctly. I always suggest that you Dim a string variable, assign it "" or the beginning of your SQL statement, then modify the variable/string as you build your final SQL.
And, do a debug.print stSQL as you proceed to check that you code has been rendered by Access as you intended.

I am a big fan of MZTools--have used it for years. I often recommend using "Add Both" procedure header and error handler. I also recommend Smart Indenter - a free utility to reformat your vba to consistent indents/format.

When using db.execute.... ,dbFailOnError if there is an error, it will be presented to you.

[ItsMe]

...
Each runs fine when I step through. I copied one of the Update statements and pasted it in Access Query Builder and executed it and here is what I get...

Because you are using SQL Server you will get this error when executing via a Module. You can use some Statements in a Query Object that you cannot use via VBA. If you read the error message it will list out what is available. IIRC, the only option is to click the object within the Nav Pane. Any Execute command will analyze the SQL within the object and check for errors prior to executing.

If you are still working on this I can check my notes to see what I have done as workarounds. Let me know.

[Phred]

So is this the proper construction?

db.Execute stSQL, sSQL = "UPDATE dbo_PropertyYearDetail"
sSQL = sSQL & " SET AOProposedAV = " & Rs1
sSQL = sSQL & " WHERE PropYearDetID = " & lngPYDID & ";", dbFailOnError

What kind of error message is generated. Is it something user friendly?

[Phred]

So this is what I am trying on one of my queries but I get a Function not defined error. Are you using sSQL in place of my stSQL? Should I make it say stSQL all the way through? I capitalized the UPDATE, SET WHERE and broke it apart.

dbExecute "stSQL = UPDATE dbo_PropertyYearDetail"
SSQL = SSQL & " SET TotalLandSF = " & lngTotLandSf & ", TotPYD = " & lngTotPYDRec & ", TotSpin = " & lngTotSPINRec & ", TotSPYD = " & lngTotSPYDRec & " "
SSQL = SSQL & "WHERE PropYearDetID = " & lngPYDID & " dbFailOnError;"

Thanks.

[orange]

No, your syntax is incorrect.

Dim stSQL as string
stSQL = "UPDATE dbo_PropertyYearDetail "
SSQL = SSQL & " SET TotalLandSF = " & lngTotLandSf & ", TotPYD = " & lngTotPYDRec & ", TotSpin = " & lngTotSPINRec & ", TotSPYD = " & lngTotSPYDRec & " "
SSQL = SSQL & " WHERE PropYearDetID = " & lngPYDID
Debug.Print stSQL
db.execute stSQL, dbFailOnError

[ItsMe]

The original error you were getting was probably because of a syntax error. I believe June mentioned there was a problem there. What you posted in post #11 won't work. I tested an UPDATE query using VBA and was able to get it to work. Here is what I tested. Meanwhile, I will take a look at your code and see what I can come up with.

Dim strSQL As String
strSQL = "UPDATE lstStates SET lstStates.StateDesc = 'Food' WHERE (((lstStates.StateKey)=81));"
CurrentDb.Execute strSQL, dbSeeChanges

[ItsMe]

As Orange pointed out in post #12, your syntax is incorrect. Because of the SQL Server thing I added the semicolon and also changed dbfailonerror to dbseechanges.

Dim stSQL As String

stSQL = "UPDATE dbo_PropertyYearDetail " & _
        "SET TotalLandSF = " & lngTotLandSf & ", TotPYD = " & lngTotPYDRec & ", TotSpin = " & lngTotSPINRec & ", TotSPYD = " & lngTotSPYDRec & " " & _
        "WHERE PropYearDetID = " & lngPYDID & ";"

 Debug.Print stSQL

 db.Execute stSQL, dbSeeChanges

[Phred]

Is this correct? I get a compile error. Variable not defined. It highlights db in the last line. What kind of variable should this be?

stSQL = "UPDATE dbo_PropertyYearDetail " & _
    "SET TotalLandSF = " & lngTotLandSf & ", TotPYD = " & lngTotPYDRec & ", TotSpin = " & lngTotSPINRec & ", TotSPYD = " & lngTotSPYDRec & " " & _
    "WHERE PropYearDetID = " & lngPYDID & ";"
    
    Debug.Print stSQL
    
    db.Execute stSQL, dbSeeChanges

Phred