Link to home
Start Free TrialLog in
Avatar of JP_TechGroup
JP_TechGroupFlag for United States of America

asked on

MySQL TEXT field to unbound Access 2007 textbox

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)

rst.MoveLast
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

dbs.Close
Set rst = Nothing
Set dbs = Nothing

Open in new window

Avatar of mbizup
mbizup
Flag of Kazakhstan image

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:
http://allenbrowne.com/ser-63.html
Avatar of JP_TechGroup

ASKER

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.
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?
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That article actually cites uniqueness (such as DISTINCT ) as a source of this problem.
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.
Hey Miriam, that's a good article to remember.  I'm going to take note of this.

/Ron