how to pass an old value field to another form

[ivonsurf123]

Hello,

Could you help me please? I have a problem with a if sentence and a query: I need to pass the POSITION.OldValue to another table but it is getting the new value instead.

Private Sub Position_BeforeUpdate(Cancel As Integer)

Dim str As String
    If Me.POSITION.OldValue <> Me.POSITION.value Then
              
            str = "INSERT INTO tbl_GCDS_Operations_Positions_fills " _
               & "([REPLACEMENT FOR],[POSITION],[POSITION NAME],[Snr Dir],[UNIT],[REPLACEMENT LAST DATE],[CompanyStartDate],[Position Status]) VALUES " _
               & "('" & Me![NAME] & "','" & Me![POSITION] & "','" & Me![POSITION] & "','" & Me![Reporting Level 1] & "','" & Me![Group] & "','" & Me![Leave Date] & "','" & Me![Company Start Date] & "','" & "Open" & "'); " _
            'Debug.Print str
            CurrentDb.Execute str
    End If

End Sub

 

[pbaldy]

You don't refer to OldValue in the SQL.

 

[ivonsurf123]

Thank you for the tip.

 

[pbaldy]

No problem. I assume you have it working now?

 

[ivonsurf123]

Yes , indeed, I did.

 

[pbaldy]

By the way, I'd probably use the before update event of the form instead of the control, in case the user changes their mind and either changes the value back to the original, or to another different position. That way you don't get unnecessary records.