VBA code for date criteria IfPrivate Sub completed_AfterUpdate()

[ECEK]

VBA code for date criteria AfterUpdate

I have a form with a date on it called [all_date]
When the user presses the [completed] radio button the [comp_date] is populated via the following code:

Private Sub completed_AfterUpdate()
If Me.completed = True Then
Me.comp_date = Date
Else
Me.comp_date = Null
End If
End Sub

What I am looking to do is nullify the [completed] radio button if the [comp_date] is before the [all_date]

...so that the [comp_date] is Null and the [completed] radio button remains False

I have tried to AfterUpdate on the [comp_date] but Im getting lost.

This is for Users to illustrate what they have completed:
but not to be able to complete anything that has been allocated to them for tomorrow.

Hope you can help

 

[vbaInet]

You can apply in the After Update event of comp_date and all_date.

If Me.Comp_date < Me.All_Date Then
    Me.completed = Null
End If

 

[ECEK]

This doesn't work Im afraid!!

I also tried

If Me.comp_date < Me.all_date Then
Me.comp_date = Null
End If

Is there a way of combining the AfterUpdate of the original code ie.

If true then comp_date = Date
If comp_date is less than all_date then Null And completed = False

Else
comp_date = Null

 

[vbaInet]

The same validation but two different outcomes. You've not explained exactly what the criteria should be and which field/control should be affected.

 

[ECEK]

Ill try to explain what I want to do.

There is a radio button [completed]
There is an [all_date]
There is a [comp_date]

when I press the [completed] radio button I want to populate the [comp_date] with todays date unless todays date is less than [all_date]

It's a confirmation form where people are allocated work to do and they confirm if they have done it. I don't want them to be able to check the box if the date on completion is before the allocated date. This is due to other calculations.

 

[AlexN]

Ill try to explain what I want to do.

There is a radio button [completed]
There is an [all_date]
There is a [comp_date]

when I press the [completed] radio button I want to populate the [comp_date] with todays date unless todays date is less than [all_date]

It's a confirmation form where people are allocated work to do and they confirm if they have done it. I don't want them to be able to check the box if the date on completion is before the allocated date. This is due to other calculations.

Sorry for the interference but, why don't you change the Enabled property of the checkbox depending on the comparison between Date() and [all_date]?

 

[ECEK]

I guess I would if I knew how?
I don't understand what you mean. I thought that enable was yes or no ?

 

[vbaInet]

@AlexN: This is a continuous form and Conditional Formatting is not possible with a checkbox.

@ECEK: In the Before Update event of completed

If Date() < Me.All_Date Then
    Cancel = True
Else
    Me.Comp_date = Date()
End If

 

[AlexN]

I guess I would if I knew how?
I don't understand what you mean. I thought that enable was yes or no ?

Something like

If all_date>Date Then
Me.completed.Enabled = False
else
Me.completed.Enabled = True
End if

if I'm right....
unchecked, please check it first.



Edit
OK.....sorry, my bad......forget it....

 

[vbaInet]

Edit
OK.....sorry, my bad......forget it....

Although the point about it being a continuous form wasn't mentioned in this thread, I got to know about it on one of ECEK's other active thread.

 

[ECEK]

OK I've worked it out.

When I click the completed box it enters todays date into the comp_date then sets the focus to the comp_date

I've then set the OnGotfocus of the comp_date with the criteria. So the code is:

Private Sub completed_AfterUpdate()
If Me.completed = True Then
Me.comp_date = Date
Else
Me.comp_date = Null
End If
Me.comp_date.SetFocus
End Sub

Then ................

Private Sub comp_date_GotFocus()
If Me.comp_date < Me.all_date Then
Me.completed = False
Me.comp_date = Null
End If
End Sub

Hurray !!!!

 

[vbaInet]

Did you see my post #8?

 

[ECEK]

I thought that that had worked but Im getting a "cant save database" message now !!

 

[vbaInet]

Can you please indicate the full error message and what code line it stops.

 

[RainLover]

VBA

Should these events use Before Update rather than After Update.

I believe they should but I could be wrong.

Have you given this a thought.

 

[ECEK]

There's no VBA error it just comes up when I try to close the form that the checkbox is on.

 

[vbaInet]

Hello Rain,

Yeah I did give it some thought. That's why it sets the Cancel parameter to False so it nullifies an attempt to update the checkbox, otherwise it sets the value of the comp_date field (which doesn't cause the Before/After Update events to fire). So in principle it should work as expected.

 

[vbaInet]

ECEK, what's the full error message? Your version of "can't save database" doesn't mean much to me.

 

[ECEK]

image of message

 

[vbaInet]

Are you using the code that you were given or are you talking about the code you wrote?