Preventing requeries subform "jumping"

[Sheldon Sies]

I have a subform that requeries on a 30 second basis.
After each requery the subform "jumps" to the top record.
I would like to have the subform remain or return to the
same view after requery, the caveat is that the user might
not neccessarily select a record, just scroll down in the
subform. I have found a thread containing reference to
code written by Stephan Leban that is supposed to remedy
this, so any assistance/direction to finding this code
would be a greatly appreciated.

Thanks in advance,

Sheldon

 

[Al Camp]

Sheldon,
I've been using Stephen Leban's code, from his excellent web site.
http://lebans.com/setgetsb.htm
His code allows you to Requery/Refresh/Recalc a Subform without the
subform records "jumping."

 

[fly girl]

This is exactly what I needed to do today, also.

However, when I downloaded the sample app from Leban's
site I was unable to open it. I think it has to do with
the fact that I have Visual Source Safe on my machine.
When I try to open the .mdb it asks me to log in. Won't
accept a null login or password and tells me that I don't
have permission to open this database. Note: shift key
doesn't solve the issue either.

Could either of you post the code elsewhere?

Thanks!

 

[Guest]

Thanks for your quick response Al, works great. Highly
recommend this code for any datasheet subforms with
dynamically changing data.

 

[Al Camp]

Fly Girl,
I had to remove the rem statements. Please watch out for lines that may run
"long" (and wrap)... open your email window to maximum when viewing.

Place this code in your form module.......

* In the Declaration section *
Option Compare Database
Option Explicit
Public SR As clsSetRow

*Place this in your Form module OnCurrent *
Private Sub Form_Current()
If Not SR Is Nothing Then
SR.SelTop = Me.SelTop
SR.CurrentSectionTop = Me.CurrentSectionTop
End If

* The Sub itself, placed in your Form module *
Private Sub RequerySubform()
On Error GoTo Err_cmdReturn_Click
Dim OrigSelTop As Long, RowsFromTop As Long, OrigCurrentSectionTop As
Long
OrigSelTop = SR.SelTop
OrigCurrentSectionTop = SR.CurrentSectionTop
Me.Painting = False
Me.Requery
If Me.Section(acHeader).Visible = True Then
RowsFromTop = (OrigCurrentSectionTop - Me.Section(acHeader).Height) /
Me.Section(acDetail).Height
Else
RowsFromTop = OrigCurrentSectionTop / Me.Section(acDetail).Height
End If
Me.SelTop = Me.RecordsetClone.RecordCount
Me.SelTop = OrigSelTop - RowsFromTop
DoEvents
Me.Painting = True
Me.RecordsetClone.AbsolutePosition = Me.CurrentRecord + RowsFromTop - 1
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit_cmdReturn_Click:
Exit Sub
Err_cmdReturn_Click:
MsgBox Err.Description
Resume Exit_cmdReturn_Click
End Sub

* Place this code in a Module and call that Module "clsSetRow" *
Option Compare Database
Option Explicit
Private mSelTop As Long
Private mCurrentSectionTop As Long
Public Property Get SelTop() As Long
SelTop = mSelTop
End Property
Public Property Let SelTop(X As Long)
mSelTop = X
End Property
Public Property Get CurrentSectionTop() As Long
CurrentSectionTop = mCurrentSectionTop
End Property
Public Property Let CurrentSectionTop(X As Long)
mCurrentSectionTop = X
End Property


To the best of my knowledge, that's it...
--
HTH...
Al Campagna
Candia Computer Consulting
Candia, NH

 

[Al Camp]

Fly Girl,
Sorry, I missed one section...
* Please add this to your Form module OnLoad event*
Public Sub Form_Load()
Set SR = New clsSetRow
End Sub

 

[Fly Girl]

Al,

Thanks so much for your help. I'll let you know if I get
this working but it should save me a huge amount of time.

If you're ever in the Boulder, CO area let me know and if
the weather is suitable I'll take you for a bi-plane ride.

Cheers!

 

[Fly Girl]

Argh!
Well, this isn't quite living up to my fantasies. Two
issues are clogging the works:

1) Rows from top always returns 1 and
2) I also need to control the horizontal scroll bar

Was there a function for the horizontal scroll bar also?

Thanks!
G

 

[Al Camp]

Fly Girl,
I went back to Stephen's code... that I have currently installed and
running in several of my apps, and I'm pretty sure I sent you all the
text/code involved.
Most of the code goes into the "internal" Form module, and as indicated
in my note, there is also an "external" module needed, called clsSetRow...
with it's associated code.

**AND, I hope you notice I sent two posts... I missed a small section of
code on my first post, so I sent a second post with the missing part right
after the first.

Hope that's the problem... if not, I would find a way to get the code
right from Lebans.com.

You need to find out why you can't open an mdb from someone else. You'll
be fighting this problem over and over if you don't.

When I open Stephen's original mdb file, I sign in the same way as I do
on all my other apps (access security only)
--
HTH...
Al Campagna
Candia Computer Consulting
Candia, NH

 

[Fly Girl]

I did finally get into the code from Leban and found the
code for the horizontal scroll bar. It was a Workgroup
File issue.

The code you sent works perfectly except that the Rows
from Top value is always either 1 or 0, hence, it doesn't
work for me. I am trying to figure out why my values for
original current section top and detail height (there is
no header) are not panning out. Perhaps it has something
to do with being in datasheet view rather than form view.
I may switch to just trying the vertical scrollbar code
since I have no problems with which record is selected.

If you are interested I'll let you know what I find out.

Thanks for your help. The code you sent was perfect and I
had no trouble setting it up in my project, I just don't
seem to get the right values in one spot.

GSchipper

 

[Fly Girl]

Code works now. I had to change the RowsFromTop
calculation to:

RowsFromTop = (OrigCurrentSectionTop * 10) / frm!
[EventsSub].Form.Section(acDetail).Height

Then I got the correct number consistently. After this I
had to make two other minor tweaks:

frm![EventsSub].Form.SelTop = OrigSelTop - RowsFromTop + 1
frm![EventsSub].Form.RecordsetClone.AbsolutePosition = frm!
[EventsSub].Form.CurrentRecord + RowsFromTop - 2

It's amazing how much work you need to do just to have
nothing happen on your form!

The horizontal slider bar code works perfectly.

Now on to the next hurdle....

 

[Guest]

Probably has to do with your security settings within
Access. I was able to get to the code thru an unsecured
version of '97. Maybe if you login as Admin?

 

[Al Camp]

Fly Girl,
Sorry... I got side-tracked for a bit.
Well, I'm stumped... because the Leban's code ran for me... right out of
the box. I checked and double checked that I had sent you all the
text/code.
Sounds like your doing OK now.

 

[Fly Girl]

The code seems rock-solid now. Of course that's never the
end of the story. While my call to it from a shortcut menu
is glorious, calling the same routine from one of the
control's events causes serious problems--an unhandled
exception at the application level.

Thought it was just because I'm rolling off the record
during the repositioning of the subform, but not doing
that does not prevent the error.

Always something!