L
Laurel
I have an application that's been running with few problesm for a couple of
years. Twice now, a table has gotten subtly corrupted so that a select on a
query doesn't work properly inside of code, but it does work in the query
builder. By not working "properly," I mean that it retrieves only one
record, the first record, when it should be retrieving hundreds. I'm sure of
the query, because I cut it out of the code in the debugger and paste it
into the builder and execute it. One place it retrieves 1 record and the
other place the expected hundreds.
I'm sure the problem is the table, because both times I was able to make the
problem go away be reverting to an older copy of the table and re-entering
data by hand. The first time it happened, I don't know exactly what piece
of data caused the change. But the second time it happened I determined
that the problem was caused when 15 records were deleted from the table.
That is ANY fifteen records. The problem first happened when I deleted 17
records with a DELETE query. Then I deleted several at a time in the table
builder. And then I deleted one at a time in the table builder, running the
application after each delete. Three times the problem cropped up after I
deleted the 15th row. In each case I did NOT delete the row that had caused
the problem previously.
Truly weird!
Any advice about how to clean up the table so this never happens again??????
Here are various details.
0. The table that causes the problem is tblStudents.
1. The Select statement that works in the query builder but not in the code.
select * from qryClassSummaryDetailBaseDebug where class_code = 'ml2' and
student_id <> -1
2. The definition of the query itself.
SELECT [first_name] & " " & [last_name] AS Full_Name, tblScores.Score_Date
AS Week_of, tblScores.Score_Date, tblScores.Safety, tblScores.Respect,
tblScores.Responsibility, tblScores.Bonus, tblScores.Safety AS TotalScores,
tblStudents.Student_ID, tblperiods.Period_Code, tblperiods.Sort_Order,
tblStudents.Class_Code
FROM tblStudents INNER JOIN (tblperiods INNER JOIN tblScores ON
tblperiods.Period_Code = tblScores.Period_Code) ON tblStudents.Student_ID =
tblScores.Student_ID
ORDER BY tblStudents.Student_ID;
3. The code that fails. I know the query is returning only one row both
because of the value of RecordCount and because of the display in the
subsequent report.
'3/26 DEBUG
'ls_temp = "Select * from qryClassSummaryDetail " & as_where
'Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)
Set rstClassDetail = CurrentDb.OpenRecordset("select * from
qryClassSummaryDetailBaseDebug where class_code = 'ml2' and student_id
<> -1")
'END 3/26 DEBU
If rstClassDetail.RecordCount = 0 Then
' liReturn = MsgBox("There are no scores for this class and time
period")
GoTo Exit_CalcSummaryInfo
End If
years. Twice now, a table has gotten subtly corrupted so that a select on a
query doesn't work properly inside of code, but it does work in the query
builder. By not working "properly," I mean that it retrieves only one
record, the first record, when it should be retrieving hundreds. I'm sure of
the query, because I cut it out of the code in the debugger and paste it
into the builder and execute it. One place it retrieves 1 record and the
other place the expected hundreds.
I'm sure the problem is the table, because both times I was able to make the
problem go away be reverting to an older copy of the table and re-entering
data by hand. The first time it happened, I don't know exactly what piece
of data caused the change. But the second time it happened I determined
that the problem was caused when 15 records were deleted from the table.
That is ANY fifteen records. The problem first happened when I deleted 17
records with a DELETE query. Then I deleted several at a time in the table
builder. And then I deleted one at a time in the table builder, running the
application after each delete. Three times the problem cropped up after I
deleted the 15th row. In each case I did NOT delete the row that had caused
the problem previously.
Truly weird!
Any advice about how to clean up the table so this never happens again??????
Here are various details.
0. The table that causes the problem is tblStudents.
1. The Select statement that works in the query builder but not in the code.
select * from qryClassSummaryDetailBaseDebug where class_code = 'ml2' and
student_id <> -1
2. The definition of the query itself.
SELECT [first_name] & " " & [last_name] AS Full_Name, tblScores.Score_Date
AS Week_of, tblScores.Score_Date, tblScores.Safety, tblScores.Respect,
tblScores.Responsibility, tblScores.Bonus, tblScores.Safety AS TotalScores,
tblStudents.Student_ID, tblperiods.Period_Code, tblperiods.Sort_Order,
tblStudents.Class_Code
FROM tblStudents INNER JOIN (tblperiods INNER JOIN tblScores ON
tblperiods.Period_Code = tblScores.Period_Code) ON tblStudents.Student_ID =
tblScores.Student_ID
ORDER BY tblStudents.Student_ID;
3. The code that fails. I know the query is returning only one row both
because of the value of RecordCount and because of the display in the
subsequent report.
'3/26 DEBUG
'ls_temp = "Select * from qryClassSummaryDetail " & as_where
'Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)
Set rstClassDetail = CurrentDb.OpenRecordset("select * from
qryClassSummaryDetailBaseDebug where class_code = 'ml2' and student_id
<> -1")
'END 3/26 DEBU
If rstClassDetail.RecordCount = 0 Then
' liReturn = MsgBox("There are no scores for this class and time
period")
GoTo Exit_CalcSummaryInfo
End If