VBA problem (I think) to check for data entry

[Guest]

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

 

[Guest]

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.

 

[AccessVandal via AccessMonster.com]

Hi Jim,

This is from Access Help.(2003)

"The BeforeUpdate event does not apply to option buttons, check boxes, or
toggle buttons in an option group. It applies only to the option group itself.
"

I'm not sure this might be the cause from your checkboxes.

HTH

 

[storrboy]

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

 

[AccessVandal via AccessMonster.com]

Hi storrboy,

It covers both forms and controls.

storrboy wrote:
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)

 

[AccessVandal via AccessMonster.com]

Hi storrboy,

From the original code or yours,

ElseIf Me.ysnClient = False And Me.ysnDonor = False And Me.ysnCharity =
False And Me.ysnVolunteer = False Then

First, if the checkboxes are independent they will work.

From his code by default, the checkbox control is set to “ControlName.Valueâ€.

Hence, the error "2427 You entered an expression that has no value."

The correct code is the control properties “OptionValue†not “Valueâ€.

ElseIf Me.ysnClient.OptionValue = False And Me.ysnDonor.OptionValue = False
And Me.ysnCharity.OptionValue = False And Me.ysnVolunteer.OptionValue = False
Then

Use the above if they are not Option Groups.

However, if they are Option Groups. You’ll need the Option Group Control Name.
In this case the default name is “Frame0â€. So…….

“Frame0.Value†to check which one of the checkbox is ticked. If unticked, it
is “Nullâ€.

 

[AccessVandal via AccessMonster.com]

Ignore my last post.

Misread the last phrases of “second message†from the OP first post.

The error seems to come from another event or a misspelled control.