Write Conflict

[Rick Brandt]

I have a bound form that I use to input records with. The only field is a Memo
Field. This field is attached to tblReceipe, This table has only one record
in it. And will always only have one record in it.
After I write the contents of that record to another table I want to change
the contents of that record in the tblReceipe table to " ".
But I keep getting the write conflict message. Basically the problem is that
when I open the input form I need that memo field to appear blank.

Dim AboutSQL As String
Dim MemoSQL As String

DoCmd.SetWarnings False
AboutSQL = "UPDATE tblBostonItems SET tblBostonItems.BostonAbout =
Forms!frmBSBostonInput!MM " & _
"WHERE tblBostonItems.BostonCat = Forms!frmBSBostonDrinks!TxtKatID " & _
"AND tblBostonItems.BostonID = Forms!frmBSBostonDrinks!TxtDrinkID;"
DoCmd.RunSQL (AboutSQL)

MemoSQL = "UPDATE tblReceipe SET tblReceipe.MM = "" "";"
DoCmd.RunSQL (MemoSQL)
DoCmd.SetWarnings True

DoCmd.Close acForm, "frmBSBostonInput"

MM is the memo field.

Why do you have this table that the form is bound to? Why not just use an
unbound form? Or...why not just bind the form to the table you actually intend
to update instead of a different one?

 

[DS]

I have a bound form that I use to input records with. The only field is
a Memo Field. This field is attached to tblReceipe, This table has
only one record in it. And will always only have one record in it.
After I write the contents of that record to another table I want to
change the contents of that record in the tblReceipe table to " ".
But I keep getting the write conflict message. Basically the problem is
that when I open the input form I need that memo field to appear blank.

Dim AboutSQL As String
Dim MemoSQL As String

DoCmd.SetWarnings False
AboutSQL = "UPDATE tblBostonItems SET tblBostonItems.BostonAbout =
Forms!frmBSBostonInput!MM " & _
"WHERE tblBostonItems.BostonCat = Forms!frmBSBostonDrinks!TxtKatID " & _
"AND tblBostonItems.BostonID = Forms!frmBSBostonDrinks!TxtDrinkID;"
DoCmd.RunSQL (AboutSQL)

MemoSQL = "UPDATE tblReceipe SET tblReceipe.MM = "" "";"
DoCmd.RunSQL (MemoSQL)
DoCmd.SetWarnings True

DoCmd.Close acForm, "frmBSBostonInput"

MM is the memo field.
Thanks
DS

 

[DS]

This seems to work.

Forms![frmBSBostonInput].Dirty = False
Dim AboutSQL As String

DoCmd.SetWarnings False
AboutSQL = "UPDATE tblBostonItems SET tblBostonItems.BostonAbout =
Forms!frmBSBostonInput!MM " & _
"WHERE tblBostonItems.BostonCat = Forms!frmBSBostonDrinks!TxtKatID " & _
"AND tblBostonItems.BostonID = Forms!frmBSBostonDrinks!TxtDrinkID;"
DoCmd.RunSQL (AboutSQL)

Forms!frmBSBostonInput!MM = " "
Forms![frmBSBostonInput].Dirty = False
DoCmd.Close acForm, "frmBSBostonInput"

This way I get the new record saved and then it's cleared for the next
input. Any comments welcomed.
Thanks
DS

 

[DS]

Why do you have this table that the form is bound to? Why not just use an
unbound form? Or...why not just bind the form to the table you actually intend
to update instead of a different one?

Well Rick talk about taking the long way around, the problem that set
this whole thing off was that I was inputing on an unbound form into an
unbound textbox, but it was acting weird. So low and behold while
building this complicated fix I discovered the problem of my misery was
the OnEnter property. it was set to default, I switch it to start new
line, and I am once again back to using the old form. Funny how little
things get by you!
Thanks
DS