Run Time 3021 No Current record found (1 Viewer)

Msmartine

Registered User.
Local time
Today, 11:30
Joined
Sep 15, 2014
Messages
26
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.


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:

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:30
Joined
Aug 30, 2003
Messages
36,118
The way to figure it out is to identify the line causing the error. Hitting debug when you get the error should bring you to the line. If there's an "On error goto..." line temporarily comment it out.
 

Msmartine

Registered User.
Local time
Today, 11:30
Joined
Sep 15, 2014
Messages
26
Hi Paul,

Thank you for your reply. I'm sorry, I just realized I specify the line.

The error is on:

Do While MyRS![Serial_Number] = Serial1
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:30
Joined
Aug 30, 2003
Messages
36,118
Curious. I was thinking that the

MyRS.MoveNext

previous to that would have moved it to EOF, but then the line between should error. Now I'm thinking that the MoveNext inside the loop moves it to EOF, then loops back to the test. Try adding a test for it there and taking the appropriate action.

If MyRS.EOF Then
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:30
Joined
Aug 30, 2003
Messages
36,118
I was thinking here:

Code:
                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
                '[COLOR="Red"]add test here[/COLOR]
                Loop
 

Msmartine

Registered User.
Local time
Today, 11:30
Joined
Sep 15, 2014
Messages
26
Unfortunately, that didn't work. The targettble is no longer populating.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:30
Joined
Aug 30, 2003
Messages
36,118
Can you attach a sample db here with instructions on how to recreate the problem?
 

JHB

Have been here a while
Local time
Today, 16:30
Joined
Jun 17, 2012
Messages
7,732
Comment out the line "DoCmd.SetWarnings" until your code runs okay, then maybe you'll get another error.
Else post your database with some sample data + description to reproduce the error you get.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 28, 2001
Messages
26,999
Do While MyRS![Serial_Number] = Serial1

Error 3021 in this case suggests that there is no record associated with the recordset opened through MyRS.

In this code segment, look at the highlighted items:

Code:
    If MyRS.RecordCount > 0 Then
    'selects first record Serial/Lot Number
    [COLOR="Red"]MyRS.MoveFirst[/COLOR]
    
    'queries each record and appends to tblMeter_Reading_Volumes
     [COLOR="red"]Do Until MyRS.EOF[/COLOR]
     
    
            
    'checks if counter has been reset due to change of PCB or faulty counter
     [COLOR="red"]Do Until MyRS![Reading][/COLOR] <> RestartCountVal And MyRS![Reading] <> RestartCountVal1
            
     [COLOR="red"]MyRS.MoveNext[/COLOR]
            Loop
            
                'checks if there are more than one meter reading for each Asset
                Serial1 = MyRS![Serial_Number]

You are looping twice on the same recordset content and it APPEARS that you could never do anything for the first record because the next significant action on MyRS is a .MoveNext, or so it appears. In the specific case where there is one and only one record in MyRS, you skip it and at that point, if the next record isn't there, you have not tested for MyRS.EOF yet based on the apparent flow of the loops.
 

Msmartine

Registered User.
Local time
Today, 11:30
Joined
Sep 15, 2014
Messages
26
Thanks everyone for your replies. I tried all suggestions with no luck. I also tried uploading a sample of the database but I received an error message about a missing security token.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
I think you could likely do it simpler, but I am not completely certain on all your logic. The multiple loops can get real confusing. If I understand correctly you are getting the first and last record for each serial number. If that is the case I would get an RS that returns all the serial numbers. Then Loop that creating another recordset for each serial number. This is a lot easier than looping the whole thing checking for changes

Also it appears to me that this just moves passed any record with a Reading of 9999 or 99999999
Code:
     Do Until MyRS![Reading] <> RestartCountVal And MyRS![Reading] <> RestartCountVal1
          MyRS.MoveNext
     Loop
If that is the case you could do
Code:
dim strSql as string
strSql = "Select * from [Meter Reading History By Machine_SVMX] where Reading <> 9999 or Reading <> 99999999"
Set MyRS = MyDB.OpenRecordset(strSql)

something like
Code:
Public Sub testit()
        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 NumActivities As Long 'the number of Activities between the measured cycle counts
        Dim Include_Type As Long
        Dim RS_SN as dao.recordset 
       
       '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
        strSql = "Select Distinct Serial_Number from [Meter Reading History By Machine_SVMX]"
        set RS_SN = db.openrecordset(strSql)

        do while not RS_SN.eof
           'loop the serial numbers an create a unique RS for each serial number
           strSql = "Select * from [Meter Reading History By Machine_SVMX] where Reading <> 9999 or Reading <> 99999999"
           strSql = strSql & " AND Serial_Number = '" & RS_SN!Serial_Number & "'"
            set MyRS = DB.openrecordset (StrSql)
           'you have the records just for that serial number
            NumActivities = MyRS.Recordcount  
            'you are at the first record of that serial number so
             Serial1 = MyRS![Serial_Number]
             ReadingDate1 = MyRS![DATE_TAKEN]
             Reading1 = MyRS![Reading]
             WONum1 = MyRS![WO_NUM]
             Include_Type = MyRS![Include_Type]
             'Move to last record in serial number
             MyRS.movelast
             Serial2 = MyRS![Serial_Number]
             ReadingDate2 = MyRS![DATE_TAKEN]
             Reading2 = MyRS![Reading]
             WONum2 = MyRS![WO_NUM] 
             'put the rest of the code here to update the target
            rs_SN.movenext
        loop

However, If I understand correctly maybe the whole thing can be done without any code or much simpler with an aggregate query. Can you explain the data a little better and what this does?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
After re-reading the NumActivities has another filter on it where Include_Type = 1. Again instead of looping I would use a query and a function

Code:
Public Function getActivities (serialNumber as string) as long
  dim strSql as string
  dim rs as dao.recordset
  strSql = "Select * from [Meter Reading History By Machine_SVMX] where Reading <> 9999 or Reading <> 99999999"
  strSql = strSql & " AND Serial_Number = '" & SerialNumber & "'"
  strSql = strSql & " AND Include_Type = "         
  set RS = currentdb.openrecordset (strSql)
  getActivities = RS.recordcount
end function
You even could use a simple dcount instead

Then replace this line
NumActivities = MyRS.Recordcount
with
NumActivities = getActivities(RS_SN!Serial_Number)
 

Users who are viewing this thread

Top Bottom