PDA

View Full Version : Solved: Put data in Text Box via a Variable



asingh
01-12-2006, 09:39 AM
Hi,
I want to place data onto a text box using a variable. I am being able to do it using direct value...

When I try the following: my string value does get placed in the text box


Dim TxtBox as shape

Set TxtBox = ActiveSheet.Shapes("Text Box 1")
TxtBox.Select
Selection.Characters.Text = "data to be pasted"
Set TxtBox = Nothing




Now when I try:


Dim TxtBox as shape
Dim MyVariable as string

Set TxtBox = ActiveSheet.Shapes("Text Box 1")
TxtBox.Select
Selection.Characters.Text = MyVariable
Set TxtBox = Nothing

I have a string value all ready stored in my string variable "MyVariable" , that value is not coming onto the text box. I am not getting any compile or runtime error when I step through the code..


thanks,

asingh

Bob Phillips
01-12-2006, 10:00 AM
Works fine for me with some text in MyVariable.

Norie
01-12-2006, 10:49 AM
Where are you actually giving the variable a value?

asingh
01-12-2006, 10:56 AM
i am storing a string value in the variable..

Norie
01-12-2006, 11:07 AM
Where are you doing that?

In the posted code you've declared the variable but you haven't given it any value.

Therefore within the scope of that code it's an empty string.

asingh
01-12-2006, 11:25 AM
ok...I am sorry I did not mention that...when I pasted my code here..but in my original code it looks like this..but still no data is reaching the text box...

Dim TxtBox as shape
Dim MyVariable as string

MyVariable = "Sample data to be pasted to text box"

Set TxtBox = ActiveSheet.Shapes("Text Box 1")
TxtBox.Select
Selection.Characters.Text = MyVariable
Set TxtBox = Nothing

i.e I am storing a string value in my variable..

asingh

Norie
01-12-2006, 11:37 AM
That code works perfectly for me.

asingh
01-12-2006, 12:02 PM
could you try with the following..

1. Keep the size [length only] of your text box small..around 10-12 characters long...and try to paste a string that is of around 150 words onto the text box...I am able to paste small strings..but large strings..are not working..and I need my text box to be small...length has to be small ..but the width can be long as needed....!

asingh
01-13-2006, 05:03 PM
When I try to paste a string in the text box that is equal to or greater than 256 characters , the data is not getting pasted to the text box..using the above mentioned code. If the string length is 255 then it is getting pasted fine. And this is happenig irrespective of what the text box size is....! I am totally lost here..does the text box have a character limit....? And if it does..is there any way around to get more than 256 characters into the text box...?

geekgirlau
01-13-2006, 05:52 PM
Off the top of my head, I believe 255 is the number of characters. If you need more than than you'll have to find another type of control to use. Alternatively, populate a cell in your workbook rather than a control, or have multiple controls and split the text into groups of no more than 255 characters.

Imdabaum
06-26-2006, 07:51 AM
I think I am having a similar problem. I can set the textField value, but it always seems that it will only record up to 801 characters(including spaces).
Background: There is a main form that holds Project information. On this main form is a locked Memo field. We want to store all notes individually and not as one large text block. We do want to display it as one large text block though. So from the main form you click "AddNote" button. It opens a new form then in the newForm_Close() Subroutine it calls this function.

Call BuildMemoField(Me.ProjectID)
MsgBox Me.ProjectID & ": " & Me.ProjectNumber, vbOKOnly, "ProjectInfo"
[Forms]![frmProjectsNew]![Memo] = BuildMemoField(Me.ProjectID)
DoCmd.Close
End Sub


Private Function BuildMemoField(ByVal ProjectID As String) As String
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT NoteDate & ' ' & entryType & ' ' & Memo & ' ' & AGENT as BigMemo " & _
"FROM tblProjectUpdates WHERE tblProjectsUpdates.ProjectID = Me.ProjectID " & _
"ORDER BY tblProjectUpdates.NoteDate desc;"
Set qdf = dbs.CreateQueryDef("qryUpdateMemo", strSQL)
Dim strMemo As String
Dim temp As Recordset
Set temp = qdf.OpenRecordset

Do Until temp.EOF
strMemo = strMemo & temp.Fields("BigMemo").Value & vbNewLine & vbNewLine
temp.MoveNext
Loop
MsgBox strMemo, vbInformation, "MEMO BUILDER"
BuildMemoField = strMemo
dbs.QueryDefs.Delete ("qryUpdateMemo")
Set qdf = Nothing
Set dbs = Nothing
Set temp = Nothing
End Function

This is suppose to cycle through all records with the projectID that matches the ID in the project form. It reads all Memo records for the projectID. That works. But when I try to set a textfield value to strMemo or to the function itself ie txt= BuildMemoField(projectID) it only writes the first 801 characters. Is there a maximum length to a string variable or a maximum length that a txtbox will hold? If so how should I go about getting around this?

mdmackillop
06-26-2006, 10:30 AM
Here's a wee routine to test for maximum length.

Sub testTB()
Dim TxtBox As Shape
Dim MyVariable As String
Set TxtBox = ActiveSheet.Shapes("Text Box 1")
Do
MyVariable = MyVariable & "a"
TxtBox.Select
Selection.Characters.Text = MyVariable
Loop Until Selection.Characters.Count < Len(MyVariable)
MsgBox Len(MyVariable)
Set TxtBox = Nothing
End Sub

Imdabaum
06-28-2006, 02:16 PM
Just so people don't get confused with my last post, it isn't 801 that throws the memo off it's that I am recording individual notes as strings into the Memo field. The transfer breaks down if any individual note is greater than 255 (the limit of a string character).