Track Date when Yes/No box is checked (1 Viewer)

[&Boom]

I've a list of Staff Info displayed on a form, with an 'Inactive' Yes/No check box (same as DoNotUse within the table). I need to track what date a check to one of the inactive boxes is made. I am not concerned with who did it, simply the date it was checked, and I need to display that date next to the checked selection.

Can anyone help me out here?

Thanks

 

[ypma]

If i have understood your requirement all you would require is a after update event .
Private Sub Check129_AfterUpdate()
If Me.Check129 = -1 Then
Me.Actiondate = Now()
End If

The above is if the box is ticked . If you required a date for either yes or no then Just :
me.actiondate = Now()
I have used Now() but you might prefer to use Date()

hope this of use
End Sub

 

[pbaldy]

Depending on your needs, you may also want an Else clause to set the date back to Null if they uncheck the box.

 

[&Boom]

Well I am attempting to make the change, but Access keeps saying I don't have enough memory, then locks the Subform I am trying to code behind...

Great.

 

[ypma]

Well I am attempting to make the change, but Access keeps saying I don't have enough memory, then locks the Subform I am trying to code behind...

Not sure what that's all about, Can you confirm you are opening the form in design mode and then right clicking the check box , click on properties ,then EVENTS . After update : event procedure ... enter the script . Using the correct name of the check box and the date/time field you have for entering the date checked.
Let me know how you get on

 

[&Boom]

Of, of course, ypma. The change took me 30 seconds to implement, however once I close VBA and click Save (for the subform), it locks up and MS Access shuts down.. Upon re-opening MS Access, that Subform became unavailable.. I know it isn't a problem with the code. I am wondering why it is saying I don't have enough memory?

 

[ypma]

Remember i am just trying guess whats wrong , I know you said you are happy with the script , but i am assuming you did not have a memory issue before you entered it . Have you tried removing it ?
If so did you still have the memory issue.
In my experience when using Access 2003 the memory issue was normally, because access does not like some coding or the formula was too difficult .
Without know you set up i am at a loss as how to assist you further, hopefully someone else can advise you
Regards

 

[&Boom]

I decided to delete that Sub form and re-make it. The only issue I am currently having is when I add the date to the "Actiondate" text box after the Inactive is checked, it is putting the date into all of the "Actiondate" text boxes.. How do I make sure it's only 1 at a time?

 

[pbaldy]

The textbox would have to be bound to a field in the table.

 

[&Boom]

Gotcha.. It was a linked table on a different drive, so I was seeing if there was a work-around. Obviously there wasn't, so I added the field on the drive and refreshed via linkted table manager. So now the code should work eh? Will let you know!

Thanks

 

[&Boom]

Okay, it works. As a Summary for any future references..

**I added a field in the StaffTbl -> "InactiveDate"
Went to the Subform, added a textbox (InactiveDateTxt) with control source of the InactiveDate field (also added it to the query)
Used the code :

If Me.CheckBox = -1 Then
InactiveDateTxt = Date()
Else
InactiveDate = ""
End If

I hope this helps someone in the future!

 

[pbaldy]

Glad we got it sorted out.