Getting Access to fill in a ‘date created’ field when a record is pasted in a DB

[Inchman]

Hello,

I have a daily operation that requires users to cut data from excel spreadsheets and paste it into an Access database. The problem is that I need Access to populate a ‘Date Created’ field in the database, but it doesn’t seem to do this when a record is pasted into the database. (Note, it works fine for manually created records in Access using the Now() function.)

How do I get Access to fill in a ‘date created’ field when a record is pasted?

I’ve searched the web (and this site) for an answer, but the results are always bogged down with simple answers about creating new records manually.)

Thanks

[burrina]

Try using the AfterUpdateEvent.

Me.SomeField = Date

HTH

[Inchman]

Thank you so much for the quick reply!
I've tried putting this in the form, but it doesn't work. Where should I be putting it? I'm sure I'm missing something simple here.

[June7]

I tried the DefaultValue property and code in the form AfterUpdate event - neither is triggered by the paste.

I think this will require an sql UPDATE action.

CurrentDb.Execute "UPDATE tablename SET fieldname = Date() WHERE fieldname Is Null;

or

CurrentDb.Execute "UPDATE tablename SET fieldname = Nz(fieldname, Date())

[burrina]

Using the OnCurrentEvent for your form, you can try this:

If IsNull(Me.YourFieldName) Or Me.newrecord = True Then
Me.SomeField = Date
End If

One additional thing: You will need to add on the Exit Event of the pasted field.

Me.Dirty = False

This worked for me.

[June7]

I doubt that will work any different from AfterUpdate. Pasting does not trigger events.

[burrina]

See above response.

[June7]

I knew I should have tested before speaking.

[burrina]

Hmm, you mean I'm not the only one who does that?