By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,809 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

Scroll control of Access Form and Subform

P: 7
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.
Jan 23 '19 #1

✓ answered by 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....

Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
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.
Jan 23 '19 #2

P: 7
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".
Jan 24 '19 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 24 '19 #4

P: 7
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
Jan 24 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Yes - my suggestion was designing command buttons to do your scrolling for you on the form.
Jan 25 '19 #6

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Ctl3_10_19_AfterUpdate()
  2. Dim strPK1, strPK2, strBookmark As String
  3. Me.Refresh
  4. strPK2 = Me!Names
  5. strBookmark = Me.Bookmark
  6. Forms!frmTPL.SetFocus
  7. Forms!frmTPL.Form.SetFocus
  8. Parent.Requery
  9. DoCmd.FindRecord (strPK2)
  10. Me.Parent.Form![SumOf3/17/19].SetFocus
  11. Me.Parent![Labor Plan].SetFocus
  12. Me.Parent![Labor Plan].Form.Bookmark = strBookmark
  13. Me.Parent![Labor Plan].Form![3/17/19].SetFocus
  14. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Ctl2_3_19_AfterUpdate()    'This procedure
  2.               synchronizes scrolling between form and 
  3.               subform – but does NOT work if user tabs 
  4.               through controls.
  5. With Forms!frmLPT
  6.         Dim intTabIndex1, intTabIndex2, intTabIndex3 As Integer
  7.         intTabIndex1 = Me![2/3/19].TabIndex
  8.         intTabIndex2 = intTabIndex1 + 1
  9.         intTabIndex3 = intTabIndex2 - 44    
  10.         Me![sfrmLPT_Subtotals].SetFocus
  11.     If Forms!frmLPT![sfrmLPT_Subtotals].Form.SubdatasheetExpanded = False Then        
  12.     ‘MsgBox (SubdatasheetExpanded)        ‘used as a test to see if this path was being followed
  13.         'Me.Refresh                                   'Refreshes even without this statement
  14.         '[sfrmLPT_Subtotals].Requery                 'Requeries even without this statement
  15.         SendKeys "+^{DOWN}", True                   'Opens single instance of Subdatasheet
  16.         Do  ‘this Loop TABs over to next chronological
  17.               control on subform
  18.             SendKeys "{Tab}", True
  19.             intTabIndex3 = intTabIndex3 - 1
  20.             If intTabIndex3 = 0 Then
  21.                 Exit Do
  22.             End If
  23.         Loop
  24.         SendKeys "^{TAB}", True 'Exits subdatasheet & 
  25. moves to next 
  26.                 datasheet record
  27.         SendKeys "{UP}", True     'Shifts focus to previous record, bypassing subdatasheet
  28.         'Forms!frmLPT![2/10/19].SetFocus         'this will NOT reset focus
  29.         intTabIndex3 = intTabIndex2 - 43
  30.         Do  ‘this Loop TABs over to next chronological
  31.  control on main form
  32.             SendKeys "{Tab}", True
  33.             intTabIndex3 = intTabIndex3 - 1
  34.             If intTabIndex3 = 0 Then
  35.                 Exit Do
  36.             End If
  37.         Loop
  38.     Else ‘if subdatasheet already open, this path is followed
  39.     ‘MsgBox (SubdatasheetExpanded)        ‘used as a test to see if this path was being followed
  40.  
  41.         Me.Refresh
  42.         [sfrmLPT_Subtotals].Requery
  43.         Forms!frmLPT!sfrmLPT_Subtotals.SetFocus
  44.         Forms!frmLPT!sfrmLPT_Subtotals.Form![SumOf2/10/19].SetFocus
  45.         Forms!frmLPT.SetFocus
  46.         Forms!frmLPT![2/10/19].SetFocus
  47.     End If
  48. End With
  49. 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?
Mar 1 '19 #7

twinnyfo
Expert Mod 2.5K+
P: 3,284
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!
Mar 1 '19 #8

P: 7
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.
Mar 1 '19 #9

twinnyfo
Expert Mod 2.5K+
P: 3,284
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....
Mar 1 '19 #10

Post your reply

Sign in to post your reply or Sign up for a free account.