Compatibility issue between Access 2003 and Access 2007?

[Al Oberneder]

I wrote VBA code that copies text that I have highlighted within a field (called "Description) in one form (Form A) and pastes it into a field by the same name but in a different form (Form B). When pasting it, it also adds a date stamp.

The VBA code I wrote is as follows:

'Declare global variable to hold the text I want to copy/paste.
Global GloMtgText As String

'Copy selected text from the Description field in Form A. (This code is attached to the Lost Focus event).
GloMtgText = "(" & Me.DateCompleted & ") " & Me.Description.SelText

'Append the text into the Description field in Form B. (This code is attached to the On Click event).
Me.Description = GloMtgText & Me.Description

This code works just fine in MS Access 2003. I can copy a rather large string from one field to the other. However, in MS Access 2007 the pasted text is truncated at about 155 characters. I am baffled! Can anyone help me? Is there a string limitation in MS Access 2007 or is there a compatibility issue between Access 2003 and 2007?

Thank you very much for your help!

 

[Mr. B]

Have you varified that your "GloMtgText" variable actually contains more than the 155 or so characters? The reason I ask is that we can start to identify where the issue is occuring. If the variable has the expected value then we can look at other things. I assume that the Me.Description control is bound to a field in a table and if so we would then look to see what the length limit of that field is. If necessary, you may have to change your field to a memo type field so it will hold everything you want to copy to it.

Just my thoughts.

 

[Al Oberneder]

Mr. B,

Good question. I forgot to mention that piece of information. Yes, the copy operation is doing the truncating; not the paste operation. I set a breakpoint and examined the GloMtgText variable immediately after selecting the text and it only contains about 155 characters. It works perfectly in MS Access 2003 but Access 2007 seems to be behaving differently. I would greatly appreciate any further help/advice that you can give me.

Thank you.
-Al

 

[Mr. B]

Well, I did some testing here and I have not been able to reproduce your problem. I created a new form in Access 2002 (I don't have 2003 to test with). I created two text boxes and a command button. I pasted some text that I created in MS Word with 222 words and spaces into the first of my text boxes then I used the following code in the On Click event of my command button to place the same 222 characters into the second text box:

Private Sub Command2_Click()
strSpecTest = Me.txtTestBox
Me.txtTest2 = strSpecTest
End Sub

The global variable was defined in a module:
Global strSpecTest As String

Everything went exactly as designed in Access 2002.

I then opened the file in Access 2007 and tried the same action and it all worked exactly as expected. There was no difference.

Can you post your database so I can take a look at it?

 

[Al Oberneder]

Mr. B,

Thank you for your fast response. I appreciate it.

Unfortunately, I am not at liberty to post a copy of the database.

I have VBA code in another portion of my database that is pretty much identical to the sample code that you provided and it works just fine in Access 2003 and 2007 so I'm not surprised that your sample code didn't present any compatibility problems when you tried executing it. Copying from one field of a form to a global variable and pasting to another field in another form seems to not be the issue. I think the issue is with the ".SelText" attribute. That seems to be behaving differently between Access 2003 and 2007. Can you try using the .SelText attribute in your code?

Again, thank you for your help. I do very much appreciate it.
-Al

 

[Mr. B]

Well, it is definately the SelText method. I do not have any idea why, but when I tested this method in 2002 it worked fine but in 2007 it did not. I was only able to get it to copy 128 characters of the 160 or so that I had selected when I tabed out of the control. I was using the Lost Focus event.

Unfortunately, I don't have any solution at this time. I will consider it and see what we might come up with. This is an interesting issue.

Perhaps someone else might have something to add.

 

[Al Oberneder]

Mr. B,

Thank you very much for trying this out and confirming the root cause of this issue. I certainly appreciate it! My guess is that this is a very serious bug in the 2007 release of MS Access. If you ever come across a work-around to this issue, I would greatly appreciate it if you would post it to this thread. I will do the same.

Again, thank you very much for the time that you spent helping me. It is much appreciated!

 

[gemma-the-husky]

is A2007 being used to open the same database, or a different one?

if its a different one, i would check the field setting for the field, and double-check the size of the text field

also try and edit the data manually in the table - see if that lets you add extra text to a "full" entry.

============
you mention 155 chars - that seems a strange value - you could understand 255, as this is the max size of a text field. to go longer you need to define the field as a memo type. i would check the field sizes first.


============
on rereading the posts, you seem to have pinned this down to the seltext method/property, so my comments are probably not too germane

I would therefore be inclined to change this

GloMtgText = "(" & Me.DateCompleted & ") " & Me.Description.SelText

to

dim tmpvar as string
tmpvar = Me.Description.SelText
msgbox(tmpvar & " Length: " & len(tmpvar))

GloMtgText = "(" & Me.DateCompleted & ") " & tmpvar

to see exactly what is going on. Maybe the issue is the me itself. It may be neutral, but I dont usually add me when referring to controls on a form.

 

[vbaInet]

I wrote VBA code that copies text that I have highlighted within a field (called "Description) in one form (Form A) and pastes it into a field by the same name but in a different form (Form B). When pasting it, it also adds a date stamp.

The VBA code I wrote is as follows:

'Declare global variable to hold the text I want to copy/paste.
Global GloMtgText As String

'Copy selected text from the Description field in Form A. (This code is attached to the Lost Focus event).
GloMtgText = "(" & Me.DateCompleted & ") " & Me.Description.SelText

'Append the text into the Description field in Form B. (This code is attached to the On Click event).
Me.Description = GloMtgText & Me.Description

This code works just fine in MS Access 2003. I can copy a rather large string from one field to the other. However, in MS Access 2007 the pasted text is truncated at about 155 characters. I am baffled! Can anyone help me? Is there a string limitation in MS Access 2007 or is there a compatibility issue between Access 2003 and 2007?

Thank you very much for your help!

Interesting situation so I just had a play with it.

The SelText property requires the control to have focus for the property to return the number of selected characters (according to the help files). It seems when a control loses focuses the number of selected characters is truncated to 128 (returned by the SelText property). Coincidentally happens to be the same number as that of the number of ASCII character code definitions available.

This is where it gets interesting. The SelLength property returns the correct number of characters selected even when using the Lost Focus event. This property also requires the control to have focus. The same goes for the SelStart property.

Therefore, if the SelLength and SelStart properties both return the correct number of characters what do we do? We Mid it:

... Mid(Me.Description.Text, Me.Description.SelStart + 1,  Me.Description.SelLength)

As we all know the SelStart property is 0 based so we add a 1 to it.

This sitatuation speaks of a bug. I would have thought that the SelText property would be reliant on the SelStart and SelLength properties, but this proves not to be the case. Interestingly, the SelLength property is set by the SelStart property but has no effect on the SelText property.

Try the above.

 

[Mr. B]

Using 2007, I tried one more interesting way to check this.

I had already decided that the problem was with the .SelText property. So to try to prove this I used the followin code in the On Mouse Up event:

Debug.Print Me.txtTestBox.SelLength
Debug.Print Len(Me.txtTestBox.SelText)

A physical count of the number of characters returned was 172
Me.txtTestBox.SelLength returned the correct selected length of 172
Len(Me.txtTestBox.SelText) returned only the 128 which is the maximum number that seems to always be returned by the SelText property.

I even tried to eliminate the "Me" to see if there might be any connection, that did not chane anything.

The only work around, and it is not a really good one, is to make smaller selections, appending each selection to any existing string.

As I said, not really a good solution, but if the SelTest does not and will not return the correct selected string, then you simply cannot use this property in the same way that it could be used in previous versions.

 

[Al Oberneder]

Mr. vbaInet,

I tried your solution and it works perfectly! Thank you very much for your help. I certainly appreciate it!

Mr. B,
Thank you for all the help that you have given me as well. I very much appreciate the time that you took to try out the code and verify for me that it was indeed an issue with the .SelText attribute.

 

[vbaInet]

Mr. vbaInet,

I tried your solution and it works perfectly! Thank you very much for your help. I certainly appreciate it!

Glad that sorted your problem. You're welcome.