Record Lock of Code problem?

[austris]

Hi,

It's single user .mdb (for now).
i've created a generic form for a user to add a note to existing note
in a memo Note field in a table (myTbl). The relevant part of the
code:

'
NoteText = ExsntNote & Chr(13) & Chr(10) & Date & " (" & GetUserName &
") " & NoteText

strSQL = "UPDATE " & myTbl & " SET " & myField & " = '" & NoteText & _
"' WHERE " & myIDField & " = " & myID

CurrentDb.Execu te strSQL, dbFailOnError 'Adds the note to the target
field

'

So, all the above does is it takes the ExsntNote (current note in the
memo field), adds line feeder &Return, date, usrename and the new
note. the structute of "NoteText = ....NoteText" is because the new
NoteText is actually coming from a different form. This all is just to
not let the user remove/edit existing note but just to add new notes
to the existing one but still store all of the notes in a single notes
memo field (i.e., not multiple records).
The code works fine untill it reaches certain limit (seems to be
around 3000 characters) - then it runs into record lock error:
"3188,
Could not Update; currenlty locked by another session on this
machine."

So, i was hoping that you could advise whether the code has reached
some kind of memory limits or any other limit and how to overcome the
promlem if i were to stick with the structure where the notes are
still stored in one record instead of being added as new records (rows
in the table) each time.

Thanks!

[Dominic Vella]

Re: Record Lock of Code problem?

Hi

On a side note, you could use the standard 'vbCrLf' instead of the 'Chr(13)
& Chr(10)'. Much easier to remember and read.

You'd probably be best to consider having a separate table (maybe called
mySubTbl) for all these additional notes, one that will reference with a
Foriegn Key to your 'myTbl' Primary Key. Using a Subform to add these
notes will make the development simple.


Dom


"austris" <austris.bahano vskis@gmail.com wrote in message
news:e4ec8b91-53f4-423f-9ae5-ed50100b03cc@d2 1g2000prf.googl egroups.com...

Hi,
>
It's single user .mdb (for now).
i've created a generic form for a user to add a note to existing note
in a memo Note field in a table (myTbl). The relevant part of the
code:
>
'
NoteText = ExsntNote & Chr(13) & Chr(10) & Date & " (" & GetUserName &
") " & NoteText
>
strSQL = "UPDATE " & myTbl & " SET " & myField & " = '" & NoteText & _
"' WHERE " & myIDField & " = " & myID
>
CurrentDb.Execu te strSQL, dbFailOnError 'Adds the note to the target
field
>
'
>
So, all the above does is it takes the ExsntNote (current note in the
memo field), adds line feeder &Return, date, usrename and the new
note. the structute of "NoteText = ....NoteText" is because the new
NoteText is actually coming from a different form. This all is just to
not let the user remove/edit existing note but just to add new notes
to the existing one but still store all of the notes in a single notes
memo field (i.e., not multiple records).
The code works fine untill it reaches certain limit (seems to be
around 3000 characters) - then it runs into record lock error:
"3188,
Could not Update; currenlty locked by another session on this
machine."
>
So, i was hoping that you could advise whether the code has reached
some kind of memory limits or any other limit and how to overcome the
promlem if i were to stick with the structure where the notes are
still stored in one record instead of being added as new records (rows
in the table) each time.
>
Thanks!

[austris]

Re: Record Lock of Code problem?

Thanks Dom!
I even didn't know that there was that standard line feeder (proving
how little i know...).
I kind-of worked around my problem by releasing the record from the
row but it's not a good solution though so i just might have to follow
your advise to split the memos up into multiple records vs one.

Thanks again for response.