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
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
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?
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
2. If, just before 'Rst.MoveFirst' line I add
Code:
Rst.Movelast
Msgbox Rst.recordcount
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?