Problem to Saving Text Field in Form Using Code (1 Viewer)

[mattcdse]

Hi all,

In the past in had the usuall bug trying to save data from a text field using a query only to find it has only saved the first so many (256?) characters from the field, truncating the rest. I managed to find some code to overcome this. I now however have a form with two text fields in it. Naturally i thought simply duplicating the code and changing the appropriate bits would solve the problem. Which it did except for one nagging issue. The data is saved perfectly, the form even requeries itself afterwards and displays the updated data, but for some reason Access displays the message "Run time error '3188': Could not update, currently locked by another session on this manchine". When i press the debug button, the line rs.edit is highlighted in yellow.

Any ideas how to get around this - code below

Private Sub btnSave_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strComments As String

'Code to Turn off the warning re runing a query.
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateSoftImg"
DoCmd.OpenQuery "qryUpdateSoft"

'Update Comments Field
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSoftware", dbOpenTable)
rs.Index = "Software Code"
rs.Seek "=", Me.SoftwareCodeNew
Forms!frmEditSoft!CommentsNew.SetFocus
strComments = Me.CommentsNew.Text
rs.Edit
rs!Comments = strComments
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

'Update Known Issues Field
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSoftware", dbOpenTable)
rs.Index = "Software Code"
rs.Seek "=", Me.SoftwareCodeNew
Forms!frmEditSoft!KnownIssuesNew.SetFocus
strComments = Me.KnownIssuesNew.Text
rs.Edit
rs!KnownIssues = strComments
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

'Code to Turn the warnings back on.
DoCmd.SetWarnings True

'Reqery the form
Me.Requery

End Sub

 

[allan57]

Change the field in your table from a Text field to a Memo field, this allows you to save data upto 65,535 chars, then would would not need the code.

 

[mattcdse]

Whoops sorry,

I should have made it clear that the field is a memo field. The code wouldn't work anyway if that had been the case and that wouldn't explain the error I'm getting either.

Cheers,

Matt

 

[neileg]

I'm not much good with code but it strikes me you are seacrhing the record set twice for the same record. Why not do it once and set the value of both fields?

 

[mattcdse]

Thanks for the suggestion Neil,

I assumed (probably wrongly) that the section of code:

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

would release control of the record. Therefore the second piece of code that updates the second memo field would be starting from a clean slate. I guess this must be wrong but it would be nice to have some clarification.

How would I set the value of both fields?

Cheers,

Matt

 

[mattcdse]

Ta daaaa!

Ok, so the problem lay elsewhere but is not obvious. The code is perfect. Well, it works anyway! The problem was this. The memo field for Known Issues was bound to an item in a table where as the Comment fields wasn't. In the form I simply used a macro to the value of the Comment field from a seperate bound text box that was bound to the corresponding item in the table. Therefore when the update happens, Access sees the unbound control, therefore doesn't see the record as locked and hey presto, all is updated without a moan.

Hope this helps people with similar 3188 errors

Cheers,

Matt