FINISHED date

[KMEscherich]

Using Access '97

I have a sub-form on a main form that allows for 5 line items to be made against each PRIMARY KEY. On this sub-form, I have a FINISHED checkbox that the end-user is to select when finishing a row of data against the PRIMARY KEY. Once the end-user selects the FINISHED checkbox, the row of data locks and no one can make any changes.

Am wondering if there is a way to capture a SYSDATE each time someone selects the FINISHED checkbox and record that date in a table so I can report against the FINISHED dates at a later date.

If you use DATE() then the date will fluctuate each time there is an entry. I need to be sure the FINISHED DATE be locked in so there will be no changes to the FINISHED DATE.

Can someone please help me with this??

Thank you.

[NeoPa]

It's hard to answer that without any idea of your data structure, but as a general idea, set a field in your table to the current value of Date() when the CheckBox is checked. You would need to put code in an AfterUpdate event procedure for it. If it's possible to UNcheck it, you need to handle that as well.

[KMEscherich]

It's hard to answer that without any idea of your data structure, but as a general idea, set a field in your table to the current value of Date() when the CheckBox is checked. You would need to put code in an AfterUpdate event procedure for it. If it's possible to UNcheck it, you need to handle that as well.

Thank you for getting back to me. I tried this and it seems to not pull in a date.

Private Sub FINISHED_AfterUpdate()
    If Me.FINISHED Is NotNull Then Me.DATE_FINISHED = CurrentDate
    End If
End Sub

ME.FINISHED = Checkbox
ME.DATE_FINISHED is where I want to see a date.

I created a field in the table also.

Can you show me how to correct this code??

Thank you.

[missinglinq]

Not to be unkind, but if you want to use Visual Basic for Applications, you need to study the language before you start writing. Syntax from other programming languages, often including straight Visusal Basic, cannot be used! Is NotNull is not valid in VBA, you have to use Not IsNull. Neither is CurrentDate. In VBA you would use Date() for just the date, or Now() if you want the date and time.

The following code will accomplish your goal! It will set Date_Finished to the current date when Finished is checked, and clear it if Finished should be unchecked.

Private Sub Finished_Click()
If Me.Finished Then
  Me.Date_Finished = Date
Else
 Me.Date_Finished = ""
End If
End Sub

If you copy and paste this code and the line numbers and periods appear in your code, be sure to delete them!

Good Luck!

Linq

[NeoPa]

Linq's code should work fine for you, but I'd do it slightly differently. You choose :)

Private Sub FINISHED_AfterUpdate()
    Me.DATE_FINISHED = IIf(Me.FINISHED, Date(), "")
End Sub