Dim strSQL As String
Dim db As Database
Dim rst As Recordset
Dim strCount As Integer
Set dbs = CurrentDb
strCount = DCount("TRID", "Table - Test Results (Dynamic)", "[Compliance Status1] = 'Non-Compliant' and AIS = Eval ('[Forms]![Form - Main]![HidAIS]') and [Control Acronym]='TST-1'")
If strCount = 0 Then
strSQL = "UPDATE [Table - Test Results (Dynamic)]" _
& "INNER JOIN [Table - Implementation Plan] ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number]" _
& "SET [Table - Implementation Plan].[Implementation Status] = 'Implemented' " _
& "WHERE ((([Table - Test Results (Dynamic)].[Control Acronym]) Like 'TST-1')" _
& "AND (([Table - Test Results (Dynamic)].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')) AND (([Table - Implementation Plan].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')));"
CurrentDb.Execute strSQL
Else:
strSQL = "UPDATE [Table - Test Results (Dynamic)]" _
& "INNER JOIN [Table - Implementation Plan] ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number]" _
& "SET [Table - Implementation Plan].[Implementation Status] = 'Planned' " _
& "WHERE ((([Table - Test Results (Dynamic)].[Control Acronym]) Like 'TST-1')" _
& "AND (([Table - Test Results (Dynamic)].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')) AND (([Table - Implementation Plan].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')));"
CurrentDb.Execute strSQL
End If
Could you prepare sample DB and upload it? Please, show also expected result
Dim strSQL As String
Dim db As Database
Dim rst As Recordset
Dim strCount As Integer
Dim strNewValue As String 'new variable
Dim strSelectedAIS As String 'new variable
Set dbs = CurrentDb
strSelectedAIS = Eval("[Forms]![Form - Main]![HidAIS]")
strCount = DCount("TRID", _
"Table - Test Results (Dynamic)", _
"[Compliance Status1] = 'Non-Compliant' " _
& "and AIS = '" & strSelectedAIS & "' " _
& "and [Control Acronym]='TST-1'")
If strCount = 0 Then
strNewValue = "Implemented"
Else
strNewValue = "Planned"
End If
strSQL = "UPDATE [Table - Test Results (Dynamic)] " _
& "INNER JOIN [Table - Implementation Plan] " _
& "ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number] " _
& "SET [Table - Implementation Plan].[Implementation Status] = '" & strNewValue & "' " _
& "WHERE (([Table - Test Results (Dynamic)].[Control Acronym]) = 'TST-1') " _
& "AND (([Table - Test Results (Dynamic)].AIS)='" & strSelectedAIS & "') " _
& "AND (([Table - Implementation Plan].AIS)='" & strSelectedAIS & "') ;"
CurrentDb.Execute strSQL
Dim strSQL As String
Dim db As Database
Dim rst As Recordset
Dim strCount As Integer
Dim strNewValue As String 'new variable
Dim strSelectedAIS As String 'new variable
Dim strTST As String 'new variable
Dim lngLoop As Long 'new variable
Set dbs = CurrentDb
strSelectedAIS = Eval("[Forms]![Form - Main]![HidAIS]")
For lngLoop = 1 to 512
strTST = "TST-" & lngLoop
strCount = DCount("TRID", _
"Table - Test Results (Dynamic)", _
"[Compliance Status1] = 'Non-Compliant' " _
& "and AIS = '" & strSelectedAIS & "' " _
& "and [Control Acronym]='" & strTST & "'")
If strCount = 0 Then
strNewValue = "Implemented"
Else
strNewValue = "Planned"
End If
strSQL = "UPDATE [Table - Test Results (Dynamic)] " _
& "INNER JOIN [Table - Implementation Plan] " _
& "ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number] " _
& "SET [Table - Implementation Plan].[Implementation Status] = '" & strNewValue & "' " _
& "WHERE (([Table - Test Results (Dynamic)].[Control Acronym]) = '" & strTST & "') " _
& "AND (([Table - Test Results (Dynamic)].AIS)='" & strSelectedAIS & "') " _
& "AND (([Table - Implementation Plan].AIS)='" & strSelectedAIS & "') ;"
CurrentDb.Execute strSQL
Next
The problem begins with storing calculated data. I won't go into why calculated data should not be stored, there are thousands of articles available that talk about data normalization.
I didn't go through all the posts in depth so I apologize if I missed something relevant but updating the entire table with the calculated values solves the problem only for the time being. If you don't update the table every, single time, anyone changes anything relevant, you will immediately be back to where you started. Deep do do with bad data.
How do I get the query to save both those results? Some thing like this:
Dim strCom as string
Dim strNonCom as string
strCom = ???????
strNonCom = ???????
I know how to transfer the SQL into VBA.
Thanks
John