Insert Current Date Into Field

[THenry]

I have set the default value of my text box to be my current system date by setting its control source as Date().

But I want to enter this date into a field in a table. The problem is, since the control source is already defined as Date(), how can I set the control source to my field's name to enter the value into my field?

 

[dcx693]

If you set the default value to be Date() and the controlsource to be the name of the field in your table, the date should be stored, assuming you save the record.

 

[Idjit]

dcx's suggestion is the way to go, provided that you always want to store the date that the record was created.

 

[dcx693]

Idjit, the default value is only input into the field the first time the record is edited - and before it ever gets saved. Once the record is saved or the field is even edited before a save, the default value will no longer be placed into the control. You can replace it with whatever value you want.

 

[Idjit]

dcx,

Sorry my post wasn't clear. I understand how default values work. What I meant was that the method you described is the best way to go if one always wants to store the date the record was created. If one wants to store, say, the date the record was last modified and have this field automatically update each time the record is changed, this won't work because as you said once there is a value in the field it won't be changed by the default setting.

I was trying to clarify something but unfortunately ended up muddying the waters instead!

 

[THenry]

Hmmm... no go...

I've set the default value as Date(), but it does not even show up in the text box.

I need the value to be shown in the textbox, as the date where the record was last modified, and automatically inserted into a field in my table.

Please help, thanks in advance

 

[Rich]

Use the Before Update event of the form, Me.SomeField = Date or Me.SomeField=Now if you want the time included

 

[THenry]

Use the Before Update event of the form, Me.SomeField = Date or Me.SomeField=Now if you want the time included

Thanks, that worked.

But it only works when I edit some data in my main form... Is there anyway to update the date when I edit data in the subforms too?

 

[Mile-O]

Is there anyway to update the date when I edit data in the subforms too?

Forms!frmMyForm!SourceObjectName!ctlMyControl = Date()

 

[THenry]

Forms!frmMyForm!SourceObjectName!ctlMyControl = Date()

I'm an amateur in this... do not get what you mean exactly...

If my field name is DateStamp and my form is called Form, what's the codes?

 

[Mile-O]

And, you have a subform (you said.)

What is the name of the subform on your main form?

Currently, we have:

Forms!Form!SourceObjectName!DateStamp

SourceObjectName is replaced by the name of your subform.

 

[THenry]

Oh yes... I have a couple of subforms in fact...

Subforms: Contact, Education, Employment, Language, NS, Professional, Reference, Skill

My form is called Main actually, not Form, as I said in the previous post.

 

[Mile-O]

e.g.

Forms!Main!Contact!DateStamp = Date()

 

[THenry]

Hmmm... no go... field remains empty after editing some data in contact subform...

By the way, my codes are:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Forms!Main!Contact!DateStamp = date

End Sub

 

[Mile-O]

Is Contacts the name of the form that is used in the subform object or the name of the subform object itself. You should use the name of the subform object.

 

[THenry]

Pardon me, but I do not get you, how do I check?

 

[Mile-O]

Examine this example thoroughly:

 

[THenry]

Yes, my subform object and name are both called Contact.

When I edit the data in Contact subform, the DateStamp is not updated.

But when I edit the data in Main form, an error pops up, saying that Access can't find the field 'DateStamp' in my expression.

This happens using the below codes:

Forms!Main!Contact!DateStamp = Now

But the timestamp gets updated properly when I use the below codes:

Forms!Main!DateStamp = Now

 

[Mile-O]

Are you trying to reference a field in the subform or a textbox bound to a field? Go with the latter - hide the textbox, if necessary.

 

[THenry]

The textbox is bounded to a field called DateStamp in my table...