inserting text at mouse position

[Gina]

Hi

I am trying to add some convenient additionals to my app.
On dblCklick on a certain text field some specific info gets added to my
memo field (description)

for now I have the descrition just added at the end....description =
description & " " & text2Add

think it would be a good idea to have it inserted where the last mouse click
on that field occurred !
on the MouseUp event I catch the x and y position and assign it to a form
variable.
but now I am lost, what do I have to do with the existing description text
to insert the new one ??

I would be very grateful for your help !!

Thanks,
Gina

 

[Ron Weiner]

Gina

This is easier than you might think. Basically all you need to do is to
keep track of the text box SelStart and SelLen properties, and make sure
that the text box has the focus before actually inserting the text.
Assuming that the Event the causes the text to be pasted happens when the
text box DOES NOT HAVE the focus you will need to:

Define a couple module level variables to remember the position of the
cursor that last time the text box had the focus. So at the top of the form
just under Option Explicit add:
Dim mlngSelStart As Long, mlngSelLen

Then in the lost focus event of the text box you can add:
mlngSelStart = YourText.SelStart
mlngSelLen = YourText.SelLength

Lastly in the event the Pastes the text to the position where the cursor is
add:
YourText.SetFocus
YourText.SelStart = mlngSelStart
YourText.SelLength = mlngSelLen
YourText.SelText = "Text to paste at the cursor"

If the event that causes the paste happens while the text box has the focus
all you need to do is to:
YourText.SelText = "Text to paste at the cursor"

Good luck with your project

Ron W
www.WorksRite.com

 

[Gina]

Hi Ron.

hey ...
it works super !!!!

thanks so much ....
I think now with your code that insert text at mouse pos .... now it's far
more 'professional' than before )

Very Happy Gina

 

[Gina]

Ron,
it works fine with the text coming from a sql dao.recordset quering some
stuff in a subform
everything gets added where curor last was set

in order to save some typing for the user some predefined texts can be
chosen from a cboText as well

so I intended to add your nice code to the AfterUpdate event of the cboText

Me.Descrition.SetFocus
Me.Descrition.SelStart = mlngSelStart
Me.Descrition.SelLength = mlngSelLen
*** Me.Descrition.SelText = Me.cboText

I get runtime error 2115 (in German -> free translation: the function or
macro in charge for 'BeforeUpdate' or a Validation rule stops this field
from being updated <--
fairly strange as the original thing doesn't trigger an error
Me.Descrition = Me.Descrition & vbcrlf & cboText

when using the new code the strange thing is that the cboText.Text visually
gets added to field Me.Descrition but at line *** the above error occurs

the cboText gets filled by a query which only shows the descriptiontext

well .... where am I going wrong here ??
maybe you have an idea ??!!!

Gina

 

[Ron Weiner]

In the past I have had problems pasting in long hunks of text like this, but
if the text referred to by the combo is less than 2K I do not see what might
be causing the error. If the text you are pasting is > 2K then you will
have to paste it in 2K chunks. Here is some code I use to paste in chunks:

Do
lngPasteLen = 2048 ' Default Paste text Length
If lngPhraseStart + 2048 < Len(strPhrase) Then
' This text is long enough to require multiple paste's
So...
If Asc(Mid(strPhrase, lngPhraseStart + 2047, 1)) = 13 Then
' Dont allow paste between a CRLF - Shorten up pasted text
By 1 char
lngPasteLen = 2047
End If
End If
' Paste this chunk of text
gobjPhrase.SelText = Mid(strPhrase, lngPhraseStart, lngPasteLen)
' Move the Start Point for pasting the next chunk of text
lngPhraseStart = lngPhraseStart + lngPasteLen
' Reduce the number of characters that are left to be pasted
lngPhraseLen = lngPhraseLen - lngPasteLen
' Move Insertion point of next chunk of text to be pasted
' just past the end of the chunk we just did
gobjPhrase.SelStart = glngPPSelstart + lngPhraseStart - 1
Loop While lngPhraseLen > 0

Let us know if this was the problem.

Ron W

 

[Gina]

Thanks Ron,

I can see what you mean ....
but the text I would like to be inserted is lets say no more than 6 to 7
words long

even if I put

Me.Descrition.SetFocus
Me.Descrition.SelStart = mlngSelStart
Me.Descrition.SelLength = mlngSelLen
*** Me.Descrition.SelText = "testing text"

I get the same error
?????
Gina

 

[Ron Weiner]

The only other things I can think of are:

- What are the values in mlngSelStart and mlngSelLen just prior to the
Me.Description.SelText = "testing text" line?
- Do you have any code in the BeforeUpdate event of the check box or any
Validation rules for Description?
- Can you post all of the code in the AfterUpdate event of the Combo box?

Frankly other than the couple things to check above, I do not see a problem.

Ron W
www.WorksRite.com

 

[Gina]

Thanks Ron ....

here my code (sorry about the german bits - hope I got all translated)
I set up module variables for mlngSelStart and mlngSelLen

they are set and get via public functions on the Description_LostFocus the
and Description_Exit event
(everything fine)
stepping throught the code the values for those variables are fine and
different depending where I left the field in question
the strange thing is that the cboText really gets inserted at the point it
should (I can see it when stepping through!!) but at the same time I get
this run-time error 2115 ...

***

Private Sub cboText_AfterUpdate()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rst As Object

strSQL = "SELECT COUNT(tblWorkText.Description) as Counted FROM
tblWorkText WHERE tblWorkText.Description = '" & Me.cboText & "' "
Set rs = CurrentDb.OpenRecordset(strSQL)

If AccessRights.fGetUserLevel > 0 Then 'at UserLevel 0 no writing
or editing is allowed I am in at level 2
If rs("Counted") = 0 Then
Call cboText_NotInList(cboText.Value, 1)
Else
If Not IsNull(Me.cboText) Then
If IsNull(Me.Description) Then
Me.Description = Me.cboText
ElseIf Me.Description = "" Then
Me.Description = Me.cboText
Else
Me.Description.SetFocus
Me.Description.SelStart = ErrorChecking.GetmlngSelStart
Me.Description.SelLength = ErrorChecking.GetmlngSelLen
' ** this works
'Me.Description.SelText = cboText.Value
' ** this not so commented out (

Me.Description= Me.Description& vbCrLf & Me.cboText
End If
End If
End If

Me.Description.SetFocus
Me.Description.SelStart = Len(Me.Description)
End If

End Sub
******
Private Sub cboText_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim rs As DAO.Recordset
Dim i, answer As Integer

strSQL = "SELECT COUNT(tblWorkText.Description) as COUNTER FROM
tblWorkText WHERE tblWorkText.Description = '" & NewData & "' "
Set rs = CurrentDb.OpenRecordset(strSQL)

i = rs("COUNTER")
If AccessRights.fGetUserLevel > 0 Then
If i = 0 Then
answer = MsgBox("New Text ' " & cboText & " ' add to list?",
vbYesNo, "List extend")
If answer = vbYes Then
strSQL = "INSERT INTO tblWorkText(Description) VALUES ( '" &
NewData & "' )"
DoCmd.RunSQL (strSQL)
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
cboText.Undo
End If

cboText.Requery

End Sub
******


***

 

[Gina]

Ron.. well I can't get some piece and quiet ;-)
so found a way around (not really ok but working in tests - doesn't give the
2115 error )
....

lngStart = ErrorChecking.GetmlngSelStart
lngLen = ErrorChecking.GetmlngSelLen

leftPart = Left(Me.Description, lngStart)
rightPart = Mid(Me.Description, lngStart + 1,
Len(Me.Description))

Me.Description.SetFocus
Me.Description.SelStart = lngStart
Me.Description.SelLength = lngLen
' Me.Description.SelText = cboText ** still not
working !!!
If lngStart = 0 Then
Me.Description= cboText.Value & " " & rightPart
Else
Me.Description= leftPart & " " & cboText.Value &
" " & rightPart
End If

......

Can't understand what is going on and where 'something' might go wrong

Gina

 

[Ron Weiner]

Gina

I can not see anything in the code that you sent that look problematic other
than
Me.Description.SelStart = ErrorChecking.GetmlngSelStart
Me.Description.SelLength = ErrorChecking.GetmlngSelLen

I do not know what is in ErrorChecking.GetmlngSelStart or
ErrorChecking.GetmlngSelLen. But even when I substituted weird values for
mlngSelStart and mlngSelLen I could not get it to generate a 2115 error.

I created a simple form with a Text Box (txtTest) bound to a Memo Field, and
a command button (cmdPaste). Here is all of the code in the form.

Option Compare Database
Option Explicit
Dim mlngSelStart As Long, mlngSelLen As Long

Private Sub cmdPaste_Click()
txtTest.SetFocus
txtTest.SelStart = mlngSelStart
txtTest.SelLength = mlngSelLen
txtTest.SelText = "Test Text to Insert"
Me.Dirty = False ' Force the record to be saved
txtTest.SelStart = mlngSelStart
txtTest.SelLength = Len("Test Text to Insert")
End Sub

Private Sub txtTest_LostFocus()
mlngSelStart = txtTest.SelStart
mlngSelLen = txtTest.SelLength
End Sub

This simplistic code seems to work every time.

I wonder if there is might be a corruption problem with the form in your
database. I recommend that you make a ***COPY*** of your database then,
Decompile / Compact and Repair and Recompile. See if this makes the problem
"Go Away". Look here http://www.mvps.org/access/bugs/bugs0008.htm for
important information about Access and Decompile.

Ron W
www.WorksRite.com

 

[Gina]

Ron.

maybe I should mention I work with access 2k - but I don't know anything
else apart from a BUG ... iiiiii ...
setting a halt on each of the evnets they get called and values assigned
Thans so much for your time and good advice ....
well how do I decomile the code ??

for completion here's the rest of relevant code:

Form :
***
Private Sub Description_Exit(Cancel As Integer)
ErrorChecking.SetmlngSelStart (Description.SelStart)
ErrorChecking.SetmlngSelLen (Description.SelLength)
End Sub

Private Sub Description_LostFocus()
ErrorChecking.SetmlngSelStart (Description.SelStart)
ErrorChecking.SetmlngSelLen (Description.SelLength)
End Sub
***

In Module ErrorChecking :

***
Option Compare Database
Option Explicit
Dim mlngSelStart, mlngSelLen As Long

Public Function GetmlngSelStart()
GetmlngSelStart = mlngSelStart
End Function

Public Function GetmlngSelLen()
GetmlngSelLen = mlngSelLen
End Function

Public Sub SetmlngSelStart(val As Long)
mlngSelStart = val
End Sub

Public Function SetmlngSelLen(val As Long)
mlngSelLen = val
End Function

****

Gina

 

[Gina]

Hi Ron.

probably you can guess and I don't have to mention that I feel extremely
frustrated about that access 2k and do not know what else to do

I was at the page you suggested ... did a decompile / compact & repair /
compile - still the same
well, yes I do have various copies ... BUT ... as I didn't know something
was corrupted they will be corrupted too !!!!

There are some early versions but after weeks and weeks of coding I don't
think they will be of help, if you know what I mean.
In any cases I have a back-up of the data in form of text so at least this
is safe....

tried to run it on access 2002 :-((

what I found out is that one of the subforms' link ( the one where the
cboText and the Description field is in) to the parent doesn't come up
properly anymore and I get the error message 'Object variable or With-Block
not defined' or similar in English

even trying to recreate the form from scratch (using the same query as the
old form) does produce the same linking problem....

so I had to type in the parent and child links....

well the app works and customer can type their invoices with it for now ....

.... this hurts deep down inside
Do you have any idea what I could do in order to not loose my work ??? -
Please

Gina

 

[Ron Weiner]

Gina

I whish I had a good answer for you. I do all of my Access development work
in Access 2K and then test in 02 and 03 before distribution to be sure that
I am only using the lowest common denominator set of tools and
feature/function. I do not believe that the problem you are having is
specifically related to Access 2K.

If it were me at this point the first thing I would try to do is to create a
new clean database, and import all of the objects from the current database
except the forms that are giving you problems. I would manually recreate
just those forms, and then test.

While I am doing any significant Access development I consciously try to
back up the database every couple hours just in case I have a problem like
this. I have been burned before:-(

Ron W

 

[Gina]

Ron,

thanks for your support and giving me 'some ' hope I do not have to throw
away everything .....

well ... some good news after all that bad stuff ...
in the current db ( the one where the cboText.Value should be added to the
Description field ....)

i've created a test button ... on clicking it inserta the text currently
visible in the cboText without an error to the description field ....

so what does that mean now .... re my cboText ComboBox ??? I call the
me.Description.SelText in the cboText_AfterUpdate event ....

I am sorry to bother you again and again with my problems but I am very
alone here with all that corrupted stuff

Gina

 

[Ron Weiner]

Gina

I am getting kinda' lost on what you are doing on your form now. If you
don't mind sending me a sample of your database I will be happy to look at
it and see if I can determine what is giving you problems. If you would
like to have me take a look at it please do the following:

Create a new Access 2K database and place in it ONLY the forms, tables,
queries, sample data, etc. that are necessary to duplicate the problem.
Even though I do not understand German you should not have to translate to
English. Hopefully I will get the gist of what the code is doing and should
be able to replicate and hopefully fix the problem. Please include ONLY
that which is necessary to demonstrate the problem, then zip it to a file
less than 500K and Email to me at rweinerATworksriteDOTcom making the
obvious changes to the email address.

Ron W
www.WorksRite.com

 

[Gina]

Ron,

hey ... thanks so much ....
but before I' cause even more work for you .... have a look belowp plz
what I've done is added a button:

Private Sub cmdTest_Click()

Me.Description.SetFocus
Me.Description.SelStart = ErrorChecking.GetmlngSelStart
Me.Description.SelLength = ErrorChecking.GetmlngSelLen
Me.Description.SelText = cboText
Me.Dirty = False
End Sub

after disabling the old code in the cboText_AfterUpdate event .. I call
cmdTest_Click within the cboText_AfterUpdate and it gives the same error

if I now select anything from the cboText and click the button manually ...
fine text is added where cursor left Description field ?

In the past a very nice and helpful guy offered his help like you 've just
done ....
he worked with a US version and couldn't even open my app .... the queries
in the german version use german expressions like 'Nicht' instead of 'Not'
the date is german style .... and this is just coming of the top of my
head....
in addition some german alphabet is used with some strange letters ..... in
the tables, queries ... etc

I'm sure you work on a normal international version!!!

I wouldn't want to only waste your time any further ..... so what do you
think after the above additional (german) info ?? re sending a zip file?

Gina

by the way the error: 'object variable or with-block vatriable not defined'
is solved (posted in access.forms earlier today)

 

[Marshall Barton]

maybe I should mention I work with access 2k - but I don't know anything
else apart from a BUG ... iiiiii ...
setting a halt on each of the evnets they get called and values assigned
Thans so much for your time and good advice ....
well how do I decomile the code ??

for completion here's the rest of relevant code: []
In Module ErrorChecking :
Dim mlngSelStart, mlngSelLen As Long

[]


Gina, I don't really have anything to add to Ron's excellent
suggestions, but I did notice the above Dim statement might
cause problems under some odd circumstances. I think it
should be:

Dim mlngSelStart As Long, mlngSelLen As Long

I don't use A2k, so this may be irrelevant, but make sure
that you have turned off the NameAutoCorrect feature.

If corruption has crept into your app, you may want to look
at:
http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.granite.ab.ca/access/decompile.htm

 

[Gina]

Thanks Marsh,

I will gratefully look at all suggested aspects ....
wel I didn't know that the dim could be a reason ...

Gina

maybe I should mention I work with access 2k - but I don't know anything
else apart from a BUG ... iiiiii ...
setting a halt on each of the evnets they get called and values assigned
Thans so much for your time and good advice ....
well how do I decomile the code ??

for completion here's the rest of relevant code: []
In Module ErrorChecking :
Dim mlngSelStart, mlngSelLen As Long

[]


Gina, I don't really have anything to add to Ron's excellent
suggestions, but I did notice the above Dim statement might
cause problems under some odd circumstances. I think it
should be:

Dim mlngSelStart As Long, mlngSelLen As Long

I don't use A2k, so this may be irrelevant, but make sure
that you have turned off the NameAutoCorrect feature.

If corruption has crept into your app, you may want to look
at:
http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.granite.ab.ca/access/decompile.htm

 

[Ron Weiner]

Gina

Well this has been fun, NOT! :-(

This is either a bug in Access 2K or a lack of understanding on my part. I
invite others to have a look at this. Here are instructions to recreate the
problem. Create a new form bound to a table that has a memo field. Add a
text box (its name will default to Text0) and bind it to the memo field in
the table. Add a Combo box (its name will default to Combo2) that is
unbound and has a row source like "Select Foo from Bar". Just one column
nothing fancy!

Add the following code:

Option Compare Database
Option Explicit
Dim mlngSelStart As Long, mlngSelLen As Long

Private Sub Combo2_AfterUpdate()
Text0.SetFocus
Text0.SelStart = mlngSelStart
Text0.SelLength = mlngSelLen
Text0.SelText = Combo2
End Sub

Private Sub Text0_LostFocus()
mlngSelStart = Text0.SelStart
mlngSelLen = Text0.SelLength
End Sub

Select an item from the Combo -- KA-Boom 2115 error "The macro or function
set to the Before Update or Validation rule property for the field is
preventing Microsoft Access form saving the data in the field."

The cool part of all this is that the Data has already been inserted into
the text box! So the work around is to add an error handler that will
ignore the error (On Error Resume Next) to the sub just before the line
(Text0.SelText = Combo2) that creates the error. If you had an existing
error handler in the sub you could add some code to the error handler like:

if err.number = 2115 then
resume next
else
' what ever the error handler is currently doing here
endif

Soooo..... Gina the specific answer to your dilemma is:

Me.txtArbeitsBeschreibung.SetFocus
Me.txtArbeitsBeschreibung.SelStart = ErrorChecking.GetmlngSelStart
Me.txtArbeitsBeschreibung.SelLength = ErrorChecking.GetmlngSelLen
On Error Resume Next
Me.txtArbeitsBeschreibung.SelText = cboText ' ******
On Error GoTo 0

However I have discovered that this error only happens with the AfterUpdate
and Click events of a Combo box. If you move the code to the Combo box
DoubleClick event there is NO ERROR generated. I do not know why.

Perhaps one the Msoft MVP's will take a whack at this. But for right now it
looks like a bug to me. I am using Access 2K 9.0.6926 SP-3

Ron W
www.WorksRite.com

 

[Gina]

Ron,

I thank you so so much for having a deeper look at my code ...

the on error resume next ... its nice to have it and microsoft obviously
know why they included it ;-))
I am using the same version of Access 2K : 9.0.6926 SP-3

in a way I am 'calmed down' because it's not my own unableness that was the
reason for the failure of the cboBox stuff
.....
Big Thanks!! for all your advice and your hands-on help

Gina