Passing string over 255 characters from textbox to table

This is a great resource for some of the most common causes and workarounds for memo truncation issues:
http://allenbrowne.com/ser-63.html

I'm not sure that those apply in your case, though.  What code are you using to save your data?

What is your reason for not using a bound memo field?

All I am doing is taking the user comment/note from the textbox on the unbound form, and placing it into a string variable.  I am then using an append query to insert into the appropriate table.  The data type for the comment/note is "memo".

    Dim qdNew   As DAO.QueryDef
    
    lngNotesID = GetNewNotesID
    strNotes = Forms!frm_AddNewIssue!txtNotes

    Set qdNew = DB.QueryDefs("qry_appAddNewNote")
        qdNew.Parameters![NotesID] = lngNotesID
        qdNew.Parameters![Notes] = strNotes
        qdNew.Execute

Select allOpen in new window

fyed,

I prefer to use unbound forms.  It provides much more control over when data is saved to or updated into tables.  The whole application is using unbound forms, so it is too late for me to change the design now.

Why do you ask?  Is there something characteristic about a bound memo field that is better for this type of task?

Fwiw,  the work-around I have seen to this issue is to actually use a bound memo field. The other fields don't need to be bound... just that one field, and the table can be unrelated, simply containing a memo field as a holder for the notes data.

The code used to insert the data would look something like this:

Dim strSQL as string
strSQL = "INSERT INTO YourTable (NotesID, Notes)  SELECT " & Me.NotesID & " [MemoFieldName] FROM YourMemoFieldTable"

Select allOpen in new window

So the memo field would be read from a bound field in a special table, while any other fields would be read directly from the form - so the form would be bound, but it would overall be giving you the control you like from an unbound form.

One more thing that may or may not help.  

As a test, I created a new entry with a comment with a length of 200 characters.  As expected, it saved just fine.   I then directly edited the comment in the table itself.  I increased the size of the comment to over 300 characters.

I then ran a process to bring up a different unbound form that displays the comment for an entry.  It displayed the  300 character comment without truncating it.

So it seems that the issue is centered on the process of taking text from text box and putting it into a table, and not the other way around.

mbizup,

Ok, I might have to go that route.  Currrently, I am saving all the notes into a notes table.  It only has two fields.  NoteID, and the actual note that is a memo field.  So essentially, I am already doing what you are stating about the table setup.  

However, I am still not seeing how having the bound textbox will successfully accomplish a save of over 255 characters.  You are still using a query to insert into a table with a string variable holding the note to be inserted.  Does having the bound field prevent it from truncating somehow?

I am not disagreeing with you.  I am simply ignorant of the key difference in methods.

String manipulation is among the 'common causes' of memo field truncation in Access queries.

If the data is held in a dedicated memo field and directly written from one table to the other  , you are avoiding any type of string processing/manipulation.

Another thing you can try is to write your form data directly (without saving it to strNotes first).  That SEEMs like it might work (but I believe I have also seen posts about this causing truncation issues too):

Dim strSQL as string
strSQL = "INSERT INTO YourTable (NotesID, Notes)  VALUES (" & Me.NotesID & ", " & chr(34) & Me.txtNotes & chr(34) & ")"
currentdb.execute strSQL, dbfailonerror

Select allOpen in new window

I am going to try both methods mentioned in these replies.  I won't be able to until tonight, so I will post my result late tonight or tomorrow morning.

fyed,

Ok I tried your code, and I was able to make it work like I wanted.  Thank you.  However, there is one big hangup.

Here is what is happening:  I step thru code and everything is working.  I am adding a NEW ISSUE to the issues table and its NEW NOTE in the notes table.  I can actually see the new record in both tables, and it all looks good.  

Then my code goes on to do other things such as query tables for existing ISSUES & their NOTES.  It will then displays the new Issue along with its new Note/Comment in a different form.  However, the queries do not see the new Issue or the New note.

Fyi, the form to add new issues & notes is still open at this point in time.   My code is supposed to close it in a few steps but it fails because queries cannot see the new issue & notes.  

Any ideas?  Is there some kind of "commit" command that is missing?  

Here is the code that I used to add the new note into the notes table:

    Dim dbCurr      As DAO.Database
    Dim rsAddNote   As DAO.Recordset

    lngNotesID = GetNewNotesID
    
    Set dbCurr = CurrentDb
    Set rsAddNote = dbCurr.OpenRecordset("tbl_Notes", , dbFailOnError)
        With rsAddNote
            .AddNew
            !NOTES_ID = lngNotesID
            !NOTES_DESCR = Forms!frm_AddNewIssue!txtNotes
            .Update
        End With
    
    rsAddNote.Close
    
    Set rsAddNote = Nothing
    Set dbCurr = Nothing

Select allOpen in new window

If you currently have a form open and the record source of that form (or maybe as subform) includes tbl_Notes, then you will need to requery the form (me.requery) in order for it to see the newly added note.

So, lets assume you have form A open, which contains a button to add a note.  That button opens form B, where you enter the note and another button calls the code above.  In form A's button CLICK event you need codes like:

Private Sub cmd_New_Note_Click

    Dim lngID as Long

    'This line is used to keep track of the record that is currently displayed on the form.
    'It is needed because requerying the form will automatically set the forms recordset
    'pointer back to the first record in the recordset
    lngID = me.ID

    'Use the 'acDialog' WindowMode argument to halt processing of the code in this
    'subroutine until FormB is closed
    docmd.OpenForm "FormB",,,,,acDialog

    'Requery the form so that the newly added note will be visible
    me.requery
    With me.recordsetclone
         .FindFirst "[ID] = " & lngID
         me.bookmark = .bookmark
    End With

End Sub

If the "notes" are displayed in a subform, then you won't need to requery the main form, but the subform, that code would look like:

    'Requery the form so that the newly added note will be visible
    me.subFormControlName.Form.requery

And you would not need to use the With / End With lines because you would not be changing the recordsource of the main form.

I am afraid I didn't explain the issue correctly.  My apologies.  

The issue is not with seeing the new record on the form or a new form.  The record is not even being seen when you manually run a simple select query.  I can see the record in the table itself, but the query will not see the record.  

It is driving me crazy...arrrrggh.

can you post the query string?

Select noteId, note
 from tblnotes
 where noteId = [enter noteId]

OK, so when you open the table, you see the newly added note, but when you run this query you don't?

Just a question, are those the only two fields in tblNotes? Normally I would attach my notes to a record in some other table by having at least one more field in the table.  Is it possible that this is the problem?

If you try:

SELECT * FROM tblNotes ORDER BY NoteID Desc

Does the record show up as the first record?

fyed,

I finally got it to work.  In my process once user clicks SAVE button then data is saved to tables, another form to view all the newly saved data is commanded to open.  Upon opening i have code that does certain tasks based on which active form is calling my procedure.  Somehow by using the recordset method, it tripped up which form was active.  Somehow it wouldn't let go of the AddNew form and make the VIEW form the active form.  This broke all my process flow.  I still don't know how it did that though.

Anyway, Your suggestion pointed me in the correct direction.  Thank you for your time.  

PS:  One last thing.  If you can tell me why the recordset method would affect which form was active, I sure would appreciate it.

Without seeing exactly how you implemented the recordset methodology, I'd purely be guessing.  Generally, that method doesn't have anything to do with the form, so it should not have any effect on anything else you are doing, other than the need to requery any form that would be displaying those new notes.

If you can provide examples of how your are opening each of the forms, and the code in the various click events I might be able to give you some idea.