Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inline INSERT From Textbox Limited to 512 Char (Access 2002/SP2)

    This problem baffles me. All I am trying to do is execute an inline INSERT statement from a command button to update a local Access table. The problem is that only up to 512 characters are written to the table. We need to be able to insert more than 3000 characters into the table. The field date type is Memo. Here is the inline INSERT statement within the VBA beneath the form:

    DoCmd.RunSQL "INSERT INTO [tblISStatus_Temp] (STATUS_PROJECT_ID, STATUS_PROJECT_NAME, STATUS_USER_UPDATE, STATUS_DATE_COMPLETED, STATUS_PERCENT_COMPLETE, STATUS_ASSIGNED_PRIORITY, STATUS_PUBLIC_DESC, STATUS_DATE) VALUES ([REQUEST_ID].Value, [txtUpdateProjectName].Value, '" & gstrUserName & "', [txtUpdateDateCompleted].Value, [txtUpdatePercentComplete].Value, [txtUpdateAssignedPriority].Value, [PUBLIC_STATUS].Value, Now())"

    The problematic field here in tblISStatus_Temp is STATUS_PUBLIC_DESC. Another strange occurrence is that when I insert text greater than 512 characters, random characters are displayed starting from position 513. Here is an example:
    YouYouYouYouYouɇ

  2. #2
    New Lounger
    Join Date
    Mar 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inline INSERT From Textbox Limited to 512 Char (Access 2002/SP2)

    THANKS CHARLOTTE ! ! ! That was it.

    Tom-G (working with Allen)

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inline INSERT From Textbox Limited to 512 Char (Access 2002/SP2)

    Try concatenating the values into a SQL string and then executing the SQL. I'm assuming that the referenced values represent controls on the form, right? SQL knows nothing about Access forms, so you either have to concatenate the value or use the full form reference.

    Dim strSQL as String

    strSQL = "INSERT INTO [tblISStatus_Temp] (STATUS_PROJECT_ID, STATUS_PROJECT_NAME, STATUS_USER_UPDATE, " _
    & "STATUS_DATE_COMPLETED, STATUS_PERCENT_COMPLETE, STATUS_ASSIGNED_PRIORITY, " _
    & "STATUS_PUBLIC_DESC, STATUS_DATE) VALUES (" & Me.[REQUEST_ID & ", " & Me.[txtUpdateProjectName _
    & ", '" & gstrUserName & "', " & Me.[txtUpdateDateCompleted] & ", " & Me.[txtUpdatePercentComplete] & ", " _
    & Me.[txtUpdateAssignedPriority] & ", " & Me.[PUBLIC_STATUS] & ", #" & Now() & "#)"


    DoCmd.RunSQL strSQL
    Charlotte

  4. #4
    New Lounger
    Join Date
    May 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inline INSERT From Textbox Limited to 512 Char (Access 2002/SP2)

    Thank you Charlotte! I implemented your code and it works as expected. I really appreciate you taking the time to share your knowledge here.

    Allen

Posting Permissions

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