MySQL TEXT field to unbound Access 2007 textbox

Posted on 2012-08-13
Medium Priority
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.id, 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 receipts.id = " & 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
  • 3
  • 2
  • 2
LVL 61

Expert Comment

ID: 38289180
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

ID: 38290040
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

ID: 38290210
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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 38290222
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

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

Author Closing Comment

ID: 38290242
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

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


Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

750 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