Ms Access 2016: validation and create duplicate record when not null

[akika]

hi,


In access2016,
How can i create a duplicated record in the main DB table if my field for 'date_closed' is not null.

Contact_ID	Contact_LastName	Status	Contact_Tel	date_closed
1	Smith	Rejected	contact tel test 1	01/09/2018
2	Paul	Complete	contact tel test 2
3

And also i have below validation that should be raised only when field 'status' is set rejected and date_closed is not null.

Private Sub contact _name_BeforeUpdate(Cancel As Integer)


If IsNull(DLookup("[contact Name]", _
"Database", _
"[contact Name] = """ & Me.contact_name.Text & """")) = False Then
Cancel = True
MsgBox "contact Name already exists", vbOKOnly, "Warning"
Me![contact Name].Undo
End If
End Sub

[June7]

Don't use .Text property. Should be .Value but since .Value is default, no need to type it.

Why create 'duplicate' record? When do you want this duplication to take place?

[akika]

Don't use .Text property. Should be .Value but since .Value is default, no need to type it.

Why create 'duplicate' record? When do you want this duplication to take place?

Hi,
- To maintain a kind of history record (original record and amended record)..
- Duplication of record should occur when field 'status' is set rejected and date_closed is not null. then all the data for that record should be replicated.

Any suggestion pls?

Contact_ID	Contact_LastName	Status	Contact_Tel	date_closed
1	Smith	Rejected	contact tel test 1	01/09/2018
2	Paul	Complete	contact tel test 2
3

[June7]

If you want an audit trail functionality, review http://allenbrowne.com/AppAudit.html

Otherwise, to create record in data table, need code in some event, perhaps date_closed AfterUpdate.

And if validation needs to be raised only under the described condition then use an If Then structure.