Pasting Without Complaint

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi All

I do a lot of pasting of text into cells in Access. Occasionally , the
text I am pasting can be too long ,, and access complains on each
occasion. I have to start again and shorten the text I'm entering. This
brings the whole process to a halt.

Is there a way , does any body know , to have Access do a 'best fit' ,
and accept as much of the text as it can , and all me to go on without
getting an error? I appreciate this would mean foreshortening the text ,

and cutting off the overspill , but it would be fine for my purposes.

Grateful for any advice.
 
Hi Colin,

Going on the presumption that this is a case of pasting rows into a
table and not just text into one field here is one idea. Create a work table
that has the same fields as your destination table, only with the fields all
set to the largest size you might attempt to paste into them. Then create an
append query that truncates each field to the permitted length and appends
into the destination table. Finally create a delete query to clear out the
work table. Then your process would be open work table, paste, close, run
append query and then run delete query.

If that does not do what you want, please provide additional details.

Clifford Bass
 
Hi Clifford

Thanks for your help.

Actually , it IS a case of pasting text into a single field.

I'm transferring text from another program into Access. This is
automated , and it keeps tripping up when the source text is longer that
the destination cell. Access objects and throws the process out. I'd
much rather it just fitted what it could into the available space and
carried on. I was just wondering if there was a switch or an addin I
could use to make it suppress the warning popup.

I do know that when pasting rows or a table into an existing table , it
will shorten the incoming data to fit , and that's what I need to happen
here.


Best wishes.
 
Hi Colin,

Is the transfer directly into a table? Or through a form?

Clifford Bass
 
Hi Colin,

Is the transfer directly into a table? Or through a form?

Clifford Bass
Click to expand...

Hi Clifford

It's plain text cut from an external program and pasted directly into a
cell in the table.

In the vast majority of cases the text fits into the space the cell
allows , with no problem. However , sometimes the text is too long and
causes Access to default to the error message. This trips up the whole
process. The situation is caused because I can't predict the length of
the incoming text , so I need access to accept it and fit as much as it
can without giving an error message.



Best Wishes


Colin
 
Hi Clifford

It's plain text cut from an external program and pasted directly into a
cell in the table.

In the vast majority of cases the text fits into the space the cell
allows , with no problem. However , sometimes the text is too long and
causes Access to default to the error message. This trips up the whole
process. The situation is caused because I can't predict the length of
the incoming text , so I need access to accept it and fit as much as it
can without giving an error message.



Best Wishes


Colin
Click to expand...

Only way that occurs to me is to use an unbound TextBox for pasting into
then transfer the correctly length to the bound control.
 
Hi Colin,

Rick's idea is essentially one idea of what I am thinking. Create a
form with all of the fields that you paste text into in the table, bound to
text boxes. Then create a whole bunch of unbound fields that match the bound
ones. Use their after update events to transfer the appropriate texts to the
bound fields. Alternatively, use a variation on my first idea. Create a
table with fields long enough to handle the incoming test. It could be
related one-to-one to the main table. Then use an update query to transfer
the data with the correct lengths. This might have the advantage of
preserving the full data until it is absolutely not needed. There might be
additional massages that could be done with the text before it is
transferred, such as abbreviating words, triming excess spaces and so forth.

Clifford Bass
 
Hi Colin,

This issue intrigued me enough to putter around and come up with a
better answer. It will require a form specifically designed for this task.
On it, create bound text boxes for all the fields you paste into. Add
"=PasteIt()", without the quotes, to their On Click events. Also, enter the
appropriate field size in its Tag property. Add another text box named
"txtWork" to the form. This one will be unbound. In the form's code add
this function.

Private Function PasteIt()

Dim txtCurrent As TextBox

Set txtCurrent = Me.ActiveControl
txtWork.SetFocus
DoCmd.RunCommand acCmdPaste
txtCurrent.Value = Left(txtWork.Text, txtCurrent.Tag)
txtCurrent.SetFocus

End Function

Now when you have copied something all you have to do is click in the
field where you want to paste it. Of course this is dangerous because you
can easily, inadvertantly overwrite something in the wrong text box. If this
is an issue, use the On Dbl Click event instead.

Happy Pasting!

Clifford Bass
 
Hi Colin,

Or, even better because it eliminates the txtWork text box:

Public Function PasteIt2()

' Requires "Microsoft Forms 2.0 Object Library"
(c:\windows\system32\fm20.dll)

Static doWork As New DataObject
Static txtCurrent As TextBox

Set txtCurrent = Me.ActiveControl
doWork.GetFromClipboard
txtCurrent.Value = Left(doWork.GetText, txtCurrent.Tag)

End Function

Clifford Bass
 
Hi Clifford

OK thanks for your brilliant help with this.

I have to say that technically I'm not up to some of the methods you
suggest , but I'll get there. I wouldn't know how to implement your code
below for example.

I was hoping there would be a switch in the option panel , or an addin I
could import to allow a simple 'best fit' in the target cell. Or even
something that tells the cursor to go on to the next cell when the
current cell is full. This , rather than bringing the whole thing to a
halt.

Is there something that could be run when the db starts up to allow this
behaviour?

Sounds simple enough - It's certainly an interesting one.


Best Wishes


Colin
 
Hi Colin,

There does not seem to be any option for putting it into the mode you
desire. I have a memory of systems where you could configure the cursor to
jump the next field as you type, when you hit the size limit. Possibly an
older version of Access.

Regardless of that. To implement my solution create a form if you do
not already have one that uses the appropriate table. Open up the form's VBA
code window (the icon with the square tube(?) with three colored arrows(?) on
it. Copy and paste my PasteIt2() function into it. While in the VBA Editor
go to the Tools menu and choose References. Look for "Microsoft Forms 2.0
Object Library" and check it. If you do not find it use the Browse button to
find and select "c:\windows\system32\fm20.dll". Close out of the references
box and do a Debug, Compile Database. Back in your form in design mode show
the properties of a text box control for one of your tables' fields that you
paste text into. In the On Click event property enter "=PasteIt2()" without
the quotes. Enter the field size in its Tag property. Repeat for any other
fields. Save and test. Copy something from somewhere else and click in one
of the fields.

That should do it.

Clifford Bass
 
Hi Clifford

OK I'll give it a go. Thanks again for your help and expertise.



Best Wishes


Colin
 
Back
Top