Control scrollbar position on a form with ONE record?

[Mike Krailo]

I'm working on a form that that has one record but many controls and data that takes up more than the screen size and this causes a vertical scroll bar to be visible. There is a a series of command buttons that cause a requery to occur and this causes the form to reposition itself higher on screen which is undesirable. I would like to know if there is code to save the current scrollbar position and restore it after each requery. Is this possible in this scenario?

 

[pbaldy]

I would probably use a tab control and not have to scroll. You can see if this works for you if you want to keep it as is.

Select A Row Page

 

[MajP]

There is not scroll bar control through VBA, you have to use the windows API SetScrollInfo, GetScrollInfo

SetScrollInfo function (winuser.h) - Win32 apps

The SetScrollInfo function sets the parameters of a scroll bar, including the minimum and maximum scrolling positions, the page size, and the position of the scroll box (thumb). The function also redraws the scroll bar, if requested.

docs.microsoft.com

SetScrollInfo function (winuser.h) - Win32 apps

The SetScrollInfo function sets the parameters of a scroll bar, including the minimum and maximum scrolling positions, the page size, and the position of the scroll box (thumb). The function also redraws the scroll bar, if requested.

docs.microsoft.com

Unless you are versed in API, this may be challenging

Could you add a tab control for the form. Seems to me it you have so many controls some grouping would be more functional and aesthetic. (pbaldy beat me)

 

[arnelgp]

another possible solution is to use a "dummy" unbound textbox.
add this textbox on the top-most, left-most position on the form.
set its width, height to 0.
before and after requery, Setfocus on this textbox.

 

[pbaldy]

another possible solution is to use a "dummy" unbound textbox.
add this textbox on the top-most, left-most position on the form.
set its width, height to 0.
before and after requery, Setfocus on this textbox.

If the form is scrolled to the bottom at the point of the requery, won't setting focus to a textbox at the top leave it scrolled to the top?

 

[Mike Krailo]

I don't think a tab control would do anything to help the data entry of this form. If anything, that would make it worse as you would need to switch tabs which is also undesirable. This is not my database design by a long shot. The guy who did this made one huge table which is so wide it would make all of you cringe in horror. In spite of that, it does get the job done.

The form just has an annoying readjustment of the scrollbar as you work your way down the form. It's the sigma button that runs the calculations for each period scheduled, so I was trying to see if there was an easy fix for it. Here is what the form looks like and I know it's ugly, but it does work.

I'm not sure I follow what Arnel was suggesting.

 

[MajP]

Everyone has their preferences, but I would have to think that a tab would be user friendly and aesthetically pleasing. You can set it up so when you tab through Sim 1 it goes to tab 2, and when done 2 it moves to 3. When filling out block 2 do you need to look at the data in block 1 to determine your inputs? If so which fields? In other words if you had a tab then tab 2 might have to repeat some of tab 1 data. I do not care for the colors, but I think the form is very readable and well organized. However as you mentioned each sim should be a record so I am sure the table is a disaster.

You could use a normalizing query on this and have a main form with a continuous subform. I assume in the flat table each sim as the same fields repeated. If you unioned this and made it a subform. Then you would move through records not one big form. This would solve a lot of problems with scrolling.

The union would be pretty easy.
qrySim1 (all the sim 1 fields and the main ID)
qrySim2 (all sim 2 fields and the main ID)
qrySimN ....
Then union all of them together and make it a subform using the controls from Sim 1 that you already have. What will be nice is you can scroll the sub and still see the main form information. It sounds like work but I think you could do this very fast if this is truly as consistent as it appears.

If this is something you can post, I would take a quick look to see if I could demo and if that was any better UI.

 

[MajP]

However, now that I read your initial post. I think it is pretty easy. I assume Sigma does some code then causes a requery. When you are done the requery you want the window to show with sim three towards the top of the window.

Public Sub CmdSigma3_Click
do some code
me.requery
someControlBelowSim3.setfocus ' Move sim 3 up
someControlInSim3.setfocus 'Move into sim 3

To find the correct control to set focus. Move the screen to the proper return position. Find a control at the bottom of the screen. If necessary you can add an unbound control to precisely locate the return.

 

[pbaldy]

However, now that I read your initial post. You can easily save the "LastControl". Then when you requery set focus back to the last control. I can demo that.

I had the same thought, and it was pretty simple.

I had another thought, which is having a single set of controls for the repeating fields. You could use a combo or option group to change their control sources to the appropriate group (the 1,2,3 in your picture). That would avoid both scrolling and tabs, though the user would still have to initiate the change. I guess I just don't like the scrolling. If your users do, then it's fine like it is.

 

[Mike Krailo]

MajP, thanks for your follow up comment. I didn't consider tabbing through each of those periods because the shortcut for entering default data is to click on each button sim1, sim2, sim3, etc. Its only when there is some deviation when tabbing would come into play.

There are several ideas you guys have given me to at least experiment with. So thanks for you input. I couldn't imagine how to use the tabs before but now it makes sense to build that functionality into the command buttons.

 

[Mike Krailo]

OK, I played around with using three different tab controls to eliminate the scrolling. The problem now is when I get to the last comment box, I would like the tab key to move to the control on the next tab control page. it keeps moving to a different control on the same tab control page. I tried setting the Cycle property to each available setting and it still doesn't move to the next tab page.

This seems like it should be easy to do but I'm not seeing the solution to this. On exit, Lost focus, it doesn't matter, it still stays on the same tab control.

 

[gemma-the-husky]

Is this a continuous from, or a single form with 3 sections.

I assume the latter.

Can't you have a form with 4 tabs. put the details for Section 1, 2 and 3 on tabs 2,3 and 4

Then have a dashboard section on the first tab that picks out the vital feature from the 3 sections. Maybe show the scheduled time line for each section. You can have the same field bound to more than one control, so it's not an issue.

 

[Mike Krailo]

That's a good idea Gemma. I will do that. It would still be nice to know how to navigate those tabs using the key board though. So if anyone knows the answer to that it would would be nice to know.

 

[MajP]

This works for me to tab to the next page

Public Function NextPage()
  Dim idx As Integer
  idx = Me.TabCtl2.Value
  If idx = Me.TabCtl2.Pages.Count - 1 Then
    idx = 0 'return to first
  Else
    idx = idx + 1
  End If
  Me.TabCtl2.Value = idx
End Function

you can either make individual on exit event procedures for the last control on each page. Or you can type this in the event property directly for those controls instead of [Event Procedure]:
=NextPage()

 

[arnelgp]

that is hard to implement.
what if the user want to back-tab and go to previous control on same page.
since you have a code on it's "exit" event, instead of going to previous
control, you landed to the next page.

 

[MajP]

@arnelgp
Good point. How about?

Public Function NextPage()
  Dim idx As Integer
  idx = Me.TabCtl2.Value
  If idx = Me.TabCtl2.Pages.Count - 1 Then
    idx = 0 'return to first
  Else
    idx = idx + 1
  End If
  Me.TabCtl2.Value = idx
End Function

Private Sub City_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = 9 And Shift = 0 Then
   NextPage
 End If
End Sub

Private Sub ContactName_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = 9 And Shift = 0 Then
   NextPage
 End If
End Sub
 
Private Sub Country_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = 9 And Shift = 0 Then
   NextPage
 End If
End Sub

In this case the event procedures are for the last control on the page.

 

[gemma-the-husky]

I am pretty sure you could set up an autokeys macro to use a key stroke combination you don't use for anything else.
You can do things with autokeys that you can't do any other way.

call a function for the key press to test the active form and active control .
if the active control is a tab, then move to the next tab page, using examples already given.
if it's not, just ignore the key stroke.
You need a way to determine whether the activecontrol is within a tabctl. Not sure how easy that is.

I have a hotkeys table in some apps, for Ctrl plus single chars, so 0 thru 9, and A thru Z
I tend to use them as a short cut to open particular named forms.

My autokeys macro just calls this function with the key (strg)

Public Function useautokey(strg As String)
'So say, ctrl-1 for next tab

if strg = "1" then
       'tab to next page of tab control, if the active control is a tab control.
       'you have screen.activeform and screen.activecontrol which makes it easy. 
else
       'do the action for whatever the hotkey code (strg) represents. 
end if
end function

 

[Mike Krailo]

MajP, that code is brilliant. Thanks for opening my eyes to that trick. I like how you took the Shift+Tab into account as well. I also tried making a super simple dashboard as well above the tab control as suggested by Dave earlier and that works quite well for speed entry of the default times that are used most often. Only issue is there is half second of visual jittering of all the tab controls when a dashboard button for the first tab is pressed. Haven't trouble shot that yet except that it also occurs if the very first tab is clicked on, but none of the other tabs cause the jittering.

While I was at it, I ended up making another table of different time slots that is selectable from a dropdown near the top as well which works exactly like I wanted it to. Usually everything starts at 0800 but not always, so this makes it real easy to enter in three or more different sets of default time slots.

Most importantly, no more vertical scrollbar. Thank you all for helping me out with this.

 

[MajP]

You really should think about normalizing this. This makes my skin crawl thinking how painful this db would be to work with. Everything has to be painful. You will save so much work in the long run.

 

[Mike Krailo]

I may do that MajP. It is was much worse in the delivered format but you're absolutely right, it needs the complete overhaul.