Solved

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

Posted on 2010-11-15
18
2,140 Views
Last Modified: 2012-08-13
Hi

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.
0
Comment
Question by:LukeB
18 Comments
 
LVL 84
Comment Utility
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."

http://msdn.microsoft.com/en-us/library/bb213764(v=office.12).aspx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"I want another form, frm_Data, to scroll the same direction and the same amount,"

Why?

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.
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
Scott:
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
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
PS :
author at  http://www.codekabinett.com 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
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
no word from the author at  http://www.codekabinett.com yet.

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

0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
why not just make this bad boy in excel?
you can put all the header info you want and lock/freeze it in place.
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
have you thought about using a split form?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:LukeB
Comment Utility
Conagraman

Ok, good point on the API calles, that is likley what http://www.codekabinett.com 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.
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
(hold off for now while I discuss with Conagraman)
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
here is how to do it
ill make a quick example

here is a link to the text below
http://office.microsoft.com/en-us/access-help/introduction-to-forms-HA010343724.aspx#BMsplitformtool

"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.

"
sf.bmp
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
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.
Split.accdb
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
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 ...
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
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.
0
 
LVL 1

Accepted Solution

by:
LukeB earned 0 total points
Comment Utility

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_here:
    Exit Sub
err_here:
    If Err.Number = 2101 Then
        Exit Sub
    Else
        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.
0
 
LVL 10

Expert Comment

by:conagraman
Comment Utility
well i'm glad you found something that works for you.
0
 
LVL 1

Author Closing Comment

by:LukeB
Comment Utility
no word from the author at  http://www.codekabinett.com 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 ...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now