Access 2007 Scroll() Event and sync a 2nd form


I want to capture from one form, frm_HeaderComplex, the horizontal scroll event when I scroll the form (datasheet style) left or right. When that happens I want another form, frm_Data, to scroll the same direction and the same amount, i.e. sychronise the horizontal scoll on 2nd form to the 1st form .

I see in Access Help there is a Scroll Event:

For ScrollBar
Private Sub object_Scroll( )

For Frame
Private Sub object_Scroll( ActionX As fmScrollAction, ActionY As fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)

But I can not seem to make either work

Using Access 2007. both forms are datasheet view.
Who is Participating?
LukeBConnect With a Mentor Author Commented:

bit pressed for time so can not post a sample , db too big and would have to chop a lot out ... and it is hooked to a back end SQLServer .

But, in brief, I have 2 forms that both are, and need to be , datasheet view (with split forms this is not possible?). The 2 forms have different datasources (again, with Split forms this is not possible? ). The top sub form's data source is a recordset of 4 records, the 'muitl header' title lines. The bottom sub form is a recordset of X records.

These 2 forms are on a main form, see my screen shot above 'form2.jpg'

In the lower subform, the 'data listing', I have the following code (sorry I said mouse event, it is a key event):
Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
    ' have to set the form's Key Preiew to YES
      On Error GoTo err_here
        If KeyCode = 39 Or KeyCode = 37 Or KeyCode = 9 Then
        gnDataViewerColOrder = Me.Form.SelLeft - 1         Me.Parent.Form!frm_Viewer_Assays_tblACIRL_TITLE.Form.SelTop = 5    
        Me.Parent.Form!m_Viewer_Assays_tblACIRL_TITLE.Form.SelLeft = gnDataViewerColOrder
      End If
    Exit Sub
    If Err.Number = 2101 Then
        Exit Sub
        MsgBox Err.Number & " : " & Err.Description, vbExclamation
    End If

End Sub

Open in new window

What it does is when a user's cursor is in the lower form, and they are using the cursor keys to move left-right, the bottom form's SelLeft position is sent to the top form and the top form SelLeft is reset.

Not pretty but works ...

Note again that the 2 subforms are different data sources, one is 'header lines' and the lower is the 'data'.

And yes, it is meant to mimic a mulit line title like Excel ...and no, to re-iterate, I don't want to export this to Excel. The reason is that this works very fast but moreover and there is a lot of data that a user can choose from and needs to view quickly without doing an export to Exle. For example in the screen shot above , for a master record there can be up to 7 submaster records (top part of form that I did not include in the screen shot above, to make it initially easier to explain), i.e. this would mimic the worksheets in a XLS workbook. For each of those possilbe 7 sub master records, when a user clicks on one of those submaster records the bottom part of the form, the one with the 'multi record titles' and 'data record' changes. And the 'multi record titles' change , they are not the same for the 7 sub masters - -i.e. there are 7 possible and different ''multi record titles' .

When users are using this they flick between master records, then maybe to a sub record to view the detailed data then move on to the next few master records, stop and view more detailed data. To havve to export this to Excel all the time would slow down that process.

So it is all working except the horizontal scroll bar sync, I am considering just hidding that and the viewer app will be fine.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't see a ScrollBar or a Frame object in Access. It seems you're trying to use an ActiveX control to do this. I would suspect that you will, at best, have limited success with these objects in 2007/2010. These MSForms objects are NOT designed for use in Access, and thus tend to be very tempermental, especially in the newer versions of Access.

You may be able to use the SelLeft property of your Datasheet form to set the "column" that is being viewed. According to MSDN:

"The SelLeft property returns a value between 1 and the number of columns in the datasheet."
Jeffrey CoachmanMIS LiasonCommented:
"I want another form, frm_Data, to scroll the same direction and the same amount,"


Can you tell us a bit about theses forms and how they are related?
In other words, can you tell us What your ultimate goal here is)?
Detecting "Scrolling" may not be the most effctive way to go about this.

For example, if your ultimate goal is to keep the two subforms Synchronized, then tell us what the common field is and you can simply "Move" both forms to that record.

Scrolling is a "Graphical" construuct, so synchronizing forms based on "Scrolling" can be problematic.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LukeBAuthor Commented:
ok - in Acc2007 Help I just typed in Scrollbar and Help said it was available. But I suppose it could be refereing to MSForm and not the Access forms ...

Jeff :
"Why" : because the data I want to show to the user has a quite abbrevated field name that gives its type and units etc . So to make it much easier for them to tell what the data is I want for each field a multi row 'column' title. The data comes from collation of XLS reports and those reports have a multi line header above the data columns and I want to somewhat mimic that so that the users get comfortable with Access (and not whine and moan about the abbreviated header...)

I know I can do this by putting labels or textboxes in the form's Header area and then in the Detail area put in the data in data sheet view. Screen shot 1 below show this. But I have about 6 of these forms to do and there are many fields in each. To make all the multi line labels for the header and also line them up so nice is a LOT of work. Plus if the user adjust the column width in the datasheet then the header alignment  is all messed up.

So I thought why not just have a table of header info of a few lines and put that above the data datasheet. I have made up code so is user adjust the column widths then the 2 datasheet forms sychronise the column widhts. The only issue is if the user scrolls left or right then the forms are not lined up.

So what I could do is turn off the horizontal scrolls on both forms and make a button for 'left scroll' and 'right scroll' using SelLeft as Scott suggests to sync the forms. But I thought maybe the Access scroll had code for it inbuilt or API. There is API at :
that does work on Acc2003 but not on my Acc2007 (or maybe my 64bit Win 7).  I've contacted the Author to see if there is an update for Acc2007 ... see al0so PAQ Q_26579905 -  they got to work on Acc2003 (I've posted there to see if MX tried on Acc2007)

 form with lables in Form's Header form with a datasheet for column names above data datasheet
LukeBAuthor Commented:
PS :
author at responded and he will look at in next couple weeks.

So I may have to go with the SelLeft and my own 'scroll left/right' button for now
LukeBAuthor Commented:
no word from the author at yet.

I will close this Q for now and if an update I will add a commont

why not just make this bad boy in excel?
you can put all the header info you want and lock/freeze it in place.
LukeBAuthor Commented:
Hi Conagraman,

No ... it is a "on the fly 'report' " (is a form, but using it to show users, so a 'report'). The 'report' is getting the data, on the fly, from the backend SQL db.

So to get it into excel, via a ODBC link or Access query link or whatever, and make it on the fly and make it fast (and outside of the MSAccess frontend) is going to more work than what it is worth, imo.
it would actually take a fraction of the code to export data to excel - from what I saw from the example database in the link you gave. Although the effect of scrolling the way you want sounds good it is for sure more work than it is worth and a better effect of only seeing the data for the record you are using by way of sql queries would be one of them. Also if that code were to work you would more than likely be using api calls that would probably not work on all users machines. The idiot proofing of any program should be kept in mind when creating an application. using a more common functionality to present the desired data may be appreciated from your end user.
have you thought about using a split form?
LukeBAuthor Commented:

Ok, good point on the API calles, that is likley what does ... I agree best to avoid if possible.

I defintely don't want to go the Excel route - it took a LOT to get my end users off of Excel and using a proper database, I am loath to let them back there and allow bad habits to creep back in.

No, not thought of a 'split form', could you send a screen shot demo of what you are thinking of? Not sure if this is new feature, or just the lingo that I do not recognise.
LukeBAuthor Commented:
(hold off for now while I discuss with Conagraman)
here is how to do it
ill make a quick example

here is a link to the text below

"To create a split form by using the Split Form tool:

In the Navigation Pane, click the table or query that contains the data that you want on your form. Or open the table or query in Datasheet view.
On the Create tab, in the Forms group, click More Forms, and then click Split Form.
Access creates the form and displays it in Layout view. In Layout view, you can make design changes to the form while it is displaying data. For example, you can adjust the size of the text boxes to fit the data, if necessary. For more information about form views, see the Understand Layout view and Design view section.

here is an example. i added some code to the mouse wheel event. which you might want to take out. it just moves the focus to the next text field when you scroll the mouse.  i know its not exactly what you want (and the database is not pretty) but it perhaps the closest thing to what your talking about.
LukeBAuthor Commented:
thanks Conagraman,
Ok, i've used that before in another db - first glance and thoughts is 'no' will not apply for me. But I will put in my subconcious and think about it.

For now I am using Mouse Event to sync the cursor between the 2 forms and seems to work ... so far ...
how is that working?
the only way i think it would work is if you are using a split form. i spread the textboxes out so that you could kinda get that effect but what the split form does is select the corresponding field in the other form.

 if you are able to get that to work with normal forms please post a sample on how thats working.
well i'm glad you found something that works for you.
LukeBAuthor Commented:
no word from the author at yet.

I will close this Q for now and if an update I will add a commont

grade of B because my method is a bit nasty, but does work ...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.