I couldn't get Wayne G Dunn's solution working, but I came up with this alternative. It's not wonderful, but it worked for me.
The basic idea is that each record in the continuous form has a position (ie top record showing on the screen is in position 1, regardless which actual record it is). You have a table that relates those positions, to the Form.currentSectionTop property of each record in the form, so you can figure out what position the current record is in. Then it's relatively straightforward to return to that position after the requery.
Create the table with the positions - this needs to run on startup or somewhere - might need to be more frequent if the user can resize or anything might change the number of records that could be shown in the continuous form.
Public Sub Setup_Positions()
Dim sql As String
Dim Position As Long
Dim currentSectionTop As Long
Dim lastSectionTop As Long
sql = "DELETE FROM tblRecordPosition"
currentdb.execute sql
DoCmd.GoToRecord , , acFirst
Position = 1
Call Set_NoUpdate
With Forms("frmMain").Controls("frmContinuousSubForm").Form
currentSectionTop = .currentSectionTop
Do While currentSectionTop <> lastSectionTop
'record previous sectiontop
lastSectionTop = currentSectionTop
'write it into the table
sql = "INSERT INTO tblRecordPosition (Position, CurrentSectionTop) " & _
"SELECT " & Position & ", " & _
currentSectionTop
CurrentDb.Execute sql
'update to next position and record the 'last' one, move to next record. When we've run out of visible ones, the last and current will be the same.
Position = Position + 1
DoCmd.GoToRecord , , acNext
'get new current sectiontop
currentSectionTop = .currentSectionTop
Loop
End With
Call Set_NoUpdateOff
End Sub
Set up global variables and a couple of functions to maintain them. The 'NoUpdateRequired' variable is optional - I use it to prevent unnecessary stuff running all the time.
Public NoUpdateRequired As Boolean
Public Position As Long
Public Sub Set_NoUpdate()
NoUpdateRequired = True
End Sub
Public Sub Set_NoUpdateOff()
NoUpdateRequired = False
End Sub
Create this function to convert between the property you can measure, and the actual position:
Public Function Get_Position(Optional InputCurrentSectionTop As Long) As Long
Dim currentSectionTop As Long
Dim Position As Long
If InputCurrentSectionTop > 0 Then
currentSectionTop = InputCurrentSectionTop
Else
currentSectionTop = Forms("frmMain").Controls("frmContinuousSubForm").Form.currentSectionTop
End If
Position = Nz(ELookup("Position", "tblRecordPosition", "CurrentSectionTop = " & currentSectionTop), 0)
Get_Position = Position
End Function
In the current event of the continuous form, you need this:
Private Sub Form_Current()
If NoUpdateRequired = False Then
Position = Get_Position
End If
End Sub
And finally, in the bit where you want your refresh to happen, you need this:
Public Sub Refresh_ContinuousSubForm()
'All this problem goes away if you can use Refresh instead of Requery, but if you have a few things editting the underlying table, you must use requery to avoid 'another user has changed the data' errors.
'However, this then causes the form to jump
'back to the first record instead of keeping the current record selected. To get around this, the following has been employed:
'the .seltop property allows you to select the top selected record (in most cases, only one record is selected). This is recorded before the refresh, and
'the form set back to that after the refresh. However, this puts the selected record at the top of the screen - confusing when you're working down a list.
'The .currentSectionTop property measures the number of twips from the selected record to the top of the screen - and correlates to which position in the list
'of 25 records in the bottom pane. tblRecordPosition converts between the twips to the actual position (recorded when the database is opened).
'The key to all this is that going back to the right record using .seltop only puts the record at the top of the screen IF the record wasn't already visible on the screen.
'But GoToRecord, if used when you're already at the top of the screen, will push the records down the screen as you move backward (upward) through them.
'So we go to the right record, and it will probably be at the top of the screen because of the requery. Then we push them down the screen back to the original position
'using GoToRecord, but now we're on the wrong record. Then we return to the right record using .seltop, and because it's already on the screen, it won't move position.
Dim startSeltop As Long
Dim newSectionTop As Long
Dim newPosition As Long
Dim startPosition As Long
Dim recordsToMove As Long
'Also global variable Position (long) which is recorded in the form's current event
Call Set_NoUpdate
startPosition = Position
With Forms("frmMain").Controls("frmContinuousSubForm").Form
.Painting = False 'stops the screen flickering between
startSeltop = .SelTop 'records which record we're on. Position represents where that was showing on the screen.
.Requery 'does the requery
.SelTop = startSeltop 'sets us back to the correct record
newSectionTop = .currentSectionTop 'measures in twips which position it's in (usually 1)
newPosition = Get_Position(newSectionTop) 'converts that to the position
recordsToMove = startPosition - newPosition 'calculates how many records to move - moving records using GoToRecord moves the position as well
If recordsToMove > 0 Then
DoCmd.GoToRecord , , acPrevious, recordsToMove 'moves back enough records to push our record to the right place on the screen
End If
.SelTop = startSeltop 'now sets back to the correct record
.Painting = True 'turns the screen painting back on
End With
Call Set_NoUpdateOff
End Sub
