On an access 2007 form with a text box with text larger than the vertical boundry of box, how do I save the position of the text box so that it doesn't snap back to top of the vertical scroll bar

Bevos
Bevos used Ask the Experts™
on
Hello, I have a database form with a text box that contains a large paragraph memo field which typically exceeds the bounds of the text box.  Is there a way so that if I have scrolled partway through the text box that this position is retained should I click on another form element?  That is to say, is there a way to avoid the default form behavior that 'snaps' the vertical scrolll bar to the topmost position after scrolling through this memo field?

Thanks,
Bevo
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
i don't think that is possible.
you can however, move the cursor to the end of the text of the textbox, using the onfocus event of the textbox

private sub textbox_gotfocus()

me.textbox.selstart=len(me.textbox.text) + 1

end sub
Most Valuable Expert 2014
Commented:
It is partly doable.
You can save out the cursor position and return back to it when the focus returns to the control
------------------------------------------------------------------------------------------------------------------------------
You have to capture the position on the LostFocus event to a form level variable, and then feed it back in on GotFocus
 
Option Compare Database
Option Explicit
Dim mlngSelStart As Long

Private Sub txtMyTextBox_GotFocus()
Me.txtMyTextBox.SelStart = Nz(mlngSelStart, 1)
End Sub

Private Sub Comments_LostFocus()
mlngSelStart = Me.txtMyTextBox.SelStart
End Sub

Open in new window


The MAJOR caveat is that the GotFocus event CANNOT occur with a click.
The click cursor placement occurs AFTER the GotFocus event occurs and overwrites what happens in the code

Tab in = good
[enter] in = good
VBA in = good

Click in = bad

--------------------------------------------------------------
If you need the text to stay frozen WHILE your not in that control, that's trickier.
There's a discussion of that here
http://www.tek-tips.com/viewthread.cfm?qid=1379615&page=334
Most Valuable Expert 2014
Commented:
You could also commit a little legerdemain with the click event to override what the use does when it first gets the focus.
In the code sample, my control is named "Comments"

Alter as needed
Option Compare Database
Option Explicit
Dim mlngSelStart As Long
Dim CommentsGainedFocus As Boolean


Private Sub Comments_Click()
If CommentsGainedFocus = True Then
    Exit Sub
Else
    Me.Comments.SelStart = Nz(mlngSelStart, 1)
End If
CommentsGainedFocus = True
End Sub


Private Sub Comments_GotFocus()
Me.Comments.SelStart = Nz(mlngSelStart, 1)
End Sub


Private Sub Comments_LostFocus()
mlngSelStart = Me.Comments.SelStart
CommentsGainedFocus = False
End Sub

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
As you can see from what Nick67 posted, anything is possible.

If I am understanding your request correctly, another option would be to open the textbox data in a Pop-up subform...

...then the cursor location in the pop-up form would remain the same regardless of what the user did in the main form.

Minimal code would be required.
Create a form with the Primary key from the main form and the text field.
Then use code like this to open this form to the current record on the main form

    DoCmd.OpenForm "YourPopupForm", , , "ID=" & Me.ID


JeffCoachman
Top Expert 2016

Commented:
The question posted never mentioned about clicking on the text box, just scrolling the vertical bar. That is why I said it is not possible.
Most Valuable Expert 2014
Commented:
<this position is retained should I click on another form element>
That, to me said keep my place in the scrolling on LostFocus.
"retained" was the ambiguous part.
Returned to when the control regains the focus (ie clicking, tabbing ect)--yes, that was doable in a standard textbox

Freeze the text in the control--not in standard textbox, but apparently with an MS Forms 2.X Text box--yes.

Where there is a will, with MS Access there is almost always a way.
Sometimes it takes a lot of hacking and isn't worth the effort, but there usually is a way :)
Most Valuable Expert 2014
Commented:
Hell,

Now that I think about it, you could put an unbound textbox the same size as the bound one on the form.
When the bound one loses focus, the unbound one gets its value set to some portion of the contents of the bound one.

When the unbound one gets focus, it throws the focus back to the bound control and goes .Visible = False.
Fiddle with the coding and you could get pretty good fidelity.

The scrollbar would be a fly in the ointment, but take it away and have the users move via the cursor only, and you'd be most of the way there.

But it'd be a lot of coding and probably not woth the effort
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
<Sometimes it takes a lot of hacking and isn't worth the effort, but there usually is a way>
Yes, that is the beauty of this site.
It shows that almost anything is possible.
Every Expert has a puzzle that they just can't let go of until they find a solution.
;-)
This is why I try to say:
This is not possible "Directly"
...in case another expert wants to take on the challenge...

This is why I suggested the Pop-up form approach as just another alternative.

Presented with all three options, the OP now has multiple viewpoints, all perfectly valid in their way:
1. capricorn1: "i don't think that is possible."
2. Nick67: "It is partly doable...But it'd be a lot of coding and probably not worth the effort"
3. Boag2000: "another option would be to open the textbox data in a Pop-up subform"

JeffCoachman

Author

Commented:
Wow, what an all star expert responses from Capricorn, Nick, and Jeff!  I think I have enough information now to tinker with the design and fix the problem so I'll close the question now and award points to you all.

Thanks again for all the thoughtful comments,
Bevo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial