Update table record based on calculations from related subform

[Guest]

I have a tab control form (recordsource is Contacts). One of the tab
controls has a subform (recordsource is Details). I want to update some
fields in the Contacts table from results calculated in the Details subform.

Sample code used for testing (added in the BEFORE UPDATE section for a field
in the Details subform):

Dim varContactTestNumber As Variant
varContactTestNumber = 5912
Dim varDetailTableContactNo As Variant
varDetailTableContactNo = Me.ContactNo
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Contacts", dbOpenDynaset)
With rs
.FindFirst "ContactID = " & varDetailTableContactNo
If rs.NoMatch Then
MsgBox "Record not found"
End If
.Edit
!ContactTestNumber = varContactTestNumber
!ContactTimeStamp = Now()
.Update
.Fields.Refresh
.Close
End With
Set rs = Nothing

This code updates the Contacts table, but when I try to go to another tab
control, I get the Write Conflict error with message "This record has been
changed by another user since you started editing it. If you save the
changes, you will overwrite the changes the other user made."

I know you’ve suggested adding a timestamp when this error occurs, but I
don’t this is the issue in this case. I think I understand why I’m getting
the error – because I already have the Contact record open via the Contacts
form and I’m trying to make the Contact record update through its Details
subform.

Is there any way to update the Contact record from calculations made in the
Details subform?

 

[Larry Daugherty]

Your real problem is something else: You should never store
calculated data. Every time you need to display the calculated
result, calculate it. Have a peek at www.mvps.org/access and read the
Ten Commandments. While there, look around the site at all the
goodies. All of the information posted there was contributed by
Access professional developers for the benefit of other Access
developers.

HTH