Louverril
asked on
Cannot save memo field when more than 1 user and 356 chars.
Please see attached a front end and back end database. There is only one table and one form.
On the form there is only one field - which updates a memo field in the table in the back end.
When two users have the form open (displaying the one and only record, 356 chars counted in Word) if you add one more characters to the record it cannot be saved
If you delete a character, then save, then you can then replace it.
For example open the front end on two pc's, open the form and try to add the letter E to the very end of the form.
You get a message saying the record is locked on the other machine.
If you have the form open exclusively then you can save OK
The original form was more complicated but I have cut this down to show that the other stuff I had on the form was not causing the problem.
Do I have the Access Options for refresh and locking set correctly?
I am at a loss to explain this.
My customer discovered the problem on his network - a different setup completely to mine.
Any ideas?
Lou NotesProblem.zip
On the form there is only one field - which updates a memo field in the table in the back end.
When two users have the form open (displaying the one and only record, 356 chars counted in Word) if you add one more characters to the record it cannot be saved
If you delete a character, then save, then you can then replace it.
For example open the front end on two pc's, open the form and try to add the letter E to the very end of the form.
You get a message saying the record is locked on the other machine.
If you have the form open exclusively then you can save OK
The original form was more complicated but I have cut this down to show that the other stuff I had on the form was not causing the problem.
Do I have the Access Options for refresh and locking set correctly?
I am at a loss to explain this.
My customer discovered the problem on his network - a different setup completely to mine.
Any ideas?
Lou NotesProblem.zip
ASKER
The link refers to a completely different problem.
There is no problem saving the record etc. as long as it is below a certain number of characters. As can be seen from the attached files no workgroups are involved.
Thanks anyway,
Lou
There is no problem saving the record etc. as long as it is below a certain number of characters. As can be seen from the attached files no workgroups are involved.
Thanks anyway,
Lou
ASKER
This applies to ANY memo field in any record.
The length seems to be limited to around 90 - 150 lines / 356 chars.
Any one who has time try and see if you can get one to work.
fornt end on two pc's with a simple form and containing a memo field. Once you gt passed a certain length one then bothe users are locked out!
Lou
The length seems to be limited to around 90 - 150 lines / 356 chars.
Any one who has time try and see if you can get one to work.
fornt end on two pc's with a simple form and containing a memo field. Once you gt passed a certain length one then bothe users are locked out!
Lou
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also make sure your set for Record level locking (File, Options, Client Level Settings and check "Open databases by using record level locking" under Advanced).
JimD.
You should be able to enter 65K of data in a standard memo field - and 1 gig if you add it through programming
Which version of access are you using and how large is the file
2003 still has a 2 gig limit overall
Which version of access are you using and how large is the file
2003 still has a 2 gig limit overall
ASKER
Thanks for your response Jim,
I suspected something like that related to the size of the record but I had no knowledge of LVP etc.
I had already tried out your suggestion. I had set the form property "Record Locks" to "Edited Record".
This did lock the other user out while the updates were being made (i.e. while the record was Dirty) - even on short records (as it's supposed to). But once you got "over the limit" regardless of whether you were the one who got there first you couldn't save the record.
Your reply prompted to check if this was also the case with the Defalt Record Locking set to "Edited Record" in the database Access Options. The same thing occurs though.
I have also tried opening and closing the form on each PC - but once the size is passed a certain point neither PC can then edit it. The only way to edit the record is for only one PC to have it open.
I am trying to think laterally for a way around this but I am stuck. I am thinking temporary table and a button on the ribbon that opens and closes the notes form to update it on eachPC. But that's going to be very messy....
Lou
I suspected something like that related to the size of the record but I had no knowledge of LVP etc.
I had already tried out your suggestion. I had set the form property "Record Locks" to "Edited Record".
This did lock the other user out while the updates were being made (i.e. while the record was Dirty) - even on short records (as it's supposed to). But once you got "over the limit" regardless of whether you were the one who got there first you couldn't save the record.
Your reply prompted to check if this was also the case with the Defalt Record Locking set to "Edited Record" in the database Access Options. The same thing occurs though.
I have also tried opening and closing the form on each PC - but once the size is passed a certain point neither PC can then edit it. The only way to edit the record is for only one PC to have it open.
I am trying to think laterally for a way around this but I am stuck. I am thinking temporary table and a button on the ribbon that opens and closes the notes form to update it on eachPC. But that's going to be very messy....
Lou
ASKER
DTHConsulting:
Yes I know - but you just try doing that in the situation I described above - two front ends and a back end, bothusers have the front end open at the same record.
As mentioned if only one use has the record open then no problrm you have your 65K
Lou
Yes I know - but you just try doing that in the situation I described above - two front ends and a back end, bothusers have the front end open at the same record.
As mentioned if only one use has the record open then no problrm you have your 65K
Lou
<<This did lock the other user out while the updates were being made (i.e. while the record was Dirty) - even on short records (as it's supposed to). But once you got "over the limit" regardless of whether you were the one who got there first you couldn't save the record.>>
That's really odd.
I can't think of anything else and I've never heard of anyone with this problem before. Did you create this new test DB from scratch or from the clients?
I'm wondering if there isn't some corruption at work here...
JimD.
That's really odd.
I can't think of anything else and I've never heard of anyone with this problem before. Did you create this new test DB from scratch or from the clients?
I'm wondering if there isn't some corruption at work here...
JimD.
ASKER
Jim,
I created one from the clients, one COMPLETELY from scratch and another from another clients!
All have exactly the same problem.
Also I tested it with the form based on a query not a table.
Although I didn't test the record locks out with the form based on a query. I'll give it a go.
Lou
I created one from the clients, one COMPLETELY from scratch and another from another clients!
All have exactly the same problem.
Also I tested it with the form based on a query not a table.
Although I didn't test the record locks out with the form based on a query. I'll give it a go.
Lou
I'll look at it again, and if I still can't figure anything out, I'll ask around and see if I can't get some answers.
JimD.
ASKER
TEST-Memo-Field-Dbs.zip 
Thank you Jim.
I recreated the table (auto primary key and one memo field - rich text) and then used the form creator to create a simple form. I created a query to use as the reocrdsource. I still had the same problem.
I have attached some sample databases (all created form scratch - no code - very very basic) ) and instructions so you can more quickly recreate the problem.
The instructions below are also attached in a more readable formatted pdf version : Instructions to recreate the problem.pdf
I feel like no one beliewves me that this is happening so I took the time to make it easy for people to recreate,
The front ends are all mapped to Y as a backend location - but of course you can change this.
Thanks Lou
Instructions/Description of Problem:
Issue - Memo field cannot save is multiuser environment
This describes how to recreate the problem where more than one user cannot update a memo field after the contents of the filed exceed a certain size (about 100 very simple lines).
There are four databases in all – see attached.
Two front ends TEST-FE-EditlockonDb.accdb
Each of these databases either has EditLockOn set in the Access Options Advanced or has No Locks set.
All databases have “Open databases by using Record Level Locking” checked.
Inside the two front ends there are four forms and one table. They are all copies of the same form but two are based on a query of the table and two use the table as the direct record source. For each type of record source there is a version of the form which has Lock On Edit set in the forms properties and one with No Locks set in the forms properties.
Put TEST-EditLockOn_BE and TEST-NoLocks_BE on a file server.
Put Front Ends, TEST-FE-EditlockonDb.accdb
Test One (repeat with other versions of dbases and forms - if required) but this (TEST-FE-EditlockonDb & TEST-EditLockOn_BE ) is the combination which out of them all should NOT have a problem as the locking is so controlled.
Use TEST-FE-EditlockonDb.accdb
Link front end table to TEST-EditLockOn_BE
Open form frmTestMemoLockOnEdit-Qry on PC A and paste the type around 30 lines of text – could just be numbers, see below. Click Save on Ribbon.
Open form frmTestMemoLockOnEdit-Qry on PC B and add around a ten line chunk to the memo field – e.g. numbers 1-10 each on separate rows. Click Save on Ribbon.
Keep adding chunks of a few lines and saving on alternate PC’s – eventually (at about 100 lines) one PC will get this message “Could Not Save, currently locked by another user” (see attached for actual message.)
The text below was entered in blocks – when the length got to the point indicated the user could not save (the numbers 2 – 9). The last successful save was after the last “Typing something else” was saved.
Text that was entered - in chunks on alternate PC's.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
The lazy brown fox jumped over the quick dog
Today is Thursday 10th March
2
3
4
5
6
7
8
9
Typing anything
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9 <- got to here and could not save.
Recreate-the-Mermo-field-problem.pdf Recreate-the-Mermo-field-problem.pdf
Thank you Jim.
I recreated the table (auto primary key and one memo field - rich text) and then used the form creator to create a simple form. I created a query to use as the reocrdsource. I still had the same problem.
I have attached some sample databases (all created form scratch - no code - very very basic) ) and instructions so you can more quickly recreate the problem.
The instructions below are also attached in a more readable formatted pdf version : Instructions to recreate the problem.pdf
I feel like no one beliewves me that this is happening so I took the time to make it easy for people to recreate,
The front ends are all mapped to Y as a backend location - but of course you can change this.
Thanks Lou
Instructions/Description of Problem:
Issue - Memo field cannot save is multiuser environment
This describes how to recreate the problem where more than one user cannot update a memo field after the contents of the filed exceed a certain size (about 100 very simple lines).
There are four databases in all – see attached.
Two front ends TEST-FE-EditlockonDb.accdb
Each of these databases either has EditLockOn set in the Access Options Advanced or has No Locks set.
All databases have “Open databases by using Record Level Locking” checked.
Inside the two front ends there are four forms and one table. They are all copies of the same form but two are based on a query of the table and two use the table as the direct record source. For each type of record source there is a version of the form which has Lock On Edit set in the forms properties and one with No Locks set in the forms properties.
Put TEST-EditLockOn_BE and TEST-NoLocks_BE on a file server.
Put Front Ends, TEST-FE-EditlockonDb.accdb
Test One (repeat with other versions of dbases and forms - if required) but this (TEST-FE-EditlockonDb & TEST-EditLockOn_BE ) is the combination which out of them all should NOT have a problem as the locking is so controlled.
Use TEST-FE-EditlockonDb.accdb
Link front end table to TEST-EditLockOn_BE
Open form frmTestMemoLockOnEdit-Qry on PC A and paste the type around 30 lines of text – could just be numbers, see below. Click Save on Ribbon.
Open form frmTestMemoLockOnEdit-Qry on PC B and add around a ten line chunk to the memo field – e.g. numbers 1-10 each on separate rows. Click Save on Ribbon.
Keep adding chunks of a few lines and saving on alternate PC’s – eventually (at about 100 lines) one PC will get this message “Could Not Save, currently locked by another user” (see attached for actual message.)
The text below was entered in blocks – when the length got to the point indicated the user could not save (the numbers 2 – 9). The last successful save was after the last “Typing something else” was saved.
Text that was entered - in chunks on alternate PC's.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
The lazy brown fox jumped over the quick dog
Today is Thursday 10th March
2
3
4
5
6
7
8
9
Typing anything
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9
Typing something else
2
3
4
5
6
7
8
9 <- got to here and could not save.
Recreate-the-Mermo-field-problem.pdf Recreate-the-Mermo-field-problem.pdf
ASKER
Sorry attached intructions twice....
No problem. I still don't see why this doesn't work and it does look like a bug, so I've passed your first DB onto the Access Team at Microsoft. Might be a day or two before we get an answer back.
JimD.
ASKER
Thanks Jim!
Will wait to hear.
Thanks for taking this seriously.
Lou
Will wait to hear.
Thanks for taking this seriously.
Lou
Lou,
Well early word is that it is a bug related to it being a Rich Text field, which is stored as HTML. If you change it to plain text, the problem goes away.
Looking at your original database, for those 350 or so characters, the resulting HTML is a little over 2000. Since JET stores things in unicode, which is two bytes per character, and page size in JET is 4096K, it seems like this occurs when it needs another page of storage.
Some how fetching a new page along with handling the HTML is causing the problem.
I doubt we'll see any offical response from Mcirosoft on this anytime soon, so in the meantime, can you work with it as plain text or no?
JimD.
Well early word is that it is a bug related to it being a Rich Text field, which is stored as HTML. If you change it to plain text, the problem goes away.
Looking at your original database, for those 350 or so characters, the resulting HTML is a little over 2000. Since JET stores things in unicode, which is two bytes per character, and page size in JET is 4096K, it seems like this occurs when it needs another page of storage.
Some how fetching a new page along with handling the HTML is causing the problem.
I doubt we'll see any offical response from Mcirosoft on this anytime soon, so in the meantime, can you work with it as plain text or no?
JimD.
ASKER
Plain text would not really be anywhere near as good for this particular client with the notepad - his small team have been using it for 7 months now and they highlight stuff - change the colour etc to break up the notes. They find it really useful to pass on messages and reminders and to make sure everyone sees them.
I can't even really see any scope for a sort of fiddly work around. Do you think it would be worth trying to capture the updates and trying to add them to the record via vba - somehow!!! Probably not.
As an option I could say well you'll have to accept there is a length restriction - but how can I handle that to stop it getting to the point where the record locks eveyone out? Some way of counting the record size....?
If not then he'll just have to cope with plain text.
When you say "doubt we'll see official response form Microsoft on this anytime soon" Will I ever hear anything?
Thanks again,
Lou
I can't even really see any scope for a sort of fiddly work around. Do you think it would be worth trying to capture the updates and trying to add them to the record via vba - somehow!!! Probably not.
As an option I could say well you'll have to accept there is a length restriction - but how can I handle that to stop it getting to the point where the record locks eveyone out? Some way of counting the record size....?
If not then he'll just have to cope with plain text.
When you say "doubt we'll see official response form Microsoft on this anytime soon" Will I ever hear anything?
Thanks again,
Lou
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim,
I'll look at the length idea as an option for them . At least it stops them getting locked out. I'll give them that option or plain text. Cross fingers they'll stick with me.
I am not sure about the individual records way of going though - it's would be very good in some circumstances but they like the simplicity of the single sheet notepad.
I really hope there is a fix for it - another new customer really likes the idea of that notepad on their front screen - it's simple and it means everybody in the office see's the same thing. For them I have also added a couple of dashboard type "subforms" on there, where they can see clients with actions due/clients with actions overdue. So the form acts as a sort of round up and central communication point for the whole system.
Shall I leave the question open?
Thanks Lou
I'll look at the length idea as an option for them . At least it stops them getting locked out. I'll give them that option or plain text. Cross fingers they'll stick with me.
I am not sure about the individual records way of going though - it's would be very good in some circumstances but they like the simplicity of the single sheet notepad.
I really hope there is a fix for it - another new customer really likes the idea of that notepad on their front screen - it's simple and it means everybody in the office see's the same thing. For them I have also added a couple of dashboard type "subforms" on there, where they can see clients with actions due/clients with actions overdue. So the form acts as a sort of round up and central communication point for the whole system.
Shall I leave the question open?
Thanks Lou
Until a fix is found for this - have you considered just linking the field to a word document - Then you can use all the formatting - and have an unlimited size limit
<<I really hope there is a fix for it - another new customer really likes the idea of that notepad on their front screen - it's simple and it means everybody in the office see's the same thing. For them I have also added a couple of dashboard type "subforms" on there, where they can see clients with actions due/clients with actions overdue. So the form acts as a sort of round up and central communication point for the whole system.>>
Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.
<<Shall I leave the question open?>>
Yes, leave it open for the moment. However if it becomes abandoned, just post a single comment to get it flagged active again.
I'd like to hear something back from Microsoft before we close this out.
JimD.
Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.
<<Shall I leave the question open?>>
Yes, leave it open for the moment. However if it becomes abandoned, just post a single comment to get it flagged active again.
I'd like to hear something back from Microsoft before we close this out.
JimD.
ASKER
<< Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.>>
That's interesting I'll give it a go.
<< Yes, leave it open for the moment. However if it becomes abandoned, just post a single comment to get it flagged active again.>>
OK
<<I'd like to hear something back from Microsoft before we close this out.>>
Thanks again!!!!!!!!!
Lou
That's interesting I'll give it a go.
<< Yes, leave it open for the moment. However if it becomes abandoned, just post a single comment to get it flagged active again.>>
OK
<<I'd like to hear something back from Microsoft before we close this out.>>
Thanks again!!!!!!!!!
Lou
<<<< Another option, which might get around the locking issue is breaking out the memo field into another table and doing a 1:1 relationship.>>
That's interesting I'll give it a go.
>>
Personally I don't like the idea because as soon as they move to the memo field, the main record will be committed. That leaves lots of confusion for someone that tries to undo changes and can't.
JimD.
That's interesting I'll give it a go.
>>
Personally I don't like the idea because as soon as they move to the memo field, the main record will be committed. That leaves lots of confusion for someone that tries to undo changes and can't.
JimD.
ASKER
That may be OK for this particular customer as there is no only one other field on the menu form and that is a drop down list they use to quickly check prices - they can't update it.
I will have to check it out - thanks for the warning.
Lou
I will have to check it out - thanks for the warning.
Lou
ASKER
I tried the 1:1 idea but hit the same problem once the record length went over a certain size.
Below is what I did - did I understand your idea?
Two tables tblA, tblB
tblA:
one key field fldKey
tblB:
two fields
fldKey
fldNotes
I set up a one to one relationship between the two key fields and giving then a value of 1.
frmMenu record source was a query fldKey from tblA and fldNotes from tblB
Lou
Below is what I did - did I understand your idea?
Two tables tblA, tblB
tblA:
one key field fldKey
tblB:
two fields
fldKey
fldNotes
I set up a one to one relationship between the two key fields and giving then a value of 1.
frmMenu record source was a query fldKey from tblA and fldNotes from tblB
Lou
Lou,
<<frmMenu record source was a query fldKey from tblA and fldNotes from tblB>>
needs to be a main /subform, main bound to tblA and subform to tableB.
JimD.
<<frmMenu record source was a query fldKey from tblA and fldNotes from tblB>>
needs to be a main /subform, main bound to tblA and subform to tableB.
JimD.
ASKER
Jim,
I have tried the subform method and I still get the same problem.
See the attached databases (FE asnd BE) and frmNotes in the FE.
Thanks
Lou MemoProblemExampleUsingSubform.zip
I have tried the subform method and I still get the same problem.
See the attached databases (FE asnd BE) and frmNotes in the FE.
Thanks
Lou MemoProblemExampleUsingSubform.zip
Lou,
Bummer, that's a suprise. I'll try it here when I get a chance just to double check, but I don't doubt your right.
JimD.
Bummer, that's a suprise. I'll try it here when I get a chance just to double check, but I don't doubt your right.
JimD.
ASKER
Please can you keep this question open until J Dettman has some more feedback.
Thanks
Thanks
I have not heard anything yet. I'll poke them again.
JimD.
ASKER
Jim,
Just to let you know I have put in place a length limit - as you described. This is a least stopping the error from occurring.
Thanks
Just to let you know I have put in place a length limit - as you described. This is a least stopping the error from occurring.
Thanks
So far, no response from the Access team. I'm going to ping them one more time.
JimD.
ASKER
Thanks Jim.
I think it is an important feature. Dashboards etc are getting much more popular. My customers find it really useful that the whole team sees the same notes/messages when they log on.
Thanks again,
Lou.
I think it is an important feature. Dashboards etc are getting much more popular. My customers find it really useful that the whole team sees the same notes/messages when they log on.
Thanks again,
Lou.
Lou,
I'm still pushing for an answer; even just to get them to acknowledge the bug.
Stay tuned...
JimD.
I'm still pushing for an answer; even just to get them to acknowledge the bug.
Stay tuned...
JimD.
ASKER
Thanks!
Lou,
I've pushed on this and got nothing back on it. I know the Access team has been busy, but gut tells me that it is probably a fairly serious bug and there is no quick solution.
Might as well close this.
JimD.
I've pushed on this and got nothing back on it. I know the Access team has been busy, but gut tells me that it is probably a fairly serious bug and there is no quick solution.
Might as well close this.
JimD.
ASKER
OK - shame.
Thanks for you help.
Thanks for you help.
ASKER
There wasn't a solution to the issue. However Jim Detman gave ways of getting around the issue which might be useful to other people.
Lou
Lou
<<OK - shame.>>
Yes. I'm more than a little bummed. I expected an un-official response of some type at the very least and not to be simply ignored.
JimD.
Yes. I'm more than a little bummed. I expected an un-official response of some type at the very least and not to be simply ignored.
JimD.
http://www.kbalertz.com/83