Hello,
I've inherited a database and I have been struggling for weeks to find a solution to the run time 3021 - no current record I've been receiving. The code is below. Any help is gladly appreciated.
I've inherited a database and I have been struggling for weeks to find a solution to the run time 3021 - no current record I've been receiving. The code is below. Any help is gladly appreciated.
Code:
Dim MyDB As Database
Dim MyRS As Recordset
Dim Reading1 As Double
Dim Reading2 As Double
Dim ReadingDate1 As Date
Dim ReadingDate2 As Date
Dim Serial1 As String
Dim Serial2 As String
Dim WONum As String
Dim tblTarget As Recordset
Dim RestartCountVal As Long 'setting when counter has been restarted
Dim RestartCountVal1 As Long 'alternative setting when counter has been restarted
Dim NumActivities As Long 'the number of Activities between the measured cycle counts
Dim Include_Type As Long
'turns off warnings
DoCmd.SetWarnings False
'deletes all records in tblMeter_Reading_Volumes
DoCmd.OpenQuery ("qdlMeter_Reading_Volumes_SVMX")
'intialises database and opens "Meter Reading History By Machine_SVMX" as recordset to select each Activity
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Meter Reading History By Machine_SVMX")
Set tblTarget = MyDB.OpenRecordset("tblMeter_Reading_Volumes_SVMX")
RestartCountVal = 9999
RestartCountVal1 = 99999999
NumActivities = 0
If MyRS.RecordCount > 0 Then
'selects first record Serial/Lot Number
MyRS.MoveFirst
'queries each record and appends to tblMeter_Reading_Volumes
Do Until MyRS.EOF
'checks if counter has been reset due to change of PCB or faulty counter
Do Until MyRS![Reading] <> RestartCountVal And MyRS![Reading] <> RestartCountVal1
MyRS.MoveNext
Loop
'checks if there are more than one meter reading for each Asset
Serial1 = MyRS![Serial_Number]
ReadingDate1 = MyRS![DATE_TAKEN]
Reading1 = MyRS![Reading]
WONum1 = MyRS![WO_NUM]
Include_Type = MyRS![Include_Type]
MyRS.MoveNext
'finds oldest meter reading for Assets with more than one reading
If Serial1 = MyRS![Serial_Number] Then
NumActivities = 0
Do While MyRS![Serial_Number] = Serial1
'only counts Activities for Breakdowns, Other Visits and PMs
If Include_Type = 1 Then
NumActivities = NumActivities + 1
End If
MyRS.MoveNext
Loop
MyRS.MovePrevious
Serial2 = MyRS![Serial_Number]
ReadingDate2 = MyRS![DATE_TAKEN]
Reading2 = MyRS![Reading]
WONum2 = MyRS![WO_NUM]
'writes oldest and newest reading to table
'checks that last reading is greater than the previous reading and
'that readings are not on the same day
'that previous reading is not 0 - removed due to not logical, and excludes too many machines
If Asset2 = Asset1 And Reading1 > Reading2 And ReadingDate1 <> ReadingDate2 And ReadingDate1 - ReadingDate2 > 30 Then
tblTarget.AddNew
tblTarget![Serial_Number] = MyRS![Serial_Number]
tblTarget![Product] = MyRS![Product]
tblTarget![Product_Description] = MyRS![Product_Description]
tblTarget![DATE_TAKEN1] = ReadingDate2
tblTarget![Reading1] = Reading2
tblTarget![WO_NUM1] = WONum2
tblTarget![DATE_TAKEN2] = ReadingDate1
tblTarget![Reading2] = Reading1
tblTarget![WO_NUM2] = WONum1
tblTarget![Cycles_Completed] = Reading1 - Reading2
tblTarget![Days_Completed] = ReadingDate1 - ReadingDate2
tblTarget![AVERAGE_CYCLES_PER_MONTH] = ((Reading1 - Reading2) / (ReadingDate1 - ReadingDate2) * 30)
tblTarget![NUM_ACTIVITIES] = NumActivities
tblTarget.Update
End If
End If
Loop
'turns on warnings
DoCmd.SetWarnings True
Exit_Meter_Reading_Volumes_SVMX_Click:
Exit Sub
'Err_Meter_Reading_Volumes_SVMX_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_Meter_Reading_Volumes_SVMX_Click
End If
End Sub
Last edited by a moderator: