Create a history /Log table based on status change

Question

So my client wants is If a user changes the status of the case then a log needs to be generated in a history table. Basically, a new history record needs to be made in the history table as soon as the case status is changed everytime.

So the history table new record needs to based on only status Column. How can I achieve this

Answer 1

After lot of digging, i was able to find the solution. I know its not much work...But I am fairly new to the VBA like very basic...This is a small win for me :D

Private Sub Form_beforeUpdate(Cancel As Integer)
Dim ststatus As String
Dim ststatus1 As String

ststatus = Me.Case_Status.OldValue
ststatus1 = Me.Case_Status
    If ststatus <> ststatus1 Then
        DoCmd.RunSQL "Insert Into case_history select * from cases " & _
        "Where case_id=" & Case_ID
    End If
End Sub