Solved: Managing Text Fields

[Imdabaum]

I have a main form that has a memo field box. In order to add notes to the memo the user clicks "Add Note" and opens a second form which holds a memo field for the individual note. This is a protection from the people I work for since they have a habit of deleting years of project notes by not paying attention to where the focus of their form is.

So the question is currently I use a
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT ProjectID, ProjectNumber, NoteDate & ': ' & entryType & _
'; ' & RER & ' - ' & Memo AS BigMemo " & _
"From tblProjectUpdates " & _
"WHERE (((tblProjectUpdates.ProjectID) =" & ProjectID & ")) " & _
"ORDER BY NoteDate DESC;"

Set qdf = dbs.CreateQueryDef("qryUpdateMemo", strSQL)
'Set qdf = dbs.OpenRecordset(strSQL)
Dim strMemo As String
Dim records As Recordset
Set records = qdf.OpenRecordset

Do Until records.EOF
strMemo = strMemo & records.Fields("BigMemo").Value & vbNewLine & vbNewLine
records.MoveNext
Loop
End If
BuildMemoField = strMemo
dbs.QueryDefs.Delete "qryUpdateMemo"
strSQL = "SELECT * From tblProperties WHERE tblProperties.ID = " & ProjectID
Set qdf = dbs.CreateQueryDef("qryUpdatePropMemo", strSQL)
Set records = qdf.OpenRecordset
records.Edit
records.Fields("Memo").Value = strMemo
records.Update
dbs.QueryDefs.Delete "qryUpdatePropMemo"
Set qdf = Nothing
Set dbs = Nothing
Set records = Nothing

If strMemo > 255 all other updates fail to get added in the display. Any suggestions on how to handle the String object limit of 255 characters? ultimately each individual note could possiblity be up to 1500 characters. Because each note is stored individually, I can't just do mainform.Memofield= newForm.Memofield I need another way to update it.

[geekgirlau]

You could create a loop capturing 255 characters at a time, however I would approach it a bit differently.

Have your notes in a separate table, linked to the project by Project ID (if this is your key). The form to add a new note does just that - creates a new record in the Project Note table for the relevant project. A history of notes is displayed in a subform sorted in descending order by date, and set so that data can neither be deleted nor edited.

[OBP]

Why use all that VBA for a memo field? If you don't want to go down geekgirlau's route why not use a blank memo field on an form bound to a temprary data table, which does not have any 255 character restrictions. In the memo field's "after update" event procedure open the "actual" table's record set and concatenate the two fields.

[Imdabaum]

This is how I have it set up. tblProjects, and tblProjectUpdates. tblProjects has a Field Memo. tblProjectUpdates keeps track of individual notes on the projects. This is handled in newFormProjectNotes. I have tried cycling through the notes; storing each note in a string variable that is returned by a function BUILDMEMOField. If the string variable is stored with any more than 255 chars it breaks the function. I updated it yesteday trying to get around the 255 char limit by creating a select case based on the length and how many variables I would need to handle the larger texts. I added this inside the loop. but the length function stops updating when Len(string)>255. Also half2 gets corrupted characters even though it is a new variable everything after the 255 location is truncated.

Dim length As Integer
Do Until records.EOF
length = Len(records.Fields("BigMemo").Value)
Select Case length
Case 1 To 255
strMemo = strMemo & records.Fields("BigMemo").Value & vbNewLine & vbNewLine
records.MoveNext

Case 255 To 510
MsgBox length, vbInformation, "Note Length exceeds 255"
Dim half1 As String
Dim half2 As String
half1 = Left(records.Fields("BigMemo").Value, 255)
half2 = Mid(records.Fields("BigMemo").Value, 256)
strMemo = half1 & half2
records.MoveNext
End Select
Loop

[OBP]

I am obviously missing something here.
Please have a look at the attached database, it has a table and form.
Each has 3 memo fields, each memo contains a lot of characters (I didn't count them). On the form when you update and move out of field memo1, (2nd field), the fields "After update" event procedure adds the memo and memo 1 fields to what is already in the memo 2 field. Bearing in mind that memo 2 could be on aother form or in another table.
Is this what you want your memo field to do?
ie collect together data of more than 255 Chrs in to one memo field

[Imdabaum]

Why use all that VBA for a memo field? If you don't want to go down geekgirlau's route why not use a blank memo field on an form bound to a temprary data table, which does not have any 255 character restrictions. In the memo field's "after update" event procedure open the "actual" table's record set and concatenate the two fields.

If I did it that way, how would you update the memo field after editing a single note? If I just did
mainform.Memo= mainform.Memo & newForm.Memo then it would simply keep the updated notes in the same field as the old notes wouldn't it? The idea is that the people I work for want all the notes kept individually, but displayed together on the main field... If I can do this and do it your way let me know how you intend to do it because I don't really understand what you are proposing.

[OBP]

Imdabaum, I only created that to show that when you concatenate fields you do not need to use strings, which is what is causing your problem.
You can still use your loop to add the "current data" to the memo field as you have been tryin to do. To stop it adding new data to old data, reset the memo field to "" before cycling through the data.
Or do you mean that you have to compare the latest data with what is already in the memo field?

[OBP]

Imdabaum, on second toughts, maybe it isn't such a bad idea to just keep adding the data to the Memo field. If you preface each new entry with CHR$(13) & Date() & CHR(13)
you would have a memo field that was "anotated" with the date any changes/aditions were made and sill preserve the original data.
If it is a secure database you could even include the user that changed/added the data

I still personally prefer geekgirlau's related table for recording all of the individual memos on a subfrom or similar.

[Imdabaum]

Imdabaum, I only created that to show that when you concatenate fields you do not need to use strings, which is what is causing your problem.
You can still use your loop to add the "current data" to the memo field as you have been tryin to do. To stop it adding new data to old data, reset the memo field to "" before cycling through the data.
Or do you mean that you have to compare the latest data with what is already in the memo field?

I don't need to compare the data. The memo field on the main form just has to be populated from a concatination of the individual notes. But you have given me an idea. If I made an invisible memo that stored each something like this in the cycling process...

invisMemo= invisMemo & then cycle through Memo values.

Thanks OPB, I'll get back to you and see how it works.

[Imdabaum]

Do Until records.EOF
Me.memoStore = Me.memoStore & records.Fields("BigMemo").Value & vbNewLine & vbNewLine
records.MoveNext
Loop
[Forms]![frmPropertiesNew]![Memo] = Me.memoStore

This didn't work. I think it might be something in the records.Fields("BigMemo").Value operation. Because in all actuality Variable string should be able to hold ~ 6 billion chars.

[OBP]

I don't suppose you can attach a copy of te database?
It is very difficult to visualize what you are doing.
Why have you used

records.Fields("BigMemo").Value

What is the .value for?

[Imdabaum]

I don't suppose you can attach a copy of te database?
It is very difficult to visualize what you are doing.
Why have you used

records.Fields("BigMemo").Value

What is the .value for?

.Value retrieves the value inside recordset.Fields("BigMemo")

[Imdabaum]

I have been doing some more research on this issue. I found a post about a 255 limit when retrieving information from a field. I don't know if this is related, but I thought I would post it. See if anyone can enlighten me.

http://www.codecomments.com/archive352-2005-7-538617.html

I think it is talking about a specific ADO object. I don't think mine is an ADO, but just a standard object with MDB. Although I could be just revealing to everyone that I really am just an intern.lol

[OBP]

I am sure it doesn't apply because my simple database worked fine with more than 255 characters.
Any luck on reducing the size if the database. Can't you just delete all but one or two records.

[Imdabaum]

I think I might have figured out the problem. I used a function to determine the Data Type stored in the BigMemo field. It shows up as a text field instead of a memo field. So despite saving it completely in the actual Field!Notes...which is a Memo type the query stores all this data in a Field!BigMemo which is a Text type which has a max of 255 characters right? Or am I incorrect in the size of Table fields of text type? So I guess the real question is can I set the property of a field inside a query?

[OBP]

You are right the 255 limit is on text fields.
I have you managed to overcome it now?

[Imdabaum]

Not yet... still trying to figure out how to set the property of the field in my query.

strMemo= "SELECT PropertyID, PropertyNumber, NoteDate & ': ' & entryType & '; ' & RER & ' - ' & Memo AS BigMemo<--as Memo field? cont.> " & _
"From tblPropertiesUpdates " & _
"WHERE (((tblPropertiesUpdates.PropertyID) =" & PropertyID & ")) " & _
"ORDER BY NoteDate DESC;"

[OBP]

Why do you need to set the field property, can't you just use an existing table field?

[Imdabaum]

Just figured it out. If I thought it would be nice to make the one big field... BigMemo... Bigger is better right! Not always. It turns out that if I just pull each thing out and concatenate it in the string strMemo instead of pulling it out in the strSQL, then it maintains all the properties of the table I pull it from. Memos are still memos. Now I just save the data from each field individually.

strSQL = "SELECT PropertyID, PropertyNumber, NoteDate, entryType, RER, Memo " & _
"From tblPropertiesUpdates " & _
"WHERE (((tblPropertiesUpdates.PropertyID) =" & PropertyID & ")) " & _
"ORDER BY NoteDate DESC;"
strMemo= strMemo & records.Fields("NoteDate")
strMemo= strMemo & records.Fields("entryType")
strMemo= strMemo & records.Fields("RER")
strMemo= strMemo & records.Fields("Memo") & vbNewline & vbNewline

[OBP]

I think that is roughly what I suggested in post #5 LOL.:thumb:beerchug:

[Imdabaum]

Sorry somehow I missed post 5... at least when I saw it, it didn't have an attachment. But I appreciate all your help folks.