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".
Code:
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