Form data not refreshing after editing

[Cathy]

I'm using Access 2007. My form has a record source of "tblTEMP_Changes". My
close button on the form needs to do some data validation and clean up any
inconsistencies the user may have left behind. One element of the code is:

Dim wrkCurrent As DAO.Workspace
Dim rrdb As DAO.Database
Dim rs As DAO.Recordset

Set wrkCurrent = DBEngine.Workspaces(0)
Set rrdb = CurrentDb
Set rs = rrdb.OpenRecordset("tblTEMP_Changes")

wrkCurrent.BeginTrans

If rs.EOF Then Exit Sub

With rs

rs.MoveFirst
Do While Not rs.EOF
If rs!Period = Forms!frmPropose.Period And rs![Manager ID] =
Forms!frmPropose.[Manager ID] And rs![Is Grouped].Value = 0 Then
If rs![Reported Risk].Value = "High" Then
rs.Edit
rs!PrepSch.Value = "High"
rs!RevSch.Value = "High"
rs![Risk Change].Value = True
rs.Update
End If
End If
rs.MoveNext
Loop

wrkCurrent.CommitTrans

rs.Close
rrdb.Close

Set rs = Nothing
Set rrdb = Nothing

End With

The code appears to update the table perfectly. My problem is that the next
time I open the form, the old data still appears... without the changes.
What could I possibly be doing wrong? If the form is a bound form, how can
it retain & display information that is no longer on the table? Even if I
close the database and re-open it, the form does not display the correct
information. I have to be missing something pretty basic...

Thank you for your help!

 

[ruralguy via AccessMonster.com]

Comment out the With rs and End With lines and see how it works. You are not
using them anyway.

I'm using Access 2007. My form has a record source of "tblTEMP_Changes". My
close button on the form needs to do some data validation and clean up any
inconsistencies the user may have left behind. One element of the code is:

Dim wrkCurrent As DAO.Workspace
Dim rrdb As DAO.Database
Dim rs As DAO.Recordset

Set wrkCurrent = DBEngine.Workspaces(0)
Set rrdb = CurrentDb
Set rs = rrdb.OpenRecordset("tblTEMP_Changes")

wrkCurrent.BeginTrans

If rs.EOF Then Exit Sub

With rs

rs.MoveFirst
Do While Not rs.EOF
If rs!Period = Forms!frmPropose.Period And rs![Manager ID] =
Forms!frmPropose.[Manager ID] And rs![Is Grouped].Value = 0 Then
If rs![Reported Risk].Value = "High" Then
rs.Edit
rs!PrepSch.Value = "High"
rs!RevSch.Value = "High"
rs![Risk Change].Value = True
rs.Update
End If
End If
rs.MoveNext
Loop

wrkCurrent.CommitTrans

rs.Close
rrdb.Close

Set rs = Nothing
Set rrdb = Nothing

End With

The code appears to update the table perfectly. My problem is that the next
time I open the form, the old data still appears... without the changes.
What could I possibly be doing wrong? If the form is a bound form, how can
it retain & display information that is no longer on the table? Even if I
close the database and re-open it, the form does not display the correct
information. I have to be missing something pretty basic...

Thank you for your help!