Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007

    Question Unanswered: Truncated Data and alternate methods

    Hi All,

    In my DB I have a form that is not behaving as expected. Several of the fields on the form are textboxes. The final destination for the data entered by the user in these textboxes are Memo fields in a table.

    When the user clicks "Submit", I capture all data from all of the controls and attempt to write the data to the table using db.execute (after building the SQL Insert Statement). The Insert is performed and does not return errors but the issue is that in many cases I only get a portion of the data intended for the Memo Fields.

    The only solution that I could find that was 100% reliable was to create additional textboxes on the form that were directly bound to the Memo Fields in the table. The visible property is set to false initially for the bound controls. When the user clicks submit, I set it to visible, write the data from the unbound control to the bound control and then re-hide. The user never sees the control as the fore color and back color are set to White and the border is set to transparent. The speed does not seem to be an issue, but I am concerned about maintenance down the road due to the complexity of the forms and the additional code.

    So let me ask 3 questions:
    1) Is db.execute the best way to perform such an insert from a coding perspective or is there a better method?

    2) Is there a character limitation for a variable with a datatype of "Text"?

    3) Is my solution (adding bound controls) a really bad idea? If so, why?

    Ideas? Thoughts?

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia

    Re your questions.

    1. I use the AddNew method to add records to a table, so far I found no problems adding fields that are memo type.

    2. Datatype "TEXT" has limitation of 255 characters.

    3. You may have insufficient textboxes to capture the

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 12
    Quote Originally Posted by Poppa Smurf
    3. You may have insufficient textboxes to capture the
    Personally I use the DoCmd.RunSQL command to (funnily enough) run my SQL commands!
    DoCmd.runSQL "INSERT INTO MyTable(Notes) Values('This is a test')"
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    There's said to be a limit on memos of 64K. I think that's a limitation of Access text controls, not memos, as

    1. Yes, .Execute on a DAO database object (also use dbFailOnError!), or an ADO connection object, is the best way to perform such.
    2. Jet text fields are limited to 255 characters, Memos 64K characters (though, I'm suspecting the latter is more an issue with Access text controls than the field type itself)
    3. Bound controls and Memos are known to cause corruption.

    A memo field isn't stored in the table itself, only a pointer to the actual location is stored, and sometimes those pointers get corrupted when you work with bound memofields. Corrupting a memo can corrupt the whole record. One method I've seen around, is to use unbound controls, but add the memos to the recordsource of the form. That way, you can stuff the value from the unbound controls into the actual fields, without going through bound controls. Say

    Me!TheMemoField = Me!txtTheUnboundControl.Value

    Another, is to store memos in a separate table bound through the primary key, so that if a corruption should occur, then you only loose the memo, not the whole record.

    The query approach, I can do the following with .Execute without any problems

    CurrentDB.Execute "UPDATE myTable SET myMemo = '" & String(65000, "@") & "'", dbFailOnError

    which inserts 65 000 "@" into that field. If you experience truncating, I don't think this is what causes it. Are you using dbFailOnError? If so, you should get a message about whatever errors occurs. Perhaps if you show a bit of code?

  5. #5
    Join Date
    Jun 2007
    Thank for your response.

    I am just getting back to this issue. There really isn't any code to show because I was just using a bound field. But, per your request, here is the function that inserts the data.

    Function SubmitFeedback()
    'This module writes all data from the userform to the Feedback table.*
    '***Variable Declaration
        Dim Title As String, Content As String, AID As String, PID As String, strSQL As String, SubmitTime As String, Status As String, Response As String
        Dim strPartner As String, strID As String, strHistory As String, strTimestamp As String, PartnerID As String
        Dim db As Object
    '***Get Values
        'Get the title
            Title = Form_Feedback_Entry.tbTitle.Value
        'Get the content
            Content = Form_Feedback_Entry.tbContent.Value
        'Insert the Content
            'Me![Question] = Me.tbContent.Value
        'Set the Timestamp
            strTimestamp = Now
    '***Write history if the user has the flag set
        'Is the history flag set?
        PartnerID = CurrentUser
        strHistory = DLookup("[History]", "UsysUsers", "[PID] = '" & PartnerID & "'")
        'What is the Partner's Name
        strPartner = DLookup("[PartnerName]", "UsysUsers", "[PID] = '" & PartnerID & "'")
        Select Case strHistory
            Case Is = "Yes"
               'Set the string
                strHistory = strTimestamp & " " & strPartner & " Initiated Feedback " & Chr(34) & Title & Chr(34) & Chr(163) & Content
                Form_Feedback_Entry.tbHistory.Visible = True
                Form_Feedback_Entry.tbHistory.Text = strHistory
                Form_Feedback_Entry.tbHistory.Visible = False
            Case Else
                'Do not write to history
        End Select
    '***Get the Row ID for the insert
        strID = Form_Feedback_Entry.tbID.Value
    '***Close the form
        DoCmd.Close acForm, "Feedback_Entry"
    '***Get/Set values
        Status = "New"
        PID = CurrentUser
        AID = DLookup("[AID]", "UsysUsers", [PID] = PID)
        SubmitTime = Now
    '***Insert the remaining data for the row into the table
        strSQL = "Update Feedback SET Author = '" & AID & "', Partner = '" & PID & "', Submitted = '" & SubmitTime & "', Status = '" & Status & "' WHERE ((ID =" & strID & "));"
        Set db = CurrentDb
        db.Execute strSQL, dbFailOnError
    '***Test to see if the row was written
        If db.RecordsAffected = 1 Then
            'Tell them it was successful
            Response = MsgBox("Feedback Submitted Successfully.", vbOKOnly + vbInformation, "Feedback Submitted")
        ElseIf db.RecordsAffected <> 1 Then
            MsgBox "Serious Error, the row was not written to the DB"
        End If
    End Function
    Only two fields are on the form. There is a "title" field, and a "question" field. The "question" field is the memo field. I tried using your suggestion "Me!TheMemoField = Me!txtTheUnboundControl.Value" as follows:
    Me![Question] = Me!tbContent.Value
    When I try this I get an error "Invalid use of the Me Keyword" is there a syntax issue?

  6. #6
    Join Date
    Nov 2004
    So, your code isn't within the forms class module? The Me keyword can only be used within classes, such as the forms class or reports class (most common), and will there reference the class object (form or report).

    In standard modules, you'd need to qualify the reference. While the notation you use will work (even if the form is closed), the more common referencing style, is going through the forms collection - for instance like some of the following:

    Forms("Feedback_Entry").Controls("txtNameOfControl ")

    Just replace the "Me" with the referencing style you wish, to refer to the form object where they reside, and hopefully it should work.

  7. #7
    Join Date
    Jun 2007

    Thumbs up


    I understand... Right or wrong, I do it outside of the class module. I am feeling stupid for not knowing that....

    So let me ask.. Out of habit I have always coded most of my functions in standard modules. I have always used the class modules for events such as form open / button clicks. Other than the obvious "the ability to access the objects without having to fully qualify them" are there other advantages to keeping all of the code within the class module?

    BTW. I moved the code for this form ALL into the class module. Your advice worked perfectly!!!!


  8. #8
    Join Date
    Nov 2004
    I don't think there's any hard and fast rules around. My "rules" are that functions or subs that can be called from more than one form/report, I'm likely to put into a standard module. Subs or functions only called from within the form (or subforms within it), I'll keep in the form class module. Other people have other preferences.

    Sometimes, if I find some event code particularly lengthy, I'm inclined to stuff that into a standard module, too.

    At one point of time, the term "lightweight forms" was introduced - forms without any code at all, see for instance I don't think it became very popular. The theory is that a form without code, loads "lightening" fast. It's a nice theory, but with moderately applied event code, I think you still need a timer of some sort to to see the difference, I don't think the human eye is sufficient. I think working with data retrieval (filtering, indexing, selecting as little as possible when the form loads...) pays off more than playing with "lightweight" forms, performance wise. Also, at least in some version, you cannot use the Form_FormName syntax for forms without class module (nor instantiate forms through the New keyword)

    Many moons ago, I read something about what supposedly happen when you load an application (or form) in Access 2000 and later.

    1 - the class module of a form loads when you load a form (module less forms will then load faster)
    2 - if you call a sub or function in a standard module in the startup or form load, the whole module, not only the little part where the sub/function is, is loaded into memory

    If this is true (I've never bothered testing, and I don't even recall where I read it), then it might perhaps be interesting to experiment with removing lengthy code from the forms class module, and not calling subs of functions in large standard modules in app/form startup. But again, I don't know.

    Dunno if I answered anything other than - perhaps it's just preferences?

  9. #9
    Join Date
    Jun 2007

    Somewhere over the years I have just always ended up with a class module for each form and a Standard Module that contains all of the sizeable code for that Class Module.

    If the Form is named Feedback_Entry I would have

    Class Module named Form_Feedback_Entry
    Std. Module named Feedback_Entry_Utilities

    So I guess there is no real right or wrong, just preference..

    I am in the process of implementing your solution in all cases where I have controls (TextBoxes) bound to Memo fields in the db.

    Thank you so much for your help!
    Last edited by Brent Blevins; 08-24-07 at 16:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts