Check box and code to control it

[emcf]

I am trying to add a check box to a query that will indicate that a record is out of date and can then be archived by a query (to be done by a totally seperate procedure). I want to provide the user with a warning asking if they are sure that they want to do this - this I have done via the following code on mouse click on the check box (obtained from this site!)-

Private Sub Archive_Click()

If MsgBox("Are you sure you want to archive?", vbYesNo + vbQuestion, "Archive?") = vbYes Then
[DateArchived] = Date
Else
End If
End Sub

This code does not solve my problem though - I have two problems stemming from it:

1) If the user answers 'No' the check box still displays a tick - how do I stop a tick from being inserted when the user does not want the record to be archived?

2) If the check box is displaying a tick and the user clicks it the warning box is again displayed - how do I get the code to recognise that the box is already ticked and the warning need not be displayed?

I have tried playing about with the code but just seem to get myself tied up in knots!

Thanks in advance.

 

[Howlsta]

if me.yourcheckbox = "yes"
exit sub
else
If MsgBox("Are you sure you want to archive?", vbYesNo + vbQuestion, "Archive?") = vbYes Then
do what you've got to do
else
me.yourcheckbox = "no"
me.requery
end if
end if

HTH

 

[emcf]

Cheers Howlsta,

with some help from you and one of our highly paid developers the code is now -

Private Sub Archive_Click()

If IsNull([DateArchived]) Then
If MsgBox("Are you sure you want to archive?", vbYesNo + vbQuestion, "Archive?") = vbYes Then
[DateArchived] = Date
Me.Archive = "Yes"
Else
DateArchived = Null
Me.Archive = "No"
End If
Else
Me.Archive = "no"
DateArchived = Null
End If

Me.Requery

End Sub