refresh not working

[Geoff]

This program is only for use as a demonstration when working with
recordsets. The idea is that the record set s processed to determine how
many books out by junior, adult and senior members of a library. The
Form_Open event presents the correct totals.

Private Sub Form_Open(Cancel As Integer)
Dim totj As Integer, tota As Integer, tots As Integer
Call OpenRecSet
Do
If Rec("Memb_Cat") = 1 Then
totj = totj + Val(Rec("Memb_BooksOut"))
ElseIf Rec("Memb_Cat") = 2 Then
Tota = Tota + Val(Rec("Memb_BooksOut"))
Else
tots = tots + Val(Rec("Memb_BooksOut"))
End If
Rec.MoveNext
Loop Until Rec.EOF

Me.Txt_JBooks = totj
Me.Txt_ABooks = tota
Me.Txt_SBooks = tots

Rec.Close

However when a new rec is entered and the Memb_BooksOut_AfterUpdate() event
triggered when the BooksOut field is updated, the totals on the form are not
immediately refreshed. Why?

Private Sub Memb_BooksOut_AfterUpdate()

.. same code as above, but ending in .

Me.Refresh

End Sub

Any help given will be genuinely appreciated

[Wayne Gillespie]

On Fri, 27 Jan 2006 08:32:22 -0000, "Geoff" <gf****@freenetname.co.uk> wrote:

This program is only for use as a demonstration when working with
recordsets. The idea is that the record set s processed to determine how
many books out by junior, adult and senior members of a library. The
Form_Open event presents the correct totals.

Private Sub Form_Open(Cancel As Integer)
Dim totj As Integer, tota As Integer, tots As Integer
Call OpenRecSet
Do
If Rec("Memb_Cat") = 1 Then
totj = totj + Val(Rec("Memb_BooksOut"))
ElseIf Rec("Memb_Cat") = 2 Then
Tota = Tota + Val(Rec("Memb_BooksOut"))
Else
tots = tots + Val(Rec("Memb_BooksOut"))
End If
Rec.MoveNext
Loop Until Rec.EOF

Me.Txt_JBooks = totj
Me.Txt_ABooks = tota
Me.Txt_SBooks = tots

Rec.Close

However when a new rec is entered and the Memb_BooksOut_AfterUpdate() event
triggered when the BooksOut field is updated, the totals on the form are not
immediately refreshed. Why?

Private Sub Memb_BooksOut_AfterUpdate()

.. same code as above, but ending in .

Me.Refresh

End Sub

Any help given will be genuinely appreciated

Me.Refresh will get changes to the records contained in the current recordset but will not get records added after the
recordset was created. MeRequery will recreate the recordset and grab any new records.
Wayne Gillespie
Gosford NSW Australia

[Allen Browne]

Geoff, we cannot see what is in OpenRecSet, but presumably it opens a
Recordset type variable named Rec (which is presumably declared somewhere
else), and this recordset is reading the values from the table.

If so, the AfterUpdate even of BooksOut is too early. The change has not
been saved to the table yet. Try the AfterUpdate event of the Form instead.

Since you are demonstrating recordsets, you might like to OpenRecordset()
based on a query string such as this:
strSql = "SELECT Memb_Cat, Count([ID]) AS CountOfID FROM Memb_BooksOut
GROUP BY Memb_Cat;"
Set Rec = dbEngine(0)(0).OpenRecordset(strSql)

If you were not practicing recordsets, you would probably just put something
like this into the Control Source of the text box:
=DCount("*", "Memb_BooksOut", "Memb_Cat = 1")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Geoff" <gf****@freenetname.co.uk> wrote in message
news:I5********************@brightview.com...

This program is only for use as a demonstration when working with
recordsets. The idea is that the record set s processed to determine how
many books out by junior, adult and senior members of a library. The
Form_Open event presents the correct totals.

Private Sub Form_Open(Cancel As Integer)
Dim totj As Integer, tota As Integer, tots As Integer
Call OpenRecSet
Do
If Rec("Memb_Cat") = 1 Then
totj = totj + Val(Rec("Memb_BooksOut"))
ElseIf Rec("Memb_Cat") = 2 Then
Tota = Tota + Val(Rec("Memb_BooksOut"))
Else
tots = tots + Val(Rec("Memb_BooksOut"))
End If
Rec.MoveNext
Loop Until Rec.EOF

Me.Txt_JBooks = totj
Me.Txt_ABooks = tota
Me.Txt_SBooks = tots

Rec.Close

However when a new rec is entered and the Memb_BooksOut_AfterUpdate()
event
triggered when the BooksOut field is updated, the totals on the form are
not immediately refreshed. Why?

Private Sub Memb_BooksOut_AfterUpdate()

.. same code as above, but ending in .

Me.Refresh

End Sub

Any help given will be genuinely appreciated