Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26

    Unanswered: Update Query - 127 character problem

    I am having the 127 character problem running an update query on a form. If the field on the form that holds the text to be used for the update value contains more than 127 characters, the update query will not run and returns an error. Any work arounds?

  2. #2
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59

    Re: Update Query - 127 character problem

    Originally posted by Darren_Taylor
    I am having the 127 character problem running an update query on a form. If the field on the form that holds the text to be used for the update value contains more than 127 characters, the update query will not run and returns an error. Any work arounds?
    Can you not go to the relating table field and amend the amount of allowed characters?? That is assuming your field on the form is linked to a field in an underlying table!!

    Regards Dave

  3. #3
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Hi, the field is a stand alone text box on the form..

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Update Query - 127 character problem

    Originally posted by Darren_Taylor
    I am having the 127 character problem running an update query on a form. If the field on the form that holds the text to be used for the update value contains more than 127 characters, the update query will not run and returns an error. Any work arounds?
    Where are you running this query from?

  5. #5
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    I have a form with a text box, the user inputs their comments and then clicks a label which runs the update query, below, the comments are added to a memo field. All works fine until the user inputs more than 127 characters.

    UPDATE tblInteraction_Log SET tblInteraction_Log.Comments = Date() & "
    " & CurrentUser() & "
    " & [Forms]![frmInteraction_Form]![Text23] & "." & "

    " & [tblInteraction_Log].[Comments]
    WHERE ((([tblInteraction_Log]![ID])=[Forms]![frmInteraction_Form]![ID]));

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Are you using the DoCmd.RunSQL? How are you "running" this update query?

  7. #7
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    I'm running a macro - Open Query Action
    DoCmd.RunMacro

  8. #8
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Private Sub Label26_Click()
    On Error GoTo Err_Label26_Click
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    Dim stDocName As String

    stDocName = "macUpdate_Interaction_Comments"
    DoCmd.RunMacro stDocName
    Me!Text23 = Null

    Exit_Label26_Click:
    Exit Sub

    Err_Label26_Click:
    MsgBox Err.Description
    Resume Exit_Label26_Click

    End Sub

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Darren_Taylor
    Private Sub Label26_Click()
    On Error GoTo Err_Label26_Click
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    Dim stDocName As String

    stDocName = "macUpdate_Interaction_Comments"
    DoCmd.RunMacro stDocName
    Me!Text23 = Null

    Exit_Label26_Click:
    Exit Sub

    Err_Label26_Click:
    MsgBox Err.Description
    Resume Exit_Label26_Click

    End Sub
    What does the macro "stDocName" do? I would also suggest that you read some documentation on the RunSQL command. THE SQL statement has a MAX of 255 characters ...

  10. #10
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    The macro simply runs the update query. I don’t think the Run SQL statement will make any difference, whilst running, the update query will effectively have the text in two places, so the max of 255 characters is reached which would explain why only 127 characters can updated at one time.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You answered that question ... The only way I know to do this is to use a recordset and poke accross the memo field with the AppenkChunk method.

  12. #12
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    I’ve got hold of a sample database, vb runs the sql, strSQL. The memo field is updated with contents of a memo field stored in a different table, supplied by the form. I’ll give it a whirl… Thanks for your assistance and patience.

Posting Permissions

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