Automatically check a Yes/No field based on another field

[jlo]

I have a date field [DateResolution] and if a user enters a date in this
field, I would like the yes/no field [IssueClosed] checked automatically.

Can this be done?

 

[Douglas J. Steele]

Private Sub DateResolution_AfterUpdate()

If Len(Me.DateResolution & vbNullString) > 0 Then
Me.IssueClosed = IsDate(Me.DateResolution)
Else
Me.IssueClosed = False
End If

End If

 

[Allen Browne]

Use the AfterUpdate event procedure of the DateResolution check box on your
form to set the value of the yes/no field:

Private Sub DateResolution_AfterUpdate()
If Not IsNull(Me.DateResolution) Then
Me.IssueClosed = True
End If
End Sub

Before you do that, is the IssueClosed field really needed in your table?
Could there be times when an issue is closed, but the DateResolution field
would be validly null still? If the date in DateResolution was deleted,
should the issue be considered open again?

A better solution might be to drop the IssueClosed field from your table,
and create it in a query so it can never be wrong. Just type this expression
into the Field row in query design:
IssueClosed: ([DateResolution] Is Not Null)
You can use this query anywhere you would have used your table, and it's
always right.

More info about when and how to do this kind of thing:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Stefan Hoffmann]

hi,

I have a date field [DateResolution] and if a user enters a date in this
field, I would like the yes/no field [IssueClosed] checked automatically.

Use the On Change event of your date control:

Private Sub txtDateResolution_Change()

Me![IssueClosed] = True

End Sub

Or better the Before Update event of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![IssueClosed] = Not IsNull(Me![DateResolution])

End Sub

Or if an issue is always closed when a resolution date is give:

SELECT
NOT IsNull(DateResolution) AS IssueClosed
FROM
Issue


mfG
--> stefan <--

 

[jlo]

Thank you for all the responses. Worked like a charm!

hi,

I have a date field [DateResolution] and if a user enters a date in this
field, I would like the yes/no field [IssueClosed] checked automatically.

Use the On Change event of your date control:

Private Sub txtDateResolution_Change()

Me![IssueClosed] = True

End Sub

Or better the Before Update event of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![IssueClosed] = Not IsNull(Me![DateResolution])

End Sub

Or if an issue is always closed when a resolution date is give:

SELECT
NOT IsNull(DateResolution) AS IssueClosed
FROM
Issue


mfG
--> stefan <--

 

[Klatuu]

Not the Change event. It fires after every keystroke.
If you wanted to ensure a valid date has been entered, then using the IsDate
function in the Before Update and the IsNull in the After Update would be the
correct method.

Private Sub txtDateResolution_BeforeUpdated(Cancel As Integer)

With Me.txtDateResolution
If Not IsDate(.Value) Then
MsgBox .Value & " Is Not a Valid Date"
.Undo
Cancel = True
End If
End With

End Sub

Private Sub txtDateResolution_AfterUpdated()
Me.chkIssueClosed = Not IsNull(Me.txtDateResolution)
End Sub

--
Dave Hargis, Microsoft Access MVP

hi,

I have a date field [DateResolution] and if a user enters a date in this
field, I would like the yes/no field [IssueClosed] checked automatically.

Use the On Change event of your date control:

Private Sub txtDateResolution_Change()

Me![IssueClosed] = True

End Sub

Or better the Before Update event of your form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![IssueClosed] = Not IsNull(Me![DateResolution])

End Sub

Or if an issue is always closed when a resolution date is give:

SELECT
NOT IsNull(DateResolution) AS IssueClosed
FROM
Issue


mfG
--> stefan <--