Scroll control of Access Form and Subform

[gburch]

I am seeking code that will horizontally scroll a subform off the page as a user scrolls the parent form off the page (column for column). Form and subform are both in datasheet view. I am working in Access-2016 under Win10.

Stephen Lebans' open source solution is too complex for me, and apparently obsolete for anything beyond Access-2003. His solution is part API programming, and the scuttlebutt seems to be that scroll control in datasheet view is under API control, not Access/VBA control.

Oh, and I might add that I am a VBA neophyte.

[twinnyfo]

gburch,

I don't know of any pure VBA solution to this.

However, an alternative method may be to create a set of your own navigation buttons which, when one clicks to "scroll" right, the left most column of the main form and subform are hidden and the next available column to the right is unhidden.

This is just thinking out loud--I've never tried anything like this. However, it seems that you have a very "wide" dataset on these two forms that you want to keep in sync?

Either way, there could be considerable challenges with these approaches, and there would be no "easy" way for you to design this.

[gburch]

Dear Twinnyfo,

I don't much care if the solution is VBA or C#, as long as it works.

Anmd, yes, it is a wide dataset - a field for each week with a summary field for each month - it spans out to the end of Calendar Year 2019. I will be adding blocks of a calendar-quarter from time to time.

Not sure what you have in mind with creating a set of "navigation buttons". The term Navigation Button is an Access Property, but it applies to Record navigation (i.e., Vertical), and my problem is Horizontal.

However, I will do some internet searching on the topic of "creating navigation buttons" and see what I come up with. Perhaps you are thinking outside the box, while I am still a prisoner in it.

Glen

PS I like your Goofy logo - "ahyilk".

[NeoPa]

GBurch:
I don't much care if the solution is VBA or C#, as long as it works.

I think you misunderstand. Introducing another programming language at all is an extremely complex path. Twinny was referring to using VBA (The only language available natively to Access.) in conjunction with other design changes on your form.

You're right to be confused by the term Navigation Button when used in this context. I suspect Twinny was simply referring to a Command Button added to your form that would be used to aid lateral navigation of your data. It isn't a simple solution by any means, certainly not for a novice as you say you are, but if you come here with complicated and unusual requests you can't be too surprised if the answers aren't found within neat boxes.

Most of our experts are happy to hold your hand through your learning but you should know this is far from trivial. Nevertheless, it sounds like Twinny may have some ideas for you. It may help if you could give an indication of how prepared you are to stick with this if it gets a little complicated for you.

[gburch]

Dear NeoPa,

Yes, I am committed to pressing on with a solution.

I have tried for weeks to get some version of Stephen Lebans' GetSetScrollInfo working. Also someone posted an updated version of this (for later ver. of Access) and it includes a small demo, but I have been to duplicate it - in fact I just took another look at it, and it locked up Access-2016.

Any and all tips are appreciated.

Glen

[twinnyfo]

Yes - my suggestion was designing command buttons to do your scrolling for you on the form.

[gburch]

I'm posting this update for the sake of the community - others may be interested in these solutions.

I have two buttons on my Switchboard for updating the Labor Plan. The Top-Down view uses a totalizing query as Record Source for the main form. The subform contains the individual projects that each person has time planned for (as a % of his time for each weekly period). When the user selects the "Top-Down" button, a datasheet opens with the total commitment (usually 100%) for an alphabetical list of all employees. When an employee-of-interest is to be updated, the user selects the "+" at the left margin and a subdatasheet for that individual opens, exposing the projects he/she has historically supported. As each weekly entry for a given project is updated, the focus jumps to the control to the right.

What follows is the Event Procedure that keeps the main form and subform controls for each week aligned one-over-the-other:

Private Sub Ctl3_10_19_AfterUpdate()
Dim strPK1, strPK2, strBookmark As String
Me.Refresh
strPK2 = Me!Names
strBookmark = Me.Bookmark
Forms!frmTPL.SetFocus
Forms!frmTPL.Form.SetFocus
Parent.Requery
DoCmd.FindRecord (strPK2)
Me.Parent.Form![SumOf3/17/19].SetFocus
Me.Parent![Labor Plan].SetFocus
Me.Parent![Labor Plan].Form.Bookmark = strBookmark
Me.Parent![Labor Plan].Form![3/17/19].SetFocus
End Sub

Of course, I had to tailor the date references for each weekly control. Note that strPK1 is an abandoned variable.

When it came to working the Bottom-Up view, I thought it would be a simple matter of reversing the references to main forms and subforms, but that did not work. Also, I had noticed an undesirable bit of user behavior - not clicking the "+" to open the subform. The subform for this now contained the individual person's total commitments for the week. The main form contained the person's individual project planning - in some cases 3 or 4 projects might be listed. I thought it was important that a user making updates see what effect the updates were having on the totals - a person might end up overbooked or underbooked if this was ignored. The only way I could open a single instance of a subdatasheet (all other methods opened them all, making far too busy a screen) was with SendKeys and a keyboard shortcut. Then I began to notice a curious thing - any procedure executing a SendKey will no longer execute a SetFocus method. So I had to keep using the SendKey/shortcuts methodology to get the focus where I wanted it. It was a bit more code than I would have liked, but it finally worked. Here's what worked:

Private Sub Ctl2_3_19_AfterUpdate()    'This procedure
              synchronizes scrolling between form and 
              subform – but does NOT work if user tabs 
              through controls.
With Forms!frmLPT
        Dim intTabIndex1, intTabIndex2, intTabIndex3 As Integer
        intTabIndex1 = Me![2/3/19].TabIndex
        intTabIndex2 = intTabIndex1 + 1
        intTabIndex3 = intTabIndex2 - 44    
        Me![sfrmLPT_Subtotals].SetFocus
    If Forms!frmLPT![sfrmLPT_Subtotals].Form.SubdatasheetExpanded = False Then        
    ‘MsgBox (SubdatasheetExpanded)        ‘used as a test to see if this path was being followed
        'Me.Refresh                                   'Refreshes even without this statement
        '[sfrmLPT_Subtotals].Requery                 'Requeries even without this statement
        SendKeys "+^{DOWN}", True                   'Opens single instance of Subdatasheet
        Do  ‘this Loop TABs over to next chronological
              control on subform
            SendKeys "{Tab}", True
            intTabIndex3 = intTabIndex3 - 1
            If intTabIndex3 = 0 Then
                Exit Do
            End If
        Loop
        SendKeys "^{TAB}", True 'Exits subdatasheet & 
moves to next 
                datasheet record
        SendKeys "{UP}", True     'Shifts focus to previous record, bypassing subdatasheet
        'Forms!frmLPT![2/10/19].SetFocus         'this will NOT reset focus
        intTabIndex3 = intTabIndex2 - 43
        Do  ‘this Loop TABs over to next chronological
 control on main form
            SendKeys "{Tab}", True
            intTabIndex3 = intTabIndex3 - 1
            If intTabIndex3 = 0 Then
                Exit Do
            End If
        Loop
    Else ‘if subdatasheet already open, this path is followed
    ‘MsgBox (SubdatasheetExpanded)        ‘used as a test to see if this path was being followed
 
        Me.Refresh
        [sfrmLPT_Subtotals].Requery
        Forms!frmLPT!sfrmLPT_Subtotals.SetFocus
        Forms!frmLPT!sfrmLPT_Subtotals.Form![SumOf2/10/19].SetFocus
        Forms!frmLPT.SetFocus
        Forms!frmLPT![2/10/19].SetFocus
    End If
End With
End Sub

Someone who understands Event handling can perhaps explain why the use of SendKeys in a procedure turns OFF the SetFocus method. I could not find this documented anywhere, but it seems to be inductively proven by this experience.

Also, synchronized scrolling will not work if a user tabs through a series of controls to get to a starting control of interest. When I tried using the procedure above with the LostFocus Event on a single control, the program went into an endless loop and overwrote code in other control events - in a word, a disaster!

Is there any way to make an Event Procedure conditional?

[twinnyfo]

I'm glad you found a solution, although I always try to shy away from using SendKeys() when possible, because a user clicking anywhere else while the code is executing will cause problems. However, I understand that there are times when this is a necessity. I use a variation in my own projects from time to time.

I would also ask you to reconsider your naming conventions for Fields/Controls. The name "SumOf3/17/19"--for whatever reason--is a naming convention that is just waiting for a disaster. You should never have slashes in your names, as this can cause serious problems within VBA. Underscores work perfectly well, and never get confused for another usage.

gburch:
Is there any way to make an Event Procedure conditional?

What do you mean by "conditional"? There may be some connotations or implications which you don't intend. But from a programming stance, all code is "conditional" from the sense that you test "conditions" to determine whether or not you want to execute any portion of code. In fact, you have much code above that is conditional, becuase you are using If...Then statements.

I don't know if that is what you are asking, but that is always how you control whether or not to execute any type of procedure/line of code.

Hope this hepps!

[gburch]

The problem I foresee with building an AfterUpdate Event for when the user updates before tabbing/entering/arrowing right, and building a LostFocus or other event for when the user tabs without updating, is that both will execute when the user does update. I need to test for a condition of Tab-Without-Update to execute the LostFocus event. I suspect this gets into the Access app itself and is not under a developer's control.

Thanks for the warnings about the "/" - I have been careful to [] all such references, but I can always learn a better way.

[twinnyfo]

Thus, you have uncovered one of the many challenges/weaknesses (Microsoft would call it a "feature") of using Sendkeys(). You would have to access the system's activity to see of a user were doing things without doing other things first.

Again, based upon what you are doing, you may be stuck with using SendKeys(). I've been trying to think of some alternate methods of displaying what you want to display by using the standard tools Access has made available to us....