"Record has been changed by another user" mystery

[BruceM]

I have a Purchase Order form with a LineItems subform (record sources are a
main PO table and a LineItems child table). The line items are selected
from a combo box based on a products table (tblPO_Product). The information
includes ProductCode, ProductDescription, Unit (Each, Case, etc.), and
UnitPrice. There need to be the ability to edit the Unit on the fly.
Sometimes the unit information is incorrect ("Each" where it should be
"Case", or something like that). If it reads "Each" and the user changes it
to "Case", a message appears asking whether the item is to be changed for
all records or for this record only. This is the code in txtUnit, the text
box bound to the Unit field.

It works, but not the curiosity in the lline after the asterisks: vbYesNo,
with vbOK as the response. I stumbled upon this as I was attemting to get
rid of a "This record has been changed by another user" message. When I use
it as is the code works as intended. If I change it to vbYes (the "correct"
wording) I get the error message.
I have similar code to change the unit price, with no problems. I can post
it, if it helps.
I guess I could use Cancel = True rather than Exit Sub shortly after the If
Err line.
Anyhow, this has me stumped. I am the only user, but am getting the error
message unless I do this weird bit of coding.

Private Sub txtUnit_BeforeUpdate(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUnit As String, strDescr As String, strMsg As String, strTitle As
String

Set db = CurrentDb
strUnit = Me.txtUnit.Text
strDescr = Me.cboDescription.Column(0)
strMsg = "Do you want to change the Unit to """ & strUnit & """" & vbCrLf
& _
"for future selections of " & strDescr & "?" & vbCrLf & _
"(Click ""No"" to apply the change to this record only.)"
strTitle = "Change Unit Description?"
' ******* Anomaly here
If MsgBox(strMsg, vbYesNo, strTitle) = vbOK Then
Set rs = db.OpenRecordset("tblPO_Product", dbOpenDynaset)
On Error Resume Next
rs.MoveFirst
rs.FindFirst "ProductID = " & Me.cboDescription
rs.Edit
rs!ProdUnit = strUnit
If Err Then
MsgBox "Error #" & Err.Number & " (" & Err.Description & _
") txtUnit_BeforeUpdate in frmPO, fsubPO_Items"
rs.Close
Exit Sub
End If
rs.Update
rs.Close
End If

End Sub

 

[BruceM]

It works now. I was storing UnitPrice in the LineItems table since it is
subject to change over time, but the value at the time the Purchase Order
was created needs to be stored. However, I did not have a field to store
the Unit in the LineItems table since I had assumed it was not subject to
change. FWIW, I don't think it's a good idea to change it on the fly, but
that doesn't count for anything. The Unit text box was bound to the Unit
field in the Products table, and the text box was locked. When the user
selected a Product, the Unit associated with that product would appear in
the locked text box. However, since the Unit is subject to change, I need
to store the Unit in the LineItems table, which means I needed to create a
field which Unit would be stored as part of the LineItem.
It makes sense that I would be warned about updating Unit in the Product
table, since the change would apply to all LineItems records involving that
product, including old records. Too bad the error message didn't have
anything whatever to do with the facts of the matter.
Also, the vbYesNo and vbOK anomaly has gone away. I may never know what
that was all about.