Auto check a box

[JGG]

I'm looking to have a yes/no check box automatically checked if I add a date to another field (If Me.Date_Completed_ = whatever date it is Then Me.Completed = Yes) ...something like this is what it should look like (I think). What is the proper code to write to have what I need done? Please advise.

 

[boblarson]

That is storing redundant data. You don't need the checkbox. All you need is to check to see if the date field is null or not.

 

[boblarson]

Also, it violates normalization rules which state that a field should not be dependent upon any other field for its value.

 

[JGG]

:/ I know it's redundant (it was requested by someone who wanted a lot of extras (and who doesn't understand the word "no"), a completed check box as well as date completed being two of them). Sometimes, it can be checked (the check box) without the date field being filled out, however, if there is a date supplied, then the check box needs to be checked...is there a way to do that? Please advise (and if possible, start from the type of event, please).

 

[dbDamo]

In the AfterUpdate event of your Date_Completed control, you could use the following code:-

If Not IsNull(Me.Date_Completed) Then
   Me.Completed = -1
End If

However, this does not check for a valid date so I would perform validation on the date too. See this example:-

If Not IsNull(Me.Date_Completed) Then
   If Me.Date_Completed < Date()-30 Then
      MsgBox "Date Completed cannot be > 30 days before Today"
      Me.Date_Completed.SetFocus      
      Cancel = True
      Exit Sub
   ElseIf Me.Date_Completed > Date() Then
      MsgBox "Date Completed cannot be a future date"
      Me.Date_Completed.SetFocus
      Cancel = True
      Exit Sub
   Else 
      Me.Completed = -1
   End If
End If

 

[JGG]

It didn't work, could that be because it was created in Access 2002-03, and I'm now using 2007? Some of the other functions (auto checking one box should check a number of others depending on which is checked, worked while I was still running 2002, but now in 2007, it doesn't seem to be working). :/ frustrated.