3 Check Boxes 3 Date Fields

[KMEscherich]

Microsoft Access 2003


Hi there, am stuck with something that I am not sure on how to get done. I am attempting to have 3 check boxes and have 3 date fields. I need to have each date field be populated with an independent date when the end-user clicks on each of the 3 check boxes.

CB_STARTED
STARTED_DATE

CB_COMPLETED
COMPLETED_DATE

CB_CLOSED
CLOSED_DATE

I have used an After Update function at the form level, but it seems that when I place a checkmark in the 2nd check box, then both fields appear with the latest date. Is there a way to have a unique date appear for each of the 3 check boxes? You see, I can place a checkmark in the first check box today, then tomorrow, I can place a checkmark in the 2nd check box and so on. Because of this, I need to ensure that each date field will have their own unique date.


[CODE = VBA]
Private Sub Form_AfterUpdat e()
If Not IsNull(Me.CB_ST ARTED) Then
Me.STARTED_DATE .Value = Now()
End If
If Not IsNull(Me.CB_CO MPLETED) Then
Me.COMPLETED_DA TE.Value = Now()
End If
If Not IsNull(Me.CB_CL OSED) Then
Me.CLOSED_DATE. Value = Now()
End If
End Sub
[/CODE]


Thank you VERY MUCH for your assistance

[DonRayner]

Try changing your code to this

Private Sub Form_AfterUpdate()
If Not IsNull(Me.CB_STARTED) and IsNull(me.started_date) Then
Me.STARTED_DATE = Now()
End If
If Not IsNull(Me.CB_COMPLETED) and IsNull(me.completed_date) Then
Me.COMPLETED_DATE = Now()
End If
If Not IsNull(Me.CB_CLOSED) and IsNull(me.closed_date) Then
Me.CLOSED_DATE = Now()
End If
End Sub

[missinglinq]

There's two problems with the code presented so far.

If Not IsNull(Me.CB_ST ARTED)

checks to see whether the checkbox is Null, but it doesn't check to see if the checkbox is actually ticked! The user could have ticked the box, then realize that they made a mistake, and un-tick the box. The checbox is no longer Null, but it isn't ticked either.

Also, there are very few instances where the Form's AfterUpdate event is appropriate; the record is already SAVED by the time this event fires. This kind of code usually goes in the control's AfterUpdate event, like this:

Private Sub CB_Started_AfterUpdate()
 If Me.CB_Started = -1 Then
   Me.Started_Date = Date
 Else
   Me.Started_Date = Null
 End If
End Sub

Notice that this code also deals with the situation I described above, where the user accidentally ticks the box (populating it with a date) and then un-ticks it. This code removes the date if this occurs.

Simply modify this for each of your checkboxes with the appropriate names.

If you only want the date, without the time, it's better to use Date() rather than Now(). This saves you from having to format the value later to only show the date.

Linq ;0)>