Solved: Record Locked Error 3188

http://www.vbaexpress.com/forum/showthread.php?t=8716

This relates to a post that I thought I had solved earlier. But for some reason has stopped working now.

I have a table that stores individual notes. These notes make up memos related to property aquisition. Now the problem is that it works for some and not for others. The Memo max size is 65,536 characters. So I don't think there is a problem with transfering too much information and I have yet to really find a pattern.

I have the function that I posted in the previous one but since it hadn't got looked at since I last posted it I thought I would try and draw some attention to it. I attached the error message.

What produces this error is trying to overwrite the Memo Field in tblProperties.

Any ideas?

Ok. Other possible solution. If someone can help me write a query that pulls all the individual notes from the tblPropertiesUpdates and list them in some concatenated order for a report I will scrap the intermediary Memo control which is the current source for the report.

Okay I figured out a way to update the memo but I have to close the main form and then re-open it. I thought that would be easy enough since I can just do DoCmd.Open formname, , , , ID= Me.ID... well that creates a filter and opens the form with only one record.

Is there a way to open a form and then move to a specified record?

I posted this same issue on another forum and they said that the best way to update a memo type object would be to use ADO objects. Is there a specific way to set up an ADO object that references the recordset? I'm not sure I understand the one that Microsoft posts on their help site.

I do not really understand why you get this error other than if it is a new record in a main table that has not been written to the table yet and the memo is in a different table.
You can get access to assign the current record number to a variable and close the form, reopen the form and move to the same record. But you should be able to achieve the the same outcome by moving to the previous record and then to the next record thereby ending up on the same record in the current session. Or you can do it using a record set clone to update the memo field, but the current record must have been saved by access.

Okay we have tblProperties that has a field Memo. Then we have tblPropertiesUpdates that has the field Notes. All notes are compiled into one memo by date and association of ProperyID. Then after a note is finished it rewrites a string with all notes and stores it in the tblProperties field Memo. So it is a different table that I am modifying. I tried the Close form and reopen.. then Move next record until it matches the variable that stored the current record... But I have to admit it looks very astheticlly displeasing... BUT it does work.

I will look up the recordset clone to see if that's an option. I'm still learning so it may take me a bit to figure everything out. But Thanks so much for the advice.

Did you try the Next/Previous or Previous/next without closing the form?

You should be able to find some Recordset VBA on this forum, if not I have some very simple examples, but I do not think it will help. The record that you are on has to be saved by Access before you can get to the data, another alternative may be to store it in a variable (may be set to "Public") to be picked later.
Further alternatives are, use an update query after the records have been changed or save the data to a "Dummy" table and pick it up from there to update your Memo field.
If you can post an example I could look at it for you.

Yeah I did the same thing as mentioned before but instead of closing the form I just hid the form advanced one record, made the changes to the memo. went to previous record on main form and unhid it and closed the secondary form. It works pretty nice... Don't know about efficiency, but it works.

Seriously I got it to work and today It's corrupted again. Is it bad programming>?:banghead: :bore Or is it bad charma? Not a real question just needed to address some frustrations and nobody here (at work) knows how to help me.

Any possibility of posting or emailing a copy?

Attachment 3972
The first image is the Main form you will see the large memo field here labled Property Notes. Formerly users would input notes here about transactions that had been made or communications that had been made concerning the property number.

users complained that these memos were "randomly" dissappearing and asked that I create a form that would store these notes as individual entries rather than one large memo.

The trick came when they informed me that they also don't want the large Memo to disappear. I went along with this originally because there is a report that has a query that pulls directly from the tblProperty that contains this Memo data. The form's data source is a query done on the tblProperties with additional information viewed in the various tabs.

Let me know if you have questions regarding this form.

Attachment 3973
This form is opened when the new note button is pressed. You can view all past notes in the order they were written. Buttons I believe are self explanatory except for the Done button.... I modified this button so that before closing this form it concatinates all the notes into one string with appropriate formatting for Note Date, Entry Type, Contacts, and RER + Note... done recursively until there are no more notes. Then the tblPropery.Memo field is set to the string variable with all "Note" concatinations.

It works fine unless there are large numbers of notes... (the whole string length is never greater than 10K char when the error breaks. I have a meeting I'll post more explanations later.... Hope this isn't bothering anyone that I'm posting all these pictures.

I can email empty tables or dummy tables if you want to view them.

Please email me some dummy tables.

I have looked at the tables, forms and VB that you sent me and 3 things come to mind. The first is that the EOF may not be the best way to control the data being added to the memo string. It might be better to go to the last record in the recordset and set a variable to the recordcount and use a for/next loop instead.
Second I would prefer to have a "Memo Updated" date in the tblproperties and then only "append" those memos with a date after the last update date.
Third, I am going to try using a form based on the tblproperties with the ID and memo on and update that directly from the VBA recordset. That way you can see what is happening with the data even if the VB crashes.
Is the subform with the SQL and VBA on where the memos originally get entered?

I have emailed you a copy of the database which simply adds the "Current" note being added to the beginning of the Memo field on the Main frmPropertiesNew form.

Thanks OBP. You are a life saver. It seems to work great sorry I have been out of the office for a couple of days on a short vacation with my wife. But got back and tested. Everything seems to work out nicely.

Keep me posted if you have any more problems or enhancements required.

Sorry to bother you again. I tested the adding feature and it works great. I've wrestled with that for my entire internship and it seems you have fixed it by just adding the new note to the entire memo. This does raise one question though.

What if the user adds a note from a past meeting that they forgot to insert? Is there a way to keep the dates in descending order (most recent to oldest)?

Also one thing I noticed last night after some additional testing: when I started deleting the individual notes the memo does not reflect these changes.

Yes it would be possible to do both of those, I hadn't even considered deleting notes, I just concentrated on the problem at hand.
In the first case it would involve "searching" the memo field first for a date either side of one you wanted to insert, when found you would "split" the memo field with the new data.(memo = left(memo) & linefeed & linefeed & newdata and linefeed & right(memo)
In the case of deletions you would find the "note" match in the memo using the instring function and then just replace it with the word "deleted", so that there is a record of the deletion.

Thanks I'll get cracking.

Let me know if you get stuck and I will give you a hand with the string work.

Sorry I know I'm asking a lot here, but is there a particular way to modify a memo so it behaves as an acceptable input for the Instr Function?

The only way that I know of is by converting it to a string, which brings you back to your problem of too many characters for a string.
I have to ask this question, why does it have to be in the form of a memo field, why aren't you showing the data from the original notes as a subform?

One of the requests was that we keep the Memo available for the users to see all the notes they had made without clicking on a list box to view each one individual note. We already need to rebuild the system. It is crashing left and right with data turning up missing each time.

I'm thinking it might be easier to just tell them this is not a wise feature and that the subform would make it easier.

If they want to store the individual notes maybe I can find a way to just detect the new note and add it to the individual note table.... I'm just throwing out ideas... I'm at a loss as to what to do with this database... not because of this issue but because it is really just fickle.

I will post you a subform that presents the data like the memo.
If you want to rebuild the database I will give you any help I can.
The note addition "system" is a bit complicated compared to just entering the data in to a table via a form.

Sorry, I can't get the subform to do it as the fields can't grow.
But I have been looking at String sizes and I have built the string to over 250,000 characters without any problems.
How large is your largest memo from your notes?

I just found something in the table design menu. It says that you can have an OLE object as a field type> So I could have a word document essentially be a field type? Do you know much about OLE Object Fieldtypes?

I think 250,000 chars is great. I don't think I have seen one over 85K.

Just assign the current big Memo to a string and then you can manipulate it with Instr, Left and Right.
I will create something for you, no point in trying something new until we have exhausted the memo route.
The problem with a word doc as an OLE is getting the data in to word, you would have to copy and paste it.

Sorry the customers are asking me to design a new database while they try to piece data together. we have the current working version but certain data entries are getting matched up with different properties and we are having trouble identifying what the cause is. So they continue to add data but are limiting their data to the essential pieces that are working.

I will try the above suggestion to manipulate the data. Thanks again for everything.

I got another system up that includes valid links directly to the issues that were missing so we are good to run.

I was thinking that with your method of adding the note to the memo... I could possibly incorporate that process directly into the cmdAddRec_Click method. Do the split left-right if the date was not the latest date. otherwise it adds it directly to the memo when the note is added. Then there would be no reason to update the entire memo at once.

I got the add and delete to work fine if the date is greater than the last one posted... but I'm having trouble coming up with clear logic on finding the date to actually split and insert the data.

Each new note is formatted as follows:
##/#?/####: AAA- BBB? vbNewLine
noteString(possibly any length of string)
vbNewLine & Form![frmMain[![Memo]

that way there is a break after each individual note. I'm looking for some built in code that would step through each character until it found a vbNewLine character.... Then I could simply pull the subsequent characters until I find ':'. That would give me the compare date and I could use that to determine the location of insertion. If I find it first I'll let you know how I came up with it. Otherwise any help is appreciated.

Is the vbNewline character the same variable representation of hitting the enter key? if so this might be a problem.

You have it right, the vbNewLine is the item to search for, each time you find it look for the date in the next 10 characters.

:beerchug: I discussed it with some of the other designers and I don't think it is necessary to sort the date. The note they enter will be inserted according to the actual date it was entered into the system. So if they need to reference a previous date they will do it in the notes section. Otherwise all additional notes will follow the structure you gave me where it is simply added to the top.

The delete function works great. And so does the Update probably the most problamatic because in my VB infancy I declared the variables in the sub. ie Dim newMemo, oldMemo, edtCriteria as String right at the top of my function. The flow of the edit process requires the button to be pushed twice once to unlock the Memo field and other locked inputs, and the second to save the data and lock the memo and other inputs... So the first time all variables were set... when I clicked the second time of course the variables were all reinitialized and set back to null.

I made the variables global and put a big comment box on them telling people not to mess with them... not that anyone touches Access but me... guess that's why they have interns. I'd say I'm done with this project for good. I still don't understand why it kept crashing when I tried to update the entire Memo at once... but it's been enough headache for one year. I'm calling this solution .. REALLY SOLVED> Thank you to OBP for your support and talk through.:whip

Good, vbNewLine wouldn't work.
Adding CHR$(186) before the date works well when you want to find it.

Thanks. You have been a great help.