Sub Check()
Dim Machine As String
Dim Desc As String
Dim MachineCode As String
Dim Status As string
Dim id as Long
Dim TimeMs as long
Set dbs = DBEngine(0)(0)
Set TabMachine = dbs.OpenRecordset("TabMachine", DB_OPEN_DYNASET)
Set AlarmManConta = dbs.OpenRecordset("SELECT TabMachine.ID, TabMachine.[My date], TabMachine.[Date], TabMachine.[ms], TabMachine.[Machine], TabMachine.[MachineCode], TabMachine.[Description], TabMachine.[Status], TabMachine.[Tag], TabMachine.[TimeMs] , TabMachine.[Check] FROM TabMachine WHERE (TabMachine.[Check] is Null) and TabMachine.[Tag] like '*KKK' and ((TabMachine.[Status])='Alarm' Or (TabMachine.[Status])='MAN') ORDER BY TabMachine.[TimeMs];", DB_OPEN_DYNASET)
Do While Not AlarmManConta.EOF
Set AlarmMan = dbs.OpenRecordset("SELECT TabMachine.ID, TabMachine.[My date], TabMachine.[Date], TabMachine.[ms], TabMachine.[Machine], TabMachine.[MachineCode], TabMachine.[Description], TabMachine.[Status], TabMachine.[Tag], TabMachine.[TimeMs] , TabMachine.[Check] FROM TabMachine WHERE TabMachine.[Tag] like '*KKK' and ((TabMachine.[Status])='Alarm' Or (TabMachine.[Status])='MAN') and (TabMachine.[Check] is Null) ORDER BY TabMachine.[TimeMs];", DB_OPEN_DYNASET)
If AlarmMan.RecordCount = 0 Then
Exit Sub
End If
AlarmMan.MoveFirst
Machine = AlarmMan.Fields(4).Value
MachineCode = AlarmMan.Fields(5).Value
Desc = AlarmMan.Fields(6).Value
Status = AlarmMan.Fields(7).Value
TimeMs = AlarmMan.Fields(9).Value
id = AlarmMan.Fields(0).Value
If Status = "MAN" Or Status = "Alarm" Then
Set AlarmMan2 = dbs.OpenRecordset("SELECT TabMachine.ID, TabMachine.[My date], TabMachine.[Date], TabMachine.[ms], TabMachine.[Machine], TabMachine.[MachineCode], TabMachine.[Description], TabMachine.[Status], TabMachine.[Tag], TabMachine.[TimeMs], TabMachine.[Check] FROM TabMachine WHERE TabMachine.[TimeMs] < " & TimeMs & " AND TabMachine.[Description]= '" & Desc & "' and TabMachine.[Machine]= '" & Machine & "' and TabMachine.[MachineCode]= '" & MachineCode & "' and TabMachine.[Tag] like '*KKK' and ((TabMachine.[Status])='Alarm' Or (TabMachine.[Status])='MAN') and ((TabMachine.[Check] is Null) or TabMachine.[Check]= '0') ORDER BY TabMachine.[TimeMs];", DB_OPEN_DYNASET)
If AlarmMan2.RecordCount <> 0 Then
AlarmMan2.MoveLast
id2 = AlarmMan2.Fields(0).Value
If AlarmMan2.Fields(0) <> id Then
If Status <> AlarmMan2.Fields(7).Value Then ' Foi Check e tem par, escrevo 1
TabMachine.FindFirst "[ID] Like " & id & ""
TabMachine.Edit
TabMachine.Fields(11) = "1"
TabMachine.Update
TabMachine.FindFirst "[ID] Like " & id2 & ""
TabMachine.Edit
TabMachine.Fields(11) = "1"
TabMachine.Update
ElseIf Status = AlarmMan2.Fields(7) Then ' KKKa repetido, devo apagar
TabMachine.FindFirst "[ID] Like " & id2 & ""
TabMachine.Edit
TabMachine.Fields(11) = "2"
TabMachine.Update
TabMachine.FindFirst "[ID] Like " & id & ""
TabMachine.Edit
TabMachine.Fields(11) = "0"
TabMachine.Update
End If
End If
Else
TabMachine.FindFirst "[ID] Like " & id & ""
TabMachine.Edit
TabMachine.Fields(11) = "0"
TabMachine.Update
End If
AlarmMan.MoveNext
End If
Loop
DoCmd.RunSQL "DELETE * from TabMachine where TabMachine.[Check] ='2'"
End Sub
[note: I asked in other post for help with an similar issue, that was told me to use SQL(the code is above) that worked really good for few registers but isn't working so well with more registers- i'm working with arround 50.000 registers of Alarm/Man]
DELETE *
FROM AlarmMan
WHERE ((((SELECT TOP 1 Dupe.Status
FROM AlarmMan AS Dupe
WHERE Dupe.TimeMS >AlarmMan.TimeMS
AND Dupe.Machine = AlarmMan.Machine
AND Dupe.Area = AlarmMan.Area
AND Dupe.MachineCode = AlarmMan.MachineCode
ORDER BY Dupe.TimeMS ASC,Dupe.ID))=[AlarmMan..[Status]));
!!
