Datasheet view - automatically scroll Center of Window

[M3CAS]

I am trying to have a selected record in datasheet view to show up in the middle of the window--not the bottom of the window.

Details: I have a sub form in datasheet view. On the main form there is a button on the main form to add a record just below the selected record. The number of records is normally large. When the record is inserted, I have to re-sort and refresh in order to have the new record show up. There is then a procedure to find the new record in the list and, of course, it ends up at the bottom of the window. All I want to do is have this record show up in the middle of the screen. Any ideas would be gratefully received!

I've looked all over for an answer for this and have been unsuccessful.

 

[MajP]

I am not sure of the procedure you are using to select the record, but if I do something like this it usually ends up at the top not the bottom.

Dim ID As Long
Dim rs As DAO.Recordset
Set rs = Me.Recordset
ID = some code to get your ID
rs.FindFirst "fieldName = " & ID

 

[M3CAS]

Thanks MajP - Yes, that is how I'm locating the record. But it doesn't matter if it is on the top or on the bottom of the window. The goal is to have it so there are several records before and after the found record. This might be easier to do with a listbox but due to the nature and complexity of the "Spreadsheet", I have to use a subform in datasheet view.

 

[MajP]

I do not think Access has a native means to code the positioning of a record within a window or a way to control the scroll bar position. I would think the best you could do is decide how many records you want before your record. Locate your record's absolute position, and select the record X positions prior. This would put X records above your record, so your record would always be the X position.

Something like this
[/code]
Public Sub posX(ID As Long, Xbefore As Integer)
Dim rs As DAO.Recordset
Set rs = Forms("subFormOrders").Recordset
rs.FindFirst "orderID = " & ID
If rs.AbsolutePosition > Xbefore Then
MsgBox rs.AbsolutePosition - Xbefore
rs.AbsolutePosition = (rs.AbsolutePosition - Xbefore)
Else
rs.MoveFirst
End If
rs.FindFirst "orderID = " & ID
End Sub

[/code]
and call it

call posX(10249,8)

I did not think this would work, but it does. If I put X records above it and then find it while it is in the view nothing scrolls and and it stays in the X position.

 

[MajP]

BTW I only tested this in a continous form view not a datasheet view.

 

[M3CAS]

This is the best idea so far, I'm don't have a chance to try it before tonight but I'll let you know how it goes. Thanks!