memo field truncating data on form

[gillianw]

Hi


In a 2010 database I have a form with a query as the record source, one of the fields is a memo field. The query is not a union query, has no DISTINCT with the select and has no aggregate functions. There is no formatting on the memo field either. However sometimes when a user opens the form data in the memo field is truncated - other times its not, so they could look at e.g. record 5 one day and see all data in the memo field and on another day on viewing record 5 the data is truncated. This occurs on all machines that use the database.

The database has user level security and the users who experience this problem have the following permission:
Table the query pulls data from - read design, read data, update data, insert data
Query for the form- read design, read data
Form - Open/run

This has not so far happened to a user in the Admins group. I am at a loss with this one so am hoping someone else has experienced this and found the solution. Any help much appreciated.

[RuralGuy]

Is your db split and do all of the users have their own copy of the FrontEnd?

[gillianw]

Yes, back end on dedicated server and each user with their own front-end.

[RuralGuy]

It sounds like you have already reviewed Allen Browne's link here: http://allenbrowne.com/ser-63.html Maybe if you post the SQL for the query someone might see something.

[gillianw]

Yes I did look at Allen Browne's page. Here is the query, it's the memDetailsSummary field that truncates the data:

SELECT tblCase.lngCaseID, tblCase.txtSARICaseID, tblCase.dtmDateOpened, tblCase.dtmDateClosed, tblCase.lngCRPDistrictID, tblCase.memDetailsSummary, tblCase.lngCaseCategoryID, tblCase.lngSchoolID, tblCase.dtmDateEntered, tblCase.memActionWanted, tblCase.ysnRiskFactors, tblCase.txtRiskFactors, tblCase.txtNoOfIncidents, tblCase.txtIncidentPeriod, tblCase.ysnReportedToPolice, tblCase.txtCrimeReferenceNos, tblCase.lngLocalAuthorityID, tblCase.lngFamilyEthnicityID, tblCase.dtmDateReferralToCSO, tblCase.lngFearHateCrimeIDBefore, tblCase.lngFearHateCrimeIDAfter, tblCase.ysnCaseNotToGoToCRP
FROM tblCase WHERE (((tblCase.lngCaseID) = 5));

[RuralGuy]

The SQL looks pretty standard. You're pulling 22 fields. How many fields are actually in the record?

[gillianw]

Thats all of them - I used a query as my users can search for the case they want by either lngCaseID or txtSARICaseID - they make their choice, I re-write the query (only the WHERE clause) with VBA and the form displays the case they selected.

[RuralGuy]

How many characters might you find in that memo field? Have you figured out how many characters it is truncating to?

[gillianw]

Some will have less than 255 some way, way more. Tomorrow I will get one of my users to let me know what the longest entry is in that field. Can't answer the second one yet, no one has been systematic up to this point when the error occurs, but I now have them under strict instructions to copy and paste the contents of the field next time this happens.

[RuralGuy]

I'm pretty much out of ideas at this point. How did you implement User Level Security? Is everyone using ac2010?

[gillianw]

The database was originally built in 2003 which is where user level security was added (the problem also happened in 2003) - they all then upgraded to 2010 and I came in to convert the database to the new format.

[RuralGuy]

Thanks for the update.