checkbox code to autofill date and once date hit unchecks itself

[Guest]

Hi.

I've got a "doozie" for you. I'm not sure this can be done, but I figured
you guys would know if it could or not! So here it goes --

I'm looking to use a checkbox to do 2 things. It's overall purpose would be
to automatically notify, via a query, when a specific record needs to be
reviewed and updated out of potentially 100s of records. In more detail:

-when checked (manually), would autofill a "updatedue" field (date format)
with a date exactly 2 weeks from when the box is checked.
-when that date that is autofilled comes, the code would trigger the
checkbox to automatically uncheck and delete the "updatedue" field, thus
making it via a query pull for review.

This process would repeat until another checkbox is checked that marks the
record "closed", shuts down the code on that record from happening again, and
moves the record to an archive table while deleting it from the main table;
which I'd also need help in building the code for.

Pretty lofty goal, but I think it can be done. Not a clue on how to do it
though. Any ideas?

bluezcruizer

 

[Douglas J. Steele]

Is this a continuous form or not? With a continuous form, you can't do it,
since presumably the checkbox would be unbound (since it would be a mistake
to store it in the table).

On a non-continuous form, you'd put code in the AfterUpdate event of the
check box to set the UpdateDue value:

Private Sub MyCheckBox_AfterUpdate()
If Me.MyCheckBox = True Then
Me.UpdateDue = DateAdd("ww", 2, Date())
Else
Me.UpdateDue = Null
End If
End Sub

To uncheck it, you'd put code in the form's Current event:

Private Form_Current()
If IsNull(Me.UpdateDue) = False Then
If Me.UpdateDue <= Date() Then
Me.UpdateDue = Null
Me.MyCheckBox = False
Else
Me.MyCheckBox = True
End If
Else
Me.MyCheckBox = False
End If
End Sub

 

[Guest]

Thank you Doug!

I'm still in development, and had not yet decided if I'd make it continuous.
So, I guess it won't be continuous. )

I do have a couple questions though -- first, for my learning experience,
why would making the checkbox bound to the table be a mistake? Would having
the checkbox unbound cause a problem in the query that requires that checkbox
in order to pull the records I'm looking for?

The second question is, how about the final checkbox request? Any ideas on
that one as well?

Thanks Doug! I really appreciate this!

bc

 

[Douglas J. Steele]

The checkbox's value is strictly dependent on the value of the textbox. That
means it would be a violation of normalization principles to store it.