Add a date to a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AlastairO - 04 Mar 2008 17:03 GMT

I am wanting to add a CreateDate to a form which enters the date from the
computer system but doesn't update the next day.

Any helpers please!

Thanks

Alastair

Reply to this Message

Douglas J. Steele - 04 Mar 2008 17:06 GMT

I'm assuming you mean you want the CreateDate to be on a field in a table.
Simply putting a date on a form doesn't buy you much...

Set the field's DefaultValue to Date() (assuming all you want is the date),
or Now() (if you want both date and time). Whenever a new row is added to
the table, the field will pick up the default value. The Default Value will
have no effect on existing rows.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am wanting to add a CreateDate to a form which enters the date from the
> computer system but doesn't update the next day.
[quoted text clipped - 4 lines]
>
> Alastair

Reply to this Message

AlastairO - 05 Mar 2008 09:27 GMT

Hi Doug,

Thank you for your quick response. Looking at your answer, I can see what
you are getting at and I have realised that my question wasn't specific
enough (sorry). What I am doing is setting up a field (in a table) that puts
in that day's date when tick boxes from 7 fields are completed - a completion
date as it were. If the boxes aren't all ticked then the field is empty.

Hopefully this is more specific. Thanks for your help and time.

Alastair

> I'm assuming you mean you want the CreateDate to be on a field in a table.
> Simply putting a date on a form doesn't buy you much...
[quoted text clipped - 12 lines]
> >
> > Alastair

Reply to this Message

Douglas J. Steele - 05 Mar 2008 12:47 GMT

Put code in the form's BeforeUpdate event to check whether the 7 check boxes
are completed and whether no CompletionDate already exists. If so, set the
CompletionDate.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Doug,
>
[quoted text clipped - 30 lines]
>> >
>> > Alastair

Reply to this Message

Linq Adams - 05 Mar 2008 13:01 GMT

Yes, Alastair, asking for a "CreationDate" when you actually want a
"CompletionDate" is a little ambiguous! Here's two sets of code to do it. If
you ***only*** have 7 checkboxes on your form, in total

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim Counter As Integer

Counter = 0

For Each ctrl In Me.Controls
If TypeOf ctrl Is CheckBox Then
If ctrl = -1 Then
Counter = Counter + 1
End If
End If
Next

If IsNull(Me.DateCompleted) And Counter = 7 Then
Me.DateCompleted = Date
End If

End Sub

If, on the other hand, you have more than 7 checkboxes on your form, but are
only concerned with the magic 7 for you completion date, for each of those 7
you need to goto Properties - Other and enter "marked" (without the quotes)
in the Tag Property of each, then use this code instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim Counter As Integer

Counter = 0

For Each ctrl In Me.Controls
If TypeOf ctrl Is CheckBox Then
If ctrl.Tag = "marked" And ctrl = -1 Then
Counter = Counter + 1
End If
End If
Next

If IsNull(Me.DateCompleted) And Counter = 7 Then
Me.DateCompleted = Date
End If

End Sub

This assumes that once all 7 are checked you won't go back and unmark any. If
you want this possibility addressed you'd have to have more code to cover the
possibility.

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message