MySQL TEXT field to unbound Access 2007 textbox

Posted on 2012-08-13
Last Modified: 2012-08-13
I have an Access front end client working on a MySQL backend server. I have one MySQL field that populates an unbound textbox via a recordset lookup. When the data it retrieves gets large, the data in the textbox truncates in strange ways. Sometimes the last few characters at the end of the field data post, sometimes, the last 200 or so characters post.
What can be happening here? The code that populates the form is attached.
The textbox is named txt_count_notes. Thanks

Dim dbs As Database, rst As Recordset, strSQL As String, vSales_Order As Long, X
strSQL = "SELECT DISTINCT, receipts.customer_id, receipts.sales_order_id, receipts.count_correct, receipts.count_notes, "
strSQL = strSQL & "receipts.parts_damaged, receipts.parts_damaged_notes, receipts.order_size_id "
strSQL = strSQL & "FROM receipts WHERE = " & TempVars("record_id")

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

X = Nz(ELookup("complete_date", "wtbl_receivers", "receiver_id=" & TempVars("record_id")), 0)
If X <> 0 Then X = -1

With frm
    .cmb_customer.BoundColumn = 1
    .cmb_customer.Value = rst!customer_id
    .cmb_customer.BoundColumn = 2
    .cmb_order_size.BoundColumn = 1
    .cmb_order_size.Value = rst!order_size_id
    .cmb_order_size.BoundColumn = 0
    .chk_count.Value = Replace(rst!count_correct, 1, -1)
    .chk_damage.Value = Replace(rst!parts_damaged, 1, 0)
    .txt_count_notes.Value = rst!count_notes
    .txt_damage_notes.Value = rst!parts_damaged_notes
End With

Set rst = Nothing
Set dbs = Nothing

Open in new window

Question by:JP_TechGroup
    LVL 61

    Expert Comment

    Is receipts a table or is it another query?

    If it is a Totals query, and your notes field is included in a GROUP BY clause, you may see truncation of that field. If that is the case, try using FIRST, LAST or something other than GROUP BY for that notes field.

    See this article for common causes of memo field truncation in Access:

    Author Comment

    Receipts is a table.
    I just checked the value of the recordset and
    rst!count_notes is truncating the output. Weird.
    I resolved the issue by doing an independet lookup on this field and populating that way, but it would be nice to have an answer to the question.
    The attached article does not seem to apply. Thanks.
    LVL 29

    Expert Comment

    Could it be that the field count_notes includes some carriage returns?  I'm just hazarding a guess.  If so, then using the replace function would be a good idea.  Otherwise, can you import the records into an Access table and see if it also exhibits this issue?
    LVL 61

    Accepted Solution

    Just for kicks, drop the DISTINCT keyword from your query to see if you have the same issue with a simple SELECT statement.
    LVL 61

    Expert Comment

    That article actually cites uniqueness (such as DISTINCT ) as a source of this problem.

    Author Closing Comment

    Just re-read it and you're correct. Not sure how I missed it.
    Removing the DISTINCT from the query and just running SELECT did the trick.
    LVL 29

    Expert Comment

    Hey Miriam, that's a good article to remember.  I'm going to take note of this.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now