Validating Data on Form Entry

[JoeM]

I have a form where people enter in client contact information. I have a check box that they use to identify terminated clients. There is also a Termination/Transfer Date field.
They want to require people to fill out the Termination/Transfer Date before they are allowed to check the Terminated Client check-box. I programmed a Before Update Event on my check box field to check for this, i.e.

Private Sub ysnTermPlan_BeforeUpdate(Cancel As Integer)
'   Make sure Term Date is populatede before allowing them to check Term Plan check box
    
    If Nz(Me.dteTermDate, DateValue("12/31/2099")) = DateValue("12/31/2099") Then
        Cancel = True
        Undo
        MsgBox "You cannot check the Terminated Plan box without first filling out the Term/Transfer Date field!", vbOKOnly, "ATTENTION!!!"
    End If
    
End Sub

There is just one problem. If the Termination Date field is blank, ALL data changes that they have made for this client are undone, not just the check box.
How can I just undo the check box and not undo any other client changes that they may have made while on the Form?

Thanks

[June7]

Alternative is to not make the checkbox available until there is a value in date field. Set the checkbox Visible or Enabled property to No.

Code in the date textbox AfterUpdate event:
Me.checkboxname.Visible = Not IsNull(Me.datetextboxname)

Might also want this code in the form Current event so the checkbox property will be set when moving to existing record.

Why even have the checkbox? The date field is a flag that client is 'terminated' - no date means not terminated, date means terminated. If the checkbox is to distinguish between a termination as opposed to transfer, there is no way to know that the action is a termination if user neglects to check the box - results in GIGO (garbage in, garbage out) syndrome.

[RuralGuy]

Don't issue the UNDO. Just the Cancel = True will be enough.

[JoeM]

Alternative is to not make the checkbox available until there is a value in date field. Set the checkbox Visible or Enabled property to No.

I thought about that, but knowing our users, we would get a ton of people commenting that the check box is not enabled. My thinking is that this way would actually let them know "why" and reduce the redundant questions we get.
I still may go this route if I cannot get it to do what I want.

Why even have the checkbox? The date field is a flag that client is 'terminated' - no date means not terminated, date means terminated. If the checkbox is to distinguish between a termination as opposed to transfer, there is no way to know that the action is a termination if user neglects to check the box - results in GIGO (garbage in, garbage out) syndrome.

A few reasons. Here is the most important.
Near the end of the plan year, they will start to generate annual (2014) records for next year. If they know a client is terminating at the end of the year, they can enter a date of 12/31/2013. Then when they go to create records for 2014, records will not be created for that group. However, once they check the Terminated Client check box, the group is removed from all functionality and reports. They obviously do not want to do that until the end of the plan year has passed.

Don't issue the UNDO. Just the Cancel = True will be enough.

Tried that already, and that's rather clunky. It leaves the record in "write" mode, and the message box keeps popping up no matter where you click until you hit the ESC button. So if the users don't now enough to do that, they will be caught in a frustrating loop.

However, you replies got me thinking about different ways of approaching this, and I think it got it to work like I want using the AfterUpdate event instead. It has the desired/intended affect.
Basically, instead of trying to prevent the check box selection beforehand, it is just changing it back afterwards (right away).

Private Sub ysnTermPlan_AfterUpdate()
'   If Term Date field is blank, remove check box

    If (Me.ysnTermPlan = True) And (Nz(Me.dteTermDate, DateValue("12/31/2099")) = DateValue("12/31/2099")) Then
        Me.ysnTermPlan = False
        MsgBox "You cannot check the Terminated Plan box without first filling out the Term/Transfer Date field!", vbOKOnly, "ATTENTION!!!"
    End If

End Sub

[JoeM]

By the way, I realize that this a bit clunky:
Nz(Me.dteTermDate, DateValue("12/31/2099")) = DateValue("12/31/2099")
but I couldn't get it to work out right trying different variations of Null functions (i.e. = NULL, Is Null, IsNull(...), etc)

Is there a cleaner way to check to see if a Date field has an entry or not?

It works fine, I just have a feeling that I might be unnecessarily complicating things.

Thanks

[June7]

Could always put a label on the form "Checkbox not available until date entered." To me, the disabled checkbox would cause less frustration than clicking the checkbox and user getting hit with a messagebox they have to respond to. I don't like to slap hands if I can avoid it.

IsNull or IsDate can test the contents of textbox.

[JoeM]

I don't like to slap hands if I can avoid it.

Come on, that's half the fun of it! We programmers have to get our frustrations out somehow, don't we?

All kidding aside, I understand what you are saying. I would have to do some re-formatting of the Form to get it to fit in and look nice, and we are just looking for a quick fix for now.
We will probably be re-designing this database next year in VB (management feels this database has outgrown Access), so I'll keep that in mind.

IsNull or IsDate can test the contents of textbox.

I had already tried IsNull, but I was having a Monday brain cramp and was confusing it with the T-SQL IsNull function, which has two arguments.
That's what happens when you program in too many different languages, you start confusing them (Excel VBA, Access VBA, T-SQL, and soon to be VB)!

Thanks for pointing me in the right direction!