Solved: Record Locked Error 3188

[Imdabaum]

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?

[Imdabaum]

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.

[Imdabaum]

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?

[Imdabaum]

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.

[OBP]

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.

[Imdabaum]

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.

[OBP]

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.

[Imdabaum]

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.

[Imdabaum]

Seriously I got it to work and today It's corrupted again. Is it bad programming>? 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.

[OBP]

Any possibility of posting or emailing a copy?

[Imdabaum]

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.

[Imdabaum]

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.

[Imdabaum]

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

[OBP]

Please email me some dummy tables.

[OBP]

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?

[OBP]

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.

[Imdabaum]

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.

[OBP]

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

[Imdabaum]

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.

[OBP]

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.