Automatically refresh a form after a certain time interval contino

[Richard Horne]

Hi guys, as the title suggests I need to be able to refresh a form (or in
this case 10 sub forms) automatically after a certain time period as well as
constantly throughout the day, i.e. every 1 minute. I also need the form to
remember which field the user was typing in before the refresh occurs (if
they were) and return the cursor to the exact same place - uninterrupted.

After posting in another forum I was told to try the following code, but
this doesn't seem to work 100% i often get an error saying Field not found
[Tel ID] - Tel ID is the autonumber used to return to that current record
after the requery - I have no idea why this error occurs as all the sub form
have a field called Tel ID.

Private Sub Form_Timer()

Dim rst As DAO.Recordset
Dim lngID As Long
Dim CurCtl As String

If IsNull(Me.CustomerID) = True Then

Exit Sub

Else

CurCtl = Me.ActiveControl.Name
lngID = CustomerID

Me.Requery

Set rst = Me.RecordsetClone

rst.FindFirst "[CustomerID] = " & lngID

If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark


DoCmd.GoToControl CurCtl
Me.ActiveControl.SetFocus


End If
rst.Close
Set rst = Nothing
End If
End Sub

To explain this in the context of which it will be used, the system is a
telephone call log system split into a front and a back end, I need the forms
to refresh every minute to load in any new telephone calls for each persons
inbox, but as they may very often be typing in data at the moment of the
refresh I need their current position with a record to be remembered.

Please help!

 

[Wayne Morgan]

First, if there are multiple subforms on the form, instead of running the
timer on each subform, just run the timer on the main form and requery each
of the subforms from there. As far as remembering what the user had typed so
far, get the Text property of the active control and save it to a variable.
Restore this property after the requery and use the SelStart property of the
control to place the cursor at the end of the typing.

Example (from the parent form):
'Untested
If Me.ActiveControl.ControlType = acSubform Then
strSaveSubform = Me.ActiveControl.Name
strSaveControl =
Me.Controls(Me.ActiveControl.Name).Form.ActiveControl.Name
strSaveTyping =
Me.Controls(Me.ActiveControl.Name).Form.ActiveControl.Text
Else
strSaveControl = Me.ActiveControl.Name
strSaveTyping = Me.ActiveControl.Text
End If
'Requery your subforms
If strSaveSubform <> "" Then
Me.Controls(strSaveSubform).SetFocus
Me.Controls(strSaveControl).SetFocus
Me.Controls(strSaveSubform).Form.ActiveControl.Text = strSaveTyping
Me.Controls(strSaveSubform).Form.ActiveControl.SelStart =
Len(strSaveTyping)
Else
Me.Controls(strSaveControl.SetFocus
Me.ActiveControl.Text = strSaveText
Me.ActiveControlSelStart = Len(strSaveText)
End If

This is untested. If the typing is currently happening in the parent form,
this shouldn't be needed. Also, be aware that any typing already being done
may be saved to the record when you do the requery. If there is a check on
the value when you try to save the record and the typing is incomplete, you
may get an error when you try to requery because the record can't be saved
in that state. One way around this would be to save the value of every
control on the form being typed in, undo all changes, then restore all
changes after the query. Doing this would be similar to the above example,
just a lot more work. You may also find that this will happen so quickly
that the user won't realize it in time and will still be typing when this
happens. These additional key stroke may go into the buffer and be applied
to the control that momentarily receives focus after the requery. In other
words, I don't consider this to be a good idea. I would recommend that you
check to see if the subform is Dirty and if it is, wait until the record has
been saved or prompt the user for permission to proceed (although being
prompted once a minute will drive the user nuts). They probably don't need
to see the changes until they are done typing anyway.


--
Wayne Morgan
MS Access MVP

Hi guys, as the title suggests I need to be able to refresh a form (or in
this case 10 sub forms) automatically after a certain time period as well
as
constantly throughout the day, i.e. every 1 minute. I also need the form
to
remember which field the user was typing in before the refresh occurs (if
they were) and return the cursor to the exact same place - uninterrupted.

After posting in another forum I was told to try the following code, but
this doesn't seem to work 100% i often get an error saying Field not found
[Tel ID] - Tel ID is the autonumber used to return to that current record
after the requery - I have no idea why this error occurs as all the sub
form
have a field called Tel ID.

Private Sub Form_Timer()

Dim rst As DAO.Recordset
Dim lngID As Long
Dim CurCtl As String

If IsNull(Me.CustomerID) = True Then

Exit Sub

Else

CurCtl = Me.ActiveControl.Name
lngID = CustomerID

Me.Requery

Set rst = Me.RecordsetClone

rst.FindFirst "[CustomerID] = " & lngID

If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark


DoCmd.GoToControl CurCtl
Me.ActiveControl.SetFocus


End If
rst.Close
Set rst = Nothing
End If
End Sub

To explain this in the context of which it will be used, the system is a
telephone call log system split into a front and a back end, I need the
forms
to refresh every minute to load in any new telephone calls for each
persons
inbox, but as they may very often be typing in data at the moment of the
refresh I need their current position with a record to be remembered.

Please help!