Run a Macro from a Shortcut Key? 1

[Quintios]

I would like the enduser to be able to highlight text within a memo field and use a shortcut key to make it all uppercase. It's essentially the same as running the VBA code:

Forms!formname!fieldname.Value = StrConv(Forms!formname!fieldname.Value, vbUpperCase)

which I have in the 'update' event in another area of the database. The deal is I don't want the *entire* field to be uppercase, just the selected text. Going to the format menu is not an option.

Is there a way to do this?

Onwards,

Q-

 

[eljakim]

Hi Quintios,

the problem you stated has a quite elegant solution
if you are using Access 2000.

For textboxes Access provides you with the properties
selstart, sellength and seltext. You do have to
take care that while a user is editing the value
of a textbox you can only retreive this value
by [manually] selecting all text in the textbox
and then retreiving it with seltext.

The sourcecode below works for a textbox named
txtTestField; it tests for the keystroke
alt-u (Alt: shift=4, U: keycode=85) and based
on this changes the selected text to uppercase.

Private Sub txtTestField_KeyDown(KeyCode As Integer, Shift As Integer)
' 20020320 - Eljakim
' Tests for Alt-U, then turns highlighted
' text in txtTestField to uppercase
Dim selstart As Long
Dim sellength As Long
Dim seltext
If (Shift = 4) And (KeyCode = 85) Then
   ' 1 - get info on selected text
   selstart = Me.txtTestField.selstart
   sellength = Me.txtTestField.sellength

   ' 2 - get all text in textfield
   Me.txtTestField.selstart = 0
   Me.txtTestField.sellength = 255
   seltext = Me.txtTestField.seltext
   ' 3 - change part of text to uppercase
   seltext = Left(seltext, selstart) & _
             UCase(Mid(seltext, selstart + 1, sellength)) & _
             Mid(seltext, selstart + sellength + 1)

   ' 4 - write changes back
   Me.txtTestField = seltext

   ' 5 - highlight original selection
   Me.txtTestField.selstart = selstart
   Me.txtTestField.sellength = sellength
End If
End Sub

Hope this helps!

Yours,

Kim

 

[Quintios]

I'm using Access 97 and it seems to be working; however, this is a memo field and has a lot of text in it. When I hit alt-u it indeed converts the selected text to uppercase, but deletes all the text after about 255 characters. Then I noticed your code and increased the number to 100,000 and got an overflow. Same thing at 50,000. 10,000 seems to be working quite nicely, but I'm still worried that some idiot might put more than 10,000 characters in there. What's the limit for the size for 'sellength'?

Regardless, it works great!!

Onwards,

Q-

 

[eljakim]

Quintios,

selLength returns an integer, this means it ranges
between -32.768 and 32.767... To be on the safe
side I would recommend just using 32.000....

However, memofields of that size are totally
unreadable... Who would want to type that much
text into a single field?

Yours,

Kim

 

[eljakim]

Disregard my last post... Just go for

2^15-1

exactly 32.767, but nicely written

 

[Quintios]

Well, the database will be used more to make a report rather than be legible from within the database. You're absolutely right, of course, but oh well, watcha gonna do?


Onwards,

Q-

 

[eljakim]

Quintios,

you could add a 'feature' of course, so store
comments in a separate table. I usually do this
to make sure of the following:
- owner of the comment gets filled in automatically
(windowsname)
- date/time get filled in
- comment gets filled in

If you use one big memofield and someone accidentally
erases the entire field, you loose everything (!)

With this setup you can still report everything, AND
you have extra (objective, ie date/time/windows username)
information...

Kim