Working with recordset - is there a cache that needs to be cleared?

[csh2013]

I have a Public Function that is being auto executed when my database opens. It is designed to change the value of a record from "Trailing" to "Critical" when the record is older than 90 days. Everything works as expected initially by changing the status and adding some text to a comment field for tracking purposes. However, I manually flipped the status of those records back to "Trailing" for continued testing purposes and they are no longer being included in the code execution. Do recordsets have a cache that is identifying these records as already having been updated and is, therefore, excluding them for all future executions of this module? Code is below. I added a counter just for the purposes of testing and it's counting zero records, even though there are several records that meet the necessary criteria.

Public Function Trailing()
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim ND As Date
Dim CT As Integer

ND = Now() - 90
strSQL = "INSERT INTO ModuleRunDate ([Run_Date]) VALUES (Now())"

If DMax("Run_Date", "ModuleRunDate") < Date Then

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Exceptions")
CT = 0

rs.MoveLast
rs.MoveFirst

For i = 0 To rs.RecordCount - 1
If rs.Fields("Status") = "Trailing" And _
IsNull(rs.Fields("Date Completed")) And _
rs.Fields("Date of Exception") < ND Then

CT = CT + 1
rs.Edit
rs.Fields("Status") = "Critical"
rs.Fields("Comments") = rs.Fields("Comments").Value & "; Trailing to Critical " & Format(Now(), "mm/dd/yy")
rs.Update

End If
rs.MoveNext
Next i
MsgBox CT, vbOKOnly
rs.Close
Set rs = Nothing
db.Close

DoCmd.RunSQL strSQL

Else: Exit Function

End If

End Function

 

[pbaldy]

Did you change the date fields to match the test? FYI, more efficient would be to include criteria in the recordset SQL to only retrieve those records rather than include the whole table and then loop looking for the matches.

 

[csh2013]

The date fields are really old. I'm using test records that have a Date of Exception in the year 2010, which are easily older than 90 days. And since they are marked as Traililng and the Date Completed field is null, they should be included.

So you're saying that rather than having my three pieces of criteria during the loop, I should move that up to the db.openrecordset(SELECT.....) piece? I don't use loops very often, so I'm definitely a novice with them and am open to suggestion. Do you think this might solve my update issue as well?

 

[pbaldy]

I don't think it would solve this problem, no. It would just be more efficient. Can you post a sample db here by chance? To answer your original question, you shouldn't have to do anything special, your recordset should work on the records where you've changed the status back (it would have to exactly match your test though; "Trailing").

 

[csh2013]

Okay. I did some further investigation and found that my code was NOT changing all of the statuses as I originally thought. It just happened to be swapping the few I was using as an example. And, at your suggestion, pbaldy, I figured I would write the SELECT statement at the beginning rather than using the whole dataset. Since I'm not as accomplished at writing SQL, I was testing each individual piece as I went and, by the time I got to the end, the new code works. And it works even when I mess with the test data, which is where the first code failed. So, I have no idea where the breakdown in the original code was, but the code below works! Thank you for the suggestion! Even though it may not have resolved the original problem, the suggestion alone got me to the correct result!

Public Function Trailing()
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim ND As Date
Dim rst As String

ND = Now() - 90

strSQL = "INSERT INTO ModuleRunDate ([Run_Date]) VALUES (Now())"

rst = "SELECT * FROM Exceptions " & _
"WHERE [Date Completed] Is Null" & _
" And Status = '" & "Trailing" & "'" & _
" And [Date of Exception]<#" & Format(ND, "mm/dd/yy") & "#"

If DMax("Run_Date", "ModuleRunDate") < Date Then

Set db = CurrentDb
Set rs = db.OpenRecordset(rst)

rs.MoveLast
rs.MoveFirst

For i = 0 To rs.RecordCount - 1

rs.Edit
rs.Fields("Status") = "Critical"
rs.Fields("Comments") = rs.Fields("Comments").Value & "; Trailing to Critical " & Format(Now(), "mm/dd/yy")
rs.Update

rs.MoveNext
Next i
rs.Close
Set rs = Nothing
db.Close

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

Else: Exit Function

End If

End Function

 

[pbaldy]

Happy to help! One thing to watch for is that the recordset will be empty if no records meet the criteria, and the MoveLast will throw an error. I would do it like this (relevant section only, I commented out lines I would delete):

Set rs = db.OpenRecordset(rst)

'rs.MoveLast
'rs.MoveFirst

'For i = 0 To rs.RecordCount - 1
Do While Not rs.EOF

  rs.Edit
  rs.Fields("Status") = "Critical"
  rs.Fields("Comments") = rs.Fields("Comments").Value & "; Trailing to Critical " & Format(Now(), "mm/dd/yy")
  rs.Update

  rs.MoveNext
'Next i
Loop

The loop should run on all records returned, but will not error if there are none.

 

[pbaldy]

Oh, and FYI no need for the concatenation here. It can be:

" And Status = 'Trailing' " & _

It will work either way. To me it's easier to read without the concatenation, and it's microscopically more efficient.

 

[smig]

Oh, and FYI no need for the concatenation here. It can be:

" And Status = 'Trailing' " & _

It will work either way. To me it's easier to read without the concatenation, and it's microscopically more efficient.

1 +

Use what you used when you want to put a variable into the query.