Code to change Date in a field if the checkbox is checked

[John Lee]

Good day Folks,

I have written code in the On Current event of my form as follows:

Dim CurrentDate As Date
Dim NextReviewDate As Date
Dim NewReviewDate As Date

CurrentDate = Me!txtCurrentDate.Value
NextReviewDate = DateAdd("yyyy", 1, Me!txtCurrentDate.Value)

NewReviewDate = NextReviewDate

Me!txtNextReviewDate=NewReviewDate

The above code works fine as I step through each record, but the bit I am struggling with is as follows:

I have a Yes/No check box on my form named txtReviewed (Control source is ysnReviewed)

What I would like to do is when ever this check box is changed to "Yes" is for the value (Date) in the txtNextReviewDate field to replace the value (Date) in the txtCurrentDate and then for the code above to be implemented to produce a new Next Review Date. I'm not sure if this code should still be in the On Current Event of my form either.

I tried the following, but nothing happen on changing the check box:

Dim Reviewed As Boolean
Dim NextNewReviewDate As Date

Reviewed = Me!txtReviewed.value
NextNewReviewDate = Me!txtNextReviewDate

If Reviewed = -1 then
CurrentDate = NextReviewDate ' Replace the Current Date with the Next Review Date
Me!txtCurrentDate.Value = CurrentDate 'Assign the value of CurrentDate as the value of Me!txtCurrentDate
NextReviewDate = DateAdd("yyyy", 1, Me!CurrentDate.Value) 'Calculate a new Next Review Date
Me!txtNextReviewDate.Value = NextReviewDate 'Assign the value of NextReviewDate to the the Me!txtNextReviewDate field
Reviewed = 0 'Assign zero to change the Me!txtReviewed check box to No
Me!txtReviewed.Value = Reviewed 'Assign the value of Reviewed to the value of Me!txtReviewed
End If

So, I've been able to get the first bit of code working, but I'm struggling to workout why this bit of code isn't working, hopefully someone might see what I'm not.

Your assistance is most appreciated.

 

[Gasman]

Your boolean control is called ysnReviewed and you test for Reviewed which is set by Me.txtReviewed?
Do you have Option Explicit at the top of each module?

 

[arnelgp]

you post all the code (including the Private Sub..), so we can understand what event your codes belong.

 

[LarryE]

Good day Folks,

I have written code in the On Current event of my form as follows:

Dim CurrentDate As Date
Dim NextReviewDate As Date
Dim NewReviewDate As Date

CurrentDate = Me!txtCurrentDate.Value
NextReviewDate = DateAdd("yyyy", 1, Me!txtCurrentDate.Value)

NewReviewDate = NextReviewDate

Me!txtNextReviewDate=NewReviewDate

The above code works fine as I step through each record, but the bit I am struggling with is as follows:

I have a Yes/No check box on my form named txtReviewed (Control source is ysnReviewed)

What I would like to do is when ever this check box is changed to "Yes" is for the value (Date) in the txtNextReviewDate field to replace the value (Date) in the txtCurrentDate and then for the code above to be implemented to produce a new Next Review Date. I'm not sure if this code should still be in the On Current Event of my form either.

I tried the following, but nothing happen on changing the check box:

Dim Reviewed As Boolean
Dim NextNewReviewDate As Date

Reviewed = Me!txtReviewed.value
NextNewReviewDate = Me!txtNextReviewDate

If Reviewed = -1 then
CurrentDate = NextReviewDate ' Replace the Current Date with the Next Review Date
Me!txtCurrentDate.Value = CurrentDate 'Assign the value of CurrentDate as the value of Me!txtCurrentDate
NextReviewDate = DateAdd("yyyy", 1, Me!CurrentDate.Value) 'Calculate a new Next Review Date
Me!txtNextReviewDate.Value = NextReviewDate 'Assign the value of NextReviewDate to the the Me!txtNextReviewDate field
Reviewed = 0 'Assign zero to change the Me!txtReviewed check box to No
Me!txtReviewed.Value = Reviewed 'Assign the value of Reviewed to the value of Me!txtReviewed
End If

So, I've been able to get the first bit of code working, but I'm struggling to workout why this bit of code isn't working, hopefully someone might see what I'm not.

Your assistance is most appreciated.

Add a Me.Recalc after If Reviewed = -1 then
That will update the form

 

[The_Doc_Man]

I'm not sure if this code should still be in the On Current Event of my form either.

If that control actually IS a check-box, then the two places where your code should go are the Form_Current routine and the ysnReviewed_Click routine. That catches the value on navigation to the current record or the attempt to update the particular control.

If the ysnReviewed control is bound to a Boolean field, then because of defaults, you don't have to do something so complex as

Reviewed = Me!txtReviewed.value
...
If Reviewed = -1 then...

You can collapse that to

If Me.ysnReviewed Then...

Here's a rule to help you with having to type so much. The property ".Value" is the default for any control that HAS a value and is bound to a field. And when using logic, the IF statement will evaluate the test expression as TRUE or FALSE regardless of how complex - or in this case, how simple - your test expression is.

Gasman also correctly called out inconsistent naming in the code since the control name (we were told) starts with "ysn" but later you refer to "txt" and THAT indicates confusion of variable names. When talking to us? No biggie. When giving instructions to VBA? Very big biggie. VBA is extremely literal-minded.

 

[LarryE]

I always use the On Click Event for checkboxes. Then a Me. Form.Refresh as the first line of code.