'No current record' on dataset with hundreds of records (1 Viewer)

Alc

Registered User.
Local time
Today, 12:00
Joined
Mar 23, 2007
Messages
2,407
I'm hoping someone can explain to me - in a way that, say, an idiot could understand - how Access determines that it has reached the end of a record set?

I'm running the following code
Code:
    strSQL = "SELECT PQS_Response_Data.Ownership_Period, PQS_Response_Data.Model_Year, " & _
             "PQS_Response_Data.Model_Name, PQS_Response_Data.Report_Month, tblModel.ProductName " & _
             "FROM PQS_Response_Data LEFT JOIN tblModel ON PQS_Response_Data.Model_Name = tblModel.ModelName " & _
             "GROUP BY PQS_Response_Data.Ownership_Period, PQS_Response_Data.Model_Year, " & _
             "PQS_Response_Data.Model_Name, PQS_Response_Data.Report_Month, tblModel.ProductName;"
    Set Rst = Db.OpenRecordset(strSQL)
    If Not Rst.EOF Then
        Rst.MoveFirst
        Do While Not Rst.EOF
            strModelYear = Rst!Model_Year
            strModelName = Trim(Rst!Model_Name)
            strProductName = Trim(Rst!ProductName)
            ldSurveyMonth = Rst!Report_Month
            liOwnershipPeriod = Rst!Ownership_Period
            liMonthNo = Set_Month_No(Db, ldSurveyMonth, strModelYear, strModelName, liOwnershipPeriod)
            
            If liMonthNo = 0 Then
                GoTo Process_Complete
            End If
            
            Db.Execute ("INSERT INTO tblPPHUSummary " & _
                        "(Ownership_Period, MYR, Model, Survey_Month, Product_Line, Month_No) " & _
                        "VALUES (" & _
                        liOwnershipPeriod & ", " & strModelYear & ", '" & strModelName & "', '" & ldSurveyMonth & "', " & _
                        "'" & strProductName & "', " & liMonthNo & " " & _
                        ")")

            [COLOR="Red"][B]Rst.MoveNext[/B][/COLOR]
        Loop
    End If
1. If I run that SQL query on it's own it returns 279 records, as expected.
2. If, just before 'Rst.MoveFirst' line I add
Code:
Rst.Movelast
Msgbox Rst.recordcount
I get '279', as expected
3. The first record gets inserted into the relevant table with no problems.
However, every time I run the code as is, it hits 'Rst.MoveNext' (in red) and I get 'Run time error 3021: no current record'. I've tried testing with MoveLast at that point and get the same error message. What could be preventing me from moving to the next record in the set?
 

isladogs

MVP / VIP
Local time
Today, 16:00
Joined
Jan 14, 2017
Messages
18,186
This looks a bit odd and I've never used it: Do While Not Rst.EOF

Try this slightly modified code

Code:
...
Set Rst = Db.OpenRecordset(strSQL)[B][COLOR="Red"]
With Rst
If Not .EOF Then[/COLOR][/B]
       [COLOR="red"][B] .MoveLast[/B][/COLOR]
        .MoveFirst[COLOR="red"]
        Do Until .EOF[/COLOR]
            strModelYear = !Model_Year
            strModelName = Trim(!Model_Name)
            strProductName = Trim(!ProductName)
            ldSurveyMonth = !Report_Month
            liOwnershipPeriod = !Ownership_Period
            liMonthNo = Set_Month_No(Db, ldSurveyMonth, strModelYear, strModelName, liOwnershipPeriod)
            
            If liMonthNo = 0 Then
                GoTo Process_Complete
            End If
            
            Db.Execute ("INSERT INTO tblPPHUSummary " & _
                        "(Ownership_Period, MYR, Model, Survey_Month, Product_Line, Month_No) " & _
                        "VALUES (" & _
                        liOwnershipPeriod & ", " & strModelYear & ", '" & strModelName & "', '" & ldSurveyMonth & "', " & _
                        "'" & strProductName & "', " & liMonthNo & " " & _
                        ")")

           [B][COLOR="red"] .MoveNext[/COLOR][/B]
        Loop
    End If[B][COLOR="red"]
End With

Set Rst=Nothing[/COLOR][/B]

...

NOTE: it is important to clear all recordsets after use as I've done above

HOWEVER:
Why use a recordset at all?
A simple APPEND query should be able to do this and, if so, will do it far faster
 
Last edited:

Alc

Registered User.
Local time
Today, 12:00
Joined
Mar 23, 2007
Messages
2,407
Thanks for the reply.
In order:
I use the same form for the recordset loop in dozens of other places with no issues, which is why it's throwing me that this one keeps failing. I've changed the code as per your suggestion though and now the error 'Item not found in this collection' occurs on line
Code:
strModelYear = Rst!Model_Year

I already the line
Code:
Set Rst = nothing
I just didn't paste it as it occurs after the problem.

I'm using the recordset out of force of habit. A lot of the processes here don't involve data that's laid out in the same structure between source and destination tables and I've found it (until now) easier to use the recordset method, so I can manipulate it as I go. I think I'll try the append and see if that gets me around the errors.

Would still be nice to know what's going on, even if only out of curiosity.:confused:
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 12:00
Joined
Oct 17, 2012
Messages
3,276
I'm going to assume that Db is a DAO.Database variable you declared earlier in the code - let me know if that's not correct. Same with Rst and DAO.Recordset variables.

As to your specific question, in Access, recordsets begin with a Beginning of File flag, and end with and End of File flag. They are pointers that coexist with the first and last record, if there is one. If, however, you cycle through to the last record, .MoveNext, then .MoveNext again, you get the exact error you're referring to. Same if you're in an empty recordset and try to move next. The same thing happens if you try to move the pointer in an empty recordset.

It sounds to me like either you're moving to the end at some point (while generating liMonthNo?) or else the recordset is getting closed at some point.

Also, While VB7 uses GoTo for error handling, there's always a better option than GoTo in the main body. BAD PROGRAMMER! BAD!
 

Alc

Registered User.
Local time
Today, 12:00
Joined
Mar 23, 2007
Messages
2,407
I'm going to assume that Db is a DAO.Database variable you declared earlier in the code - let me know if that's not correct. Same with Rst and DAO.Recordset variables.
Yes, they are.

As to your specific question, in Access, recordsets begin with a Beginning of File flag, and end with and End of File flag. They are pointers that coexist with the first and last record, if there is one. If, however, you cycle through to the last record, .MoveNext, then .MoveNext again, you get the exact error you're referring to. Same if you're in an empty recordset and try to move next. The same thing happens if you try to move the pointer in an empty recordset.
This I've come across before and it was always an empty recordset causing the problem.
It sounds to me like either you're moving to the end at some point (while generating liMonthNo?) or else the recordset is getting closed at some point.
Bingo! the function that sets the month number used a recordset that I'd stupidly called the same thing. Once it has been called Rst is at the last record, hence the error. My god I feel stupid.:banghead:
Also, While VB7 uses GoTo for error handling, there's always a better option than GoTo in the main body. BAD PROGRAMMER! BAD!
I shall now make a cup of tea in a manner that I hope shows myself to be suitably admonished. :eek:

Thank you both.
Ridders - I'm going to take your advice and replace part of the code with an append query.

So glad it's almost the end of the week.
 

isladogs

MVP / VIP
Local time
Today, 16:00
Joined
Jan 14, 2017
Messages
18,186
Sorry. I forgot to remove all references to Rst! from within the With...End With section. I've corrected that post now.

Anyway, you should find using an append SQL statement much better but I meant that to be used instead of the entire recordset code...not just part of it.
 

Users who are viewing this thread

Top Bottom