VBA problem (I think) to check for data entry

[Jim Ory]

Using WinXP and Access2K2

On a form I have this code to check if a control has data and checkboxes
have been checked. The form is based on a Query of the underlying Table so I
can alphabetize the names on the form level.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
'Checks to see if a date has been entered and if a Category has been checked
If IsNull(Me.dteEnterDate) Then
MsgBox "Please Enter A Date."
Me.dteEnterDate.SetFocus
ElseIf Me.ysnClient = False And Me.ysnDonor = False And Me.ysnCharity =
False And Me.ysnVolunteer = False Then
MsgBox "You must select at least one Entity Category."
Me.dteEnterDate.SetFocus
Cancel = True
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "error" & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Almost everything works fine, except when attempting to leave the record and
no checkboxes have been checked. The 1st message box: "You must select at
least one Entity Category." I click on "OK" and then the 2nd message box:
"You entered an expression that has an invalid reference to the property |."
I click on "OK" and focus goes to the control; 'dteEnterDate' like it is
supposed to do. I can check any of the above check boxes and 'refresh' and
everything is fine.

I'd like to be able to write the code correctly to not have the 2nd message
box appear.

As always, suggestions appreciated.

 

[storrboy]

If I may, I would modify it as such, assuming that checkboxes cannot
be null on your form (they're either true or false).
It a personal preference to use ! instead of . in a form. Also, your
IsNull check of the date dosen't tell you if it is a date in that box,
just that there is something there. See if this helps.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
'Checks to see if a date has been entered and if a Category has been
checked
If Not IsDate(Me!dteEnterDate) Then
MsgBox "Please Enter A Date."
Me!dteEnterDate.SetFocus
Cancel=True
Exit Sub
End If

If Me!ysnClient + Me!ysnDonor + Me!ysnCharity + Me!ysnVolunteer =0
Then
MsgBox "You must select at least one Entity Category."
Me!dteEnterDate.SetFocus
Cancel = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "error" & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

 

[Jim Ory]

Thanks for the suggestion. But, the error still appears as before. I tried
the code you gave me and the line needs to be:

<If Me!ysnClient = 0 And Me!ysnDonor = 0 And Me!ysnCharity = 0 And
Me!ysnVolunteer = 0 Then>

That code also gave me a slightly different error, yet when I cleared the
error message, I could go back and correct my missing data. So, it basically
works the same way. If I could trap the error, that would work as it doesn't
have an ill effect on the database.

 

[storrboy]

Have you tried inserting a breakpoint and seeing what line of code
fails. I've tried both yours and mine, and I don't get the error you
are encountering. I think there is something else not being looked at.

 

[storrboy]

He's using the form's BeforeUpdate, not the checkboxes. If the form is
bound, this event will fire when any data changes.

 

[storrboy]

I realize that Vandal.
But his code dosen't run until the form is updating. He got a message,
so the event is running - all control BeforeUpdates have already
occured.
The message box regarding a check box not being selected IS occuring,
so the test for the check boxes being false is properly evaluating (I
can only assume properly). If they were contained in an option group,
they would contain no individual value. Instead of the checkbox
message occuring he would likely get a "2427 You entered an
expression that has no value." error (I did in two tests). Nothing
indicates to me (plus no mention in any of his posts as of yet) that
an option group is involved.

(Watch him respond now and say - yes they are in one)