• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

Get selected text from MS Access Text Box

I am working with a MS Access form and VBA to edit address information.  I need to be able to select part of the address then capture that selected part and assign it to a variable.

For example:  The text box on the form has in it:
"200 Main St Bldg 27"

I want to select the "Bldg 27" portion of this address and load it to a variable.  Once I capture this part, I will change the current text box to "200 Main St" and move the "Bldg 27" portion to a new location.
0
drjoeusa
Asked:
drjoeusa
  • 5
  • 3
  • 2
  • +1
1 Solution
 
drjoeusaAuthor Commented:
I will be away from my desk until Thurday, mid-afternoon.  Hopefully someone can give me some help on this when I get back.  Thanks,
0
 
burrcmCommented:
How many records? If it is a one off and up to 1 million, I would do it in Excel - text to columns with space as delimiter then reassemble.

Chris B
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Want to make sure I understand correctly; you as a user want the ability to select text and have what you selected moved to a variable?

<<I want to select the "Bldg 27" portion of this address and load it to a variable.  Once I capture this part, I will change the current text box to "200 Main St" and move the "Bldg 27" portion to a new location. >>

 What you describe is standard cut and paste from a user perspective, so I must be missing something here.

 If you want to work with this in code, you be using .SelStart and .SelLength of the text control to access selected text.

After that it would be the string functions Left() and Mid() to manipulate the entire text.

 or as Chris suggested,  you could use Split() to break the entire string apart based on a delimiter and then work with each piece as appropriate (no need to go to Excel).

Jim.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
aikimarkCommented:
I would recommend an update query to change all the rows, rather than doing this with form code.

@drjoeusa

What criteria do you use to determine how much of the last part of the address you are going to move?  For instance, is it always going to be "Bldg " and some number?
0
 
drjoeusaAuthor Commented:
I'm dealing with about 5,000 records that are the exceptions.  I've already gone through and done the splits that are "programmable."  

My immediate thought is what Jim suggests.  I thought it should be a simple cut and paste operation.  That's what I do manually.  

But:  When I go to "Help", I find  "CUT" & "PASTE" methods that I think should apply to a textbox.  However, I don't have access to these methods.  Perhaps it a problem with the references I have enabled.

I also considered using "SendKeys" to send "Ctrl-X"/"Ctrl-V" to cut and paste, but again couldn't access the commands.

Using Excel would probably work, but I hate to go through the export/import process.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<t:  When I go to "Help", I find  "CUT" & "PASTE" methods that I think should apply to a textbox.  However, I don't have access to these methods.  Perhaps it a problem with the references I have enabled. >>

  When you select text in a text box, the .SelStart and .SelLength properties tell you what's been selected.

  Using those, you can set a command button to do the cut and paste by manipulating the text in the control using Left(), Mid(), etc to change the data.

 What I don't understand though is why you can't do a cut and paste, or use SendKey's, both of which should work.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
And BTW, there's also .SelText, which returns the selected text.

Jim.
0
 
aikimarkCommented:
You might create a feature on your form that allows you to select a number and that number of the right-most words in the textbox would be split off from the current contents and moved to another control.  Such a feature would certainly speed up the manual processing required.

You wouldn't need to actually select the words, just specify the number of words.
0
 
drjoeusaAuthor Commented:
I think that the .SelStart and .SetLength approach will work for me.  My first try didn't work because the textbox needs to have focus.  When I setFocus, it goes to the default selection which gives me a .SelStart = 0.

I'm think that I may be able to run the code from a macro attached to a function key.  I'll work on that.

I don't think that the specifying the number of words with work.  Sometimes I have to take stuff out of the middle.
0
 
drjoeusaAuthor Commented:
I've attached to code to a macro executed by ctrl+m and it works perfectly.  I was surprised that .selStart begin with the number 0 rather than 1 so I have to use mid(strWork, .selStart + 1, .selLength) to get the right selection.

Thank you everyone for your help.  I consider the problem solved.
1
 
drjoeusaAuthor Commented:
The solution was exactly what I was looking for.  Thank you.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now