simple event order problem

[CarlyS]

I have a form with a test list. Each test is a record with a yes/no checkbox, and controls for testdate and examiner. I don't want the user to be able to exit a record if the yes box is checked and either the testdate or examiner fields are blank. The code I am using is:

If IsNull(Me.Examiner) And Me.SelectTest = Yes Or IsNull(Me.TestDate) And Me.SelectTest = Yes Then
MsgBox "You must enter an examiner and a test date for each test you select.", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Me.Examiner.SetFocus
Cancel = True
Else
Cancel = False
End If

I am having trouble with which event should be used with the code, however. In both before and after update I have had the following problem. If a user accidentally checks the wrong test and then unchecks it, they still get the message as if the box were still checked yes. Can anyone tell me why and how to avoid that?

Thanks in advance,
Carly

 

[Pat Hartman]

You need to use the form's BeforeUpdate event. The problem is that if a user enters data in a control and then deletes the data, the control isn't null any more. To get around this problem, you can check for zero-length-strings also.

If ((IsNull(Me.Examiner) Or Me.Examiner = "") And Me.SelectTest = Yes) Or ((IsNull(Me.TestDate) Or Me.TestDate = "") And Me.SelectTest = Yes) Then
    MsgBox "You must enter an examiner and a test date for each test you select.",      
    vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Me.Examiner.SetFocus
    Cancel = True
Else
    Cancel = False
End If

I also added parentheses to your condition because you need them when combining AND and OR conditions to make sure that they are evaluated as you intend.

 

[CarlyS]

still no luck

I am still having the same problem. Perhaps because of the checkbox??
If the checkbox is checked, then those other fields should be populated. If it is not checked then there is no need. Pat, are you saying the problem is in the difference between not checking and unchecking? I want unchecking a box to define that value as "no" or even "not yes", but what is happening is that it is still perceived as yes unless I use undo. Any ideas what I've done wrong?

Carly

 

[Pat Hartman]

The problem is that initially checkboxes have the potential for three values - Null, True, and False. Once a checkbox has been checked, it can never (at least via a form) be set back to Null. Uncheck makes it False.

To avoid this problem, I always define my checkboxes with a default value of False (or True if that makes more sense). If I add a checkbox to an existing table with data, I run an update query to set all rows to whatever I have decided will be the default value. I suggest that you do the same.

Given this change in logic, you'll want to change your edit.

 

[CarlyS]

Thanks for your help Pat. One more question as I'm still learning this stuff... is my code wrong then? Instead of "Me.SelectTest = Yes" should I be using "True"? Wouldn't that code still work then (provided I use the right word, Yes or True), because whether it's null or False, it still isn't True, right? Or do I still not get it?
Thanks for everything,
Carly

 

[Pat Hartman]

Nulls are a special case which you need to code for specifically. That is why I don't allow them in yes/no fields.

When you test a field for True - only the true values will be considered. When you test it for False - only the false values will be considered. If your recordset contains ONLY values of True or False then "Not True" would give you the same count as "False". However with Nulls in the mix, the two counts will be different since "Not True" will count only the rows that have non-null values that are "Not True". Nulls will not be counted.

If you leave the initial value as Null, you will need to change the edit check to account for that.

If ((IsNull(Me.Examiner) Or (Me.Examiner = False AND IsNull(Me.Examiner.OldValue)) And Me.SelectTest = Yes) Or ((IsNull(Me.TestDate) Or (Me.TestDate = False AND IsNull(Me.TestDate.OldValue)) And Me.SelectTest = Yes) Then
    MsgBox "You must enter an examiner and a test date for each test you select.",      
    vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Me.Examiner.SetFocus
    Cancel = True
Else
    Cancel = False
End If

I changed the code to check for false instead of ZLS but you need to know if false is an accidental or intential value. I'm not sure how you can do that but if you test the OldValue property of the field to see if it started out as null, that might give you what you are looking for.

 

[CarlyS]

Pat, I'm confused. The only field that is a checkbox is SelectTest. That was the one I was worried would need to be changed to "True" instead of "Yes" in my code. Is that not a necessary or accurate change?

I do think I understand the difference between not true and null and false. I sorry for being slow, but I want to make sure I learn this...
If my criteria is that SelectTest is True (or Yes?) AND neither TestDate or Examiner are null, then records where SelectTest has never been checked or records where they have been checked and are currently unchecked will not meet this criteria regardless of whether or not the other fields are null because SelectTest is not True(or Yes).

Is that true?

Thank you so much for your help with this!
Carly

 

[Pat Hartman]

How about a positive test instead of the negative tests?

If Me.SelectTest = True AND Len(Me.Examiner) > 0 AND Len(Me.TestDate) > 0 Then
    Cancel = False
Else
    MsgBox "You must enter an examiner and a test date for each test you select.",      
    vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
    Me.Examiner.SetFocus
    Cancel = True
End If