auto-refresh HOW?

[Ogg]

Is there a way to automatically refresh a form? I have a bunch of tables
linked to specialized forms. When one user deletes a record, another user
will eventually see #DELETED in the record. Generally, that's good to know
when data has been modified. But lately, it has become a nuisance to have
to manually re-sort data (we only use Form View) inorder to get an uptodate
screen. This is for Access 2000, btw.

 

[Guest]

What setting do you have under Tools > Options on the Advanced tab for
"Refresh Interval (sec):" setting? You can click into this setting and press
the F1 key to learn more.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Refreshing a form only reflects changes to the data in existing records. To
reflect deletions you need to requery the form. Another effect of this is
that, unlike refreshing, requerying moves the record pointer to the beginning
of the underlying recordset.

To requery the form without user intervention you could call the Requery
method in the form's Timer event procedure, having set the TimerInterval
property to a suitable value, but users would undoubtedly find it even more
annoying than at present to have the form suddenly move to its first record
without warning periodically. This could be overcome by grabbing the value
of the underlying record's key to a variable and then navigating back to the
current record by synchronizing the form's bookmark with that of its
recordset's clone in the usual way. However, requerying the form also saves
the current record if its currently dirty, and this might also raise an error
if the record can't be saved for some reason, so you'd probably want to check
that the form's Dirty property is False before calling the Requery method.
Let's say the key of the underlying recordset is called MyID and is a long
integer number data type (which includes autonumbers), then the code would be
along these lines:

Dim rst As Object
Dim lngID Long

If Not Me.Dirty Then
lngID = MyID
Me.Requery
Set rst = Me.Recordset.Clone
rst.FindFirst "MyID = " & lngID
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End If

Ken Sheridan
Stafford, England