Access97 - Update Memo Field 2

[SHardy]

I wasn't sure if I should put this in the queries or forms forum, so thought this may be better. Is there a way I can crosspost?

Anyway, I have a database that is used for call logging and management. The main table that keeps the request data ("tblRsq") has a "ProbDis" field that is a "Memo" field.

Form one of the support staff forms based on this table, they can select an open request, review it and add entries to the log (the "ProbDis") field. This is done by entering the text into an unbound control called "TxAddLog". A command button then runs an append query to add the text to the memo field, along with some other information.

All seemed to be working OK. That is until I got a request with quite a lot of text. The original entry into the "ProbDis" field has over 3,000 characters. However, I didn't think that this should be a problem, as a memo field allows upto 64,000 characters. However, when I try to add a log, nothing gets appended to the field contents. When I stopped suppressing messages, it correctly said that I would be updating contents in one record, but still nothing. When I amend the query to allow me to run it manually, it appended OK. ???

The code used is as follows:

On Error Resume Next

If Len(Me.TxAddLog) > 500 Then
MsgBox "Text is too long. Delete some text from the diary entry and re-submit.", vbCritical, "QBE Bromley Support"
Me.Undo
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "updateLog"
TxAddLog = ""
Me.Refresh

The query is as follows:

UPDATE TblRsq SET TblRsq.ProbDis = [probdis] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Log by " & [forms]![frmmanwip]![suppname] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "** Log added " & "on the " & Date() & " at " & Time() & " " & "Log number " & [lognumber]+"1" & " **" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [forms]![frmmanwip]![txaddlog] & Chr(13) & Chr(10) & Chr(13) & Chr(10), TblRsq.LogNumber = [LogNumber]+1
WHERE (((TblRsq.RsqID)=[forms]![frmmanwip]![rsqid]));


Any suggestions as to why this problem maybe occuring?

Thanks,
S

 

[SHardy]

Just tried removing the "On Error Resume Next". Get the following:

Run-time error '3188':
Couldn't update; currently locked by another session on this machine.

However,

No other session open.
No one else wil be viewing this record.
No message on other requests with shorter text.

???

 

[AvGuy]

Is your approach correct? I mean why put all the comments into a memo field? Wouln't it be better to create a table of comments that links to the problem so you can tell who made the comment and when?

 

[SHardy]

Yes, that's probably the way that I would have done it. However, this database was created by my predecessor, and I have taken it over. It wasn't really appropriate to completely rebuild it, as it was already in use.

Don't really understand this problem though, as that amount of text should be handled OK.

 

[Hap007]

Dear SHardy,

I would have proceeded is a different way, but that is the beauty of programming, there are many solutions to the same problem.

I would guess that your own input form has the record in questioned locked, so you need to release that record before you actually fire off your update query.

Also, access does not lock a single record. When access locks a record, it actually locks a 'block'. so if 3 records are physically located on a block, then whenever anyone of those records are locked, all 3 are locked. So, sometimes it can be confusing, but this rarely happens.

Good Luck,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[TTkeithy]

Not sure if this will help or hinder, but this message seems to appear if there is a general error in the memo field in question. I have been getting it on a database that I've been trying to compact ( hence my coming across this post) and it is a memo field that cannot be accessed and fires up this datalock message. ( why would we expect a microsoft error message to be meaningful ...)

You may have an error on the record before you are trying to write to it. The only way past it I have found is to delete the record.

HTH

Rich

 

[SHardy]

Sorry, I didn't realise that I hadn't updated this posting.

I did solve this in the end. It would appear that when the record is small enough (under 2000 characters) Access must copy the record's dat ainto the form. However, when the memo field holds over 2000 chars, it is too big to copy and links directly to the table, locking the record in the process.

To get round this I had to make the memo field unbound and run code to update the contents each time an action occurred, so that it would reflect the current record.

I think this is what Hap suggested also.

Thanks All.

 

[G4U]

I can I view the text in a Memo field using an Acess database. This line doesn't work:
Memo.Lines.Text:=Table2.FieldByName('Notes').AsString;

But it worked fine on a Paradox table! I'M using an ADOTable in Delphi 6.

 

[G4U]

How can I view the text in a Memo field using an Acess database. This line doesn't work:
Memo.Lines.Text:=Table2.FieldByName('Notes').AsString;

But it worked fine on a Paradox table! I'M using an ADOTable in Delphi 6.

 

[SHardy]

I think this question would probably be better in the Delphi forum, rather than MS Access.

I have touched on Delphi some time back, but not enough to advise I'm afraid.