So I have the below...
Sub test4()
Dim db As DAO.Database
Dim Source_rst As DAO.Recordset
Dim Dest_rst As DAO.Recordset
Set db = CurrentDb
Set Source_rst = db.OpenRecordset("SELECT DISTINCTROW [Scorecard Last Ran].[Scorecard ID], Round(([DQS - Scorecard History].[No Passed]/([DQS - Scorecard History].[No Passed]+[DQS - Scorecard History].[No Failed]))*100,1) & '%' AS Score FROM [DQS - Scorecards] INNER JOIN ((SELECT [DQS - Scorecard History].[Scorecard ID], Max([DQS - Scorecard History].[Date/Time Scorecard Ran]) AS [MaxOfDate/Time Scorecard Ran] FROM [DQS - Scorecard History] GROUP BY [DQS - Scorecard History].[Scorecard ID]) AS [Scorecard Last Ran] INNER JOIN [DQS - Scorecard History] ON ([Scorecard Last Ran].[MaxOfDate/Time Scorecard Ran] = [DQS - Scorecard History].[Date/Time Scorecard Ran]) AND ([Scorecard Last Ran].[Scorecard ID] = [DQS - Scorecard History].[Scorecard ID])) ON [DQS - Scorecards].ID = [DQS - Scorecard History].[Scorecard ID];")
Set Dest_rst = db.OpenRecordset("DQS - Scorecards")
If (Source_rst.BOF And Source_rst.EOF) Or (Dest_rst.BOF And Dest_rst.EOF) Then Exit Sub
While (Not Source_rst.EOF)
Dest_rst.MoveFirst
While (Not Dest_rst.EOF)
If Dest_rst![ID] = Source_rst![Scorecard ID] And Dest_rst![ID] <> 3 Then
Debug.Print Source_rst![Scorecard ID] & " - " & Source_rst![Score]
Dest_rst.Edit
Dest_rst![Score] = Source_rst![Score]
Dest_rst.Update
DoEvents
End If
Dest_rst.MoveNext
Wend
Source_rst.MoveNext
Wend
End Sub
Fairly straightforward right? Except if I remove the Dest_rst![ID] <> 3 it hangs, even if I leave it for hours. There is nothing special about record 3, and the data going in is the same, a percentage formatted as a string. Works great if I exclude this record.
I know an UPDATE query would be more efficient but that hangs too. Anything I am missing?!
The Dest_rst is a very small SharePoint list.
Turns out my SharePoint list was just being an arse. Completely deleted the list from my DB, re-added it, all was well, sanity restored!