If field value has changed allow an audit - if not allow form to c



Some will have objections to what I am doing and call it messy - but anyway.

If a field has had its value changed I want to be able to continue with the
audit if it has not then - allow the form to close without saving.
So I have added the following to the forms beforeUpdate.

The two fields that I want to check are numeric
Me!Eng_Cost and Me!Ext_Cost
I have tried the .Dirty and it is not intended to work here.

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Confirm with user that this record is to be modified

Dim updRecord As Byte

updRecord = MsgBox("Confirm - That you are making a record change",
vbOKCancel, "Record Modification")

If updRecord = vbCancel Then

'Cancel = True
DoCmd.Close acForm, "forms.frmWCard_Edit"
Else 'yes

' first check if Eng_Cost and or Ext_Cost have changed and if so allow

'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.

bwasNewRecord = Me.NewRecord
Call AuditEditBegin("tblWorksCard", "audTmpWorksCard", "WCard_ID",
Nz(Me!WCard_ID, 0), bwasNewRecord)

End If
End Sub

Thanks for ideas and comments.



John Spencer

I'm not sure what your question is. I think you ant to know if the values in
the two fields have changed or not? If so, try using the OldValue property of
the controls.

If Me.EngCost = Me.EngCost.OldValue then
'No Change
End IF

You might want to expand that to test for nulls - assuming that -999 is never
a value that will be entered into the field you could use.

If Nz(Me.EngCost,-999) = NZ(Me.EngCost.OldValue,-999) THEN
'No Change

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question