Code help please

[cbearden]

I have 2 controls on my form that are for comments.

txtComments
txtComments2

My comment section was a memo datatype but I changed it to text. On my reports, my comment box(when it was memo) cuts off at 255, so I'm going to make 2 comment sections.

I'm trying for this: When the txtComments control reaches 255 characters, it will automatically switch to the next comments section. also, how would I solve this problem:
Comments: prob Comments2: lem.
The b is at teh 255 mark and cuts the word in half.

Thanks in advance!

 

[Remou]

Hi
What code have you at the moment? Can you check backways for a space with InStrRev?

 

[cbearden]

I have no code yet. I didn't know how I could specify the amount of characters for the control.

 

[Remou]

I still don't really understand why you want to do this, because usually memo fields work fine.

This is a rough idea of what you said:

Private Sub Note1_Change()
Dim intBreak, intSpace, intCrLf, strTemp

If Len(Trim(Me!Note1.Text)) > 252 Then
    MsgBox "Limit reached, moving to next comment."
    
    strTemp = Me!Note1.Text
    intSpace = InStrRev(strTemp, " ")
    intCrLf = InStrRev(strTemp, vbCrLf)
    intBreak = IIf(intSpace > intCrLf, intSpace, intCrLf)
    
    Me!Note1.Text = Mid(strTemp, 1, intBreak)
    If Mid(strTemp, intBreak) > 0 Then
        Me!Note2 = Mid(strTemp, intBreak)
    End If
    
    Me!Note2.SetFocus
    Me!Note2.SelStart = Len(Me.Note2.Text)
End If
End Sub

 

[cbearden]

I did have a memo field...but on my report it cut off the information at 255 characters. So it was pointless to have a memo datatype when it treats it like a textbox. If there is a way around that, please let me know.

Thanks

 

[Remou]

This does not happen to me (Access 2000). Which version are you using? Could it be something else? Is it worth setting up a temp table with just a memo field filled with junk and trying the report wizard on it to see what happens?

 

[cbearden]

I am using Access 2000.

I'll try creating a table.

 

[cbearden]

I created a table and then created a report from that table. It cuts it off at 255.

 

[Remou]

Can you check that the Can Grow property is set to yes?

 

[cbearden]

yes it is. I do have a question though. I have it set in my query to "First". I tried to do Group By but only square boxes show up(and its not the border, its a small square box).

 

[Remou]

I am not quite sure what you are saying here:
I have it set in my query to "First". I tried to do Group By but only square boxes show up(and its not the border, its a small square box).
What do you mean by set to "First"? Also, do you mean group by the memo field? What are these boxes? Are they in your report or the query?

Here is a quote:

> It only truncates the memo field if you:
>
> - sort on the field
> - group by the field in a Totals query
> - specify a Format on the field

I believe it also truncates if you use SELECT DISTINCT, or UNION.

UNION ALL will solve the problem for the last case.

http://www.access-database-help.com/top/ng/group~5/~51603~__memo-versus-text-field/index.aspx

 

[cbearden]

They are in both my query and report. The report is based on the query. "Group By" and "First" are in the Total line in the query.

I will look at the stuff you provided. Thanks

 

[keavenmj]

I did have a memo field...but on my report it cut off the information at 255 characters. So it was pointless to have a memo datatype when it treats it like a textbox. If there is a way around that, please let me know.

In your query for the report, set the MEMO field to be First. If it is set to any kind of evaluation (such as Group By or Max) it treats it as a TEXT field and truncates it at 255 characters. If you use First it will take the whole field.

 

[cbearden]

I used First in my query but it still cut off at 255.

The problem was I had it formatted to "None" in the report when it was blank. That is why it was cutting it off.

Thanks for the help!

 

[TheAceMan1]

How are ya cbearden . . .

[blue]I still don't really understand why you want to do this, because [purple]usually memo fields work fine.[/purple][/blue]

I could'nt agree more.

First, a memo field will hold [blue]65K characters[/blue]. You are apparently having display problems.

For forms, you have to [blue]size the memo for the number of lines you want to see[/blue] (within reason of course) and the following properties should be set:
[ol][li]Scroll Bars [blue]Verticle[/blue][/li]
[li]Enter Key Behavior [blue]New Line In Field[/blue][/li][/ol]
Entering shold be no problem. Its the size you set the memo to in the form thats the only limitation. If text occupies more lines than show, you either have to use the scroll bars or make the memo bigger. This is the best you can do with memo in forms.

Reports on the other hand handle memo's [blue]magnificantly.[/blue] All you have to do is set the [purple]CanGrow/CanShrink properties to Yes[/purple], and [blue]the report resizes to show the full text for each record[/blue]. If you can't do this . . . then you have a problem!

Test it in a new report with just the momo and see!

See Ya! . . . . . .

 

[cbearden]

Everything is working fine. I had to leave the format blank. I had it showing "None" when the memo field was null. I deleted that and now it shows full text. Problem is solved.

Thanks for info!