Access 2k prevented from saving data to a field

[bryn30]

Goal: create a status History table for Intervals

Reason: Status changes all the time, Need to track the interval in days that a order was within each status.

I have changed some object names to easier understand what I am trying to accomplish, here is what I have so far:

Running code produces an Error:
"The Macro or Function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field"


Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate

If Me.CurStatus <> Me.NewStatus Or Me.CurStatusDetail <> Me.NewStatusDetail Then
UpdateHistoryTable
Else
End If

Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate
End Sub

Sub UpdateHistoryTable()
On Error GoTo Err_UpdateHistoryTable
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;StatusHistory&quot ' Change name appropriately

With rs
.AddNew
' Here is where you would specify what fields you want to add to your second table.
![OrderID] = Me.OrderID
![Status] = Me.CurStatus
![StatusDetail] = Me.CurStatusDetail
![StatusBeginDate] = Me.CurStatusDate
![StatusEndDate] = Date
![FullStatusName] = Me.CurStatus & &quot;, &quot; & Me.CurStatusDetail
![StatusInterval] = Date - Me.CurStatusDetail

.Update
.close
End With
UpdateCurrent

Exit_UpdateHistoryTable:
Exit Sub

Err_UpdateHistoryTable:
MsgBox Err.Description
Resume Exit_UpdateHistoryTable
End Sub

Sub UpdateCurrent()
On Error GoTo Err_UpdateCurrent
Me.CurStatus = Me.newStatus
Me.CurStatusDetail = Me.NewStatusDetail

If Me.StausDate > Me.StatusDetailDate Then
Me.CSTATUSDATE = Me.StatusDate
Else
Me.CSTATUSDATE = Me.StatusDetailDate
End If

Me.Refresh

Exit_UpdateCurrent:
Exit Sub

Err_UpdateCurrent:
MsgBox Err.Description
Resume Exit_UpdateCurrent

End Sub

 

[RickSpr]

Your UpdateCurrent procedure is attempting to update bound fields, but because it's being called (indirectly) from within the AfterUpdate procedure, the update of the record has already been completed. Access won't let you change the data, because that would cause an endless loop of updating.

From the control names you're using, I'd guess that the &quot;new&quot; values are unbound text boxes that you're using in order to have access to both old and new values for saving in the history table. I think you've missed the right way to do this. You should let the user update the actual bound field. Most controls have an &quot;OldValue&quot; property that retains the prior value until the update cycle is complete; that's where you get the &quot;current&quot; value from for your history, while the &quot;new&quot; value is the one in the control's Value property.

Unfortunately, that means redesigning your form almost from scratch. But that's the right way to do this. Rick Sprague

 

[bryn30]

Thanks Rick...
I am a newbi at this, I will rework the form and give it a try using the OldValue property.

 

[bryn30]

Rick,
Thanks for your help pointing me in the new direction. it works beautifly.
here is the final code with many thanks to Rick Sprague!

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;StatusHistory&quot
With rst
.AddNew
![OrderID] = Me.Text757
![Status] = Me.cbx2.OldValue
![StatusDetail] = Me.cbx1.OldValue
If Me.STA_DT.OldValue > Me.STA_DTL_DT.OldValue Then
![StatusBeginDate] = Me.STA_DT.OldValue
Else
![StatusBeginDate] = Me.STA_DTL_DT.OldValue
End If
![StatusEndDate] = Date
![FullStatusName] = Me.cbx2.OldValue & &quot;, &quot; & Me.cbx1.OldValue
![StatusInterval] = Date - ![StatusBeginDate]
.Update
End With
rst.close
Set rst = Nothing
Set db = Nothing