Requery/refresh

[Naz]

Hi

I have a form with 2 subforms on it that have queries as their source.
The main form has general employee details
The subform subfrm_details has details of absence types (sickness, maternity)
The subform frm_SubSicknessSum simply totals up the total number of absence
by type.


I have some code (see below) when someone enters the days absent, it
requery's the underlying queries and updates the summary, but the problem is
when i enter the days and press return or move to the next field, it
requery's OK, updates OK, but then moves the cursor to the record right at
the top (the first record), which gets worse if you try to enter lots of
absences as it goes to the top, and then you have to scoll back down to enter
a new record.

Private Sub NoOfDays_LostFocus()

Forms!frm_Employees!.frm_SubSicknessSum.Form.Requery
Forms!frm_Employees!.frm_SubSicknessSum.Form.Refresh

End Sub

Is there anyway the query can requery/refresh and not go back to the first
record?

All help is greatly appreciated.

Jim

 

[Klatuu]

A Requery will always go back to the first record in the recordset, but there
is a way to make it stay on the current record. First, you don't need both a
refresh and a requery. You should use the requery. Also, if the control
NoOfDays is on the SubSicknessSum subform, you don't need to qualify it like
that. It is okay, and technically correct, but not necessary. And, the Lost
Focus event is not the right place. It will fire every time you move away
from the control. So, if you don't put anything in, it will requery
unnecessarily. The correct event is the After Update event. It only fires
after data has been changed an you move away from the control. Don't confuse
it with the Change event, it fires for every keystroke.

I don't know the fields in your recordset nor the control name, so I can
only give an example. What you do is save the value in a control that is
unique (usually the primary key) to a variable. Then after you requery, you
use that variable to move back to the record you were on before the requery:

Private Sub NoOfDays_AfterUpdate()
Dim lngKeyVal As Long

'Save the value for the current record
lngKeyVal = Me.txtKeyFieldControl

'Requery the form
Me.Requery

'Move back to the original record
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

 

[Naz]

Hi Dave

Thanks for this - it worked and taught me a few things i didn't know.


Jim

A Requery will always go back to the first record in the recordset, but there
is a way to make it stay on the current record. First, you don't need both a
refresh and a requery. You should use the requery. Also, if the control
NoOfDays is on the SubSicknessSum subform, you don't need to qualify it like
that. It is okay, and technically correct, but not necessary. And, the Lost
Focus event is not the right place. It will fire every time you move away
from the control. So, if you don't put anything in, it will requery
unnecessarily. The correct event is the After Update event. It only fires
after data has been changed an you move away from the control. Don't confuse
it with the Change event, it fires for every keystroke.

I don't know the fields in your recordset nor the control name, so I can
only give an example. What you do is save the value in a control that is
unique (usually the primary key) to a variable. Then after you requery, you
use that variable to move back to the record you were on before the requery:

Private Sub NoOfDays_AfterUpdate()
Dim lngKeyVal As Long

'Save the value for the current record
lngKeyVal = Me.txtKeyFieldControl

'Requery the form
Me.Requery

'Move back to the original record
With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

Hi

I have a form with 2 subforms on it that have queries as their source.
The main form has general employee details
The subform subfrm_details has details of absence types (sickness, maternity)
The subform frm_SubSicknessSum simply totals up the total number of absence
by type.


I have some code (see below) when someone enters the days absent, it
requery's the underlying queries and updates the summary, but the problem is
when i enter the days and press return or move to the next field, it
requery's OK, updates OK, but then moves the cursor to the record right at
the top (the first record), which gets worse if you try to enter lots of
absences as it goes to the top, and then you have to scoll back down to enter
a new record.

Private Sub NoOfDays_LostFocus()

Forms!frm_Employees!.frm_SubSicknessSum.Form.Requery
Forms!frm_Employees!.frm_SubSicknessSum.Form.Refresh

End Sub

Is there anyway the query can requery/refresh and not go back to the first
record?

All help is greatly appreciated.

Jim