MsgBox if Checkbox IsNull

[jdph40]

Question 1: In Access 97, I have a form (frmVacationWee ks) with a
subform (sbfrmPostVacDa tes). The subform can have up to 33 records
and each record has 2 checkboxes, one for approved and one for
rejected. A supervisor opens frmVacationWeek s and either approves or
rejects dates the employee has requested for vacation. When the
supervisor closes frmVacationWeek s, if he has failed to click a
checkbox to approve or reject a date, I want a message box to pop up
and tell him "You missed approving or rejecting a requested date!" I
tried the following, but I get an error message - Run-time error 2450
- telling me that frmVacationWeek s cannot be found. Can someone steer
me in the right direction?

If IsNull(Forms![frmVacationWeek s]![sbfrmPostVacDat es].Approved) Or
IsNull(Forms![frmVacationWeek s]![sbfrmPostVacDat es].Rejected) Then
MsgBox "You missed approving or rejecting a requested date!",
vbOKOnly, "Supervisor Action Required"
Else
DoCmd.Close acForm, "frmVacationWee ks"
End If

Question 2: How can I disallow the supervisor from clicking both
approved and rejected checkboxes in a single record?

Thanks,
JD

[Wayne Morgan]

Where did you place this code? We may be able to move it and make a small change to get it
to work, depending on the following answer.

Do you want it to check all records before closing the form or check each record as the
Supervisor moves to the next record? The latter would save having to go back through all
of the unchecked records, although you could filter the form to show only those.
There are a few ways to disallow the supervisor from checking both boxes.

1) Place the check boxes in an option group, this will allow only one to be selected at a
time. However, you won't have a value for each check box. Instead you will have one value
for the option group indicating which box is checked. This is usually done with "radio
buttons" (the dots) instead of check boxes. Check boxes usually indicate that you can
select any or all of them.

2) Use on check box set for "Triple State". This will give you Null, Yes, and No in one
check box.

3) Use code that will only allow one of the check boxes to be checked at a time, basically
simulating the option group.

--
Wayne Morgan
Microsoft Access MVP
<jd****@yahoo.c om> wrote in message
news:75******** *************** ***@posting.goo gle.com...

Question 1: In Access 97, I have a form (frmVacationWee ks) with a
subform (sbfrmPostVacDa tes). The subform can have up to 33 records
and each record has 2 checkboxes, one for approved and one for
rejected. A supervisor opens frmVacationWeek s and either approves or
rejects dates the employee has requested for vacation. When the
supervisor closes frmVacationWeek s, if he has failed to click a
checkbox to approve or reject a date, I want a message box to pop up
and tell him "You missed approving or rejecting a requested date!" I
tried the following, but I get an error message - Run-time error 2450
- telling me that frmVacationWeek s cannot be found. Can someone steer
me in the right direction?

If IsNull(Forms![frmVacationWeek s]![sbfrmPostVacDat es].Approved) Or
IsNull(Forms![frmVacationWeek s]![sbfrmPostVacDat es].Rejected) Then
MsgBox "You missed approving or rejecting a requested date!",
vbOKOnly, "Supervisor Action Required"
Else
DoCmd.Close acForm, "frmVacationWee ks"
End If

Question 2: How can I disallow the supervisor from clicking both
approved and rejected checkboxes in a single record?

Thanks,
JD

[jdph40]

Thank you for your reply. I tried to respond back to you on Friday
from home, but I kept getting error messages from my browser.

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:<pz******* ***********@new ssvr31.news.pro digy.com>...

Where did you place this code? We may be able to move it and make a small change to get it
to work, depending on the following answer.
I put the code in the OnClose event of the subform. When I tried it
in the OnClose event of the main form, I got Run-time error 438 -
Object doesn't support this property or method.
Do you want it to check all records before closing the form or check each record as the
Supervisor moves to the next record? The latter would save having to go back through all
of the unchecked records, although you could filter the form to show only those.
I want to check all the records before closing the form.

There are a few ways to disallow the supervisor from checking both boxes.

3) Use code that will only allow one of the check boxes to be checked at a time, basically
simulating the option group.

I want to try it this way. Can you supply me with the code to use in
this case? Your help is very much appreciated.

Thanks!
JD

[Wayne Morgan]

Ok, to check them all when closing the form, use DCount to see if either field is Null or
False and if so then Cancel the close. This would be done in the OnUnload event of the
parent (main) form.

If DCount("*", "TableName" , "[ApprovedField] <> True And [RejectedField] <> True") > 0
Then
MsgBox "You missed approving or rejecting a requested date!", vbOkOnly+vbInfo rmation,
"Supervisor Action Required"
Cancel = True
Else
Me!sbfrmPostBac Dates.Form.Filt erOn = False
End If
'At this point you would filter the form to show only those records
'where both checkboxes are No or Null
'If <> True then it is No or Null
Me!sbfrmPostBac Dates.Form.Filt er = "[ApprovedField] <> True And [RejectedField] <> True"
Me!sbfrmPostBac Dates.Form.Filt erOn = True
The code to check that only one checkbox is checked would be placed in the AfterUpdate
event of each checkbox.

In the Rejected checkbox's AfterUpdate
Me.Approved = Not Me.Rejected

And in the Approved checkbox's AfterUpdate
Me.Rejected = Not Me.Approved

This will set the other checkbox to be the opposite value of the box you just checked.
Make sure the field name and control name aren't the same. If the field name is Rejected,
then make the name of the checkbox chkRejected. If done to both, that would change the
above to

Me.chkRejected = Not Me.chkApproved

--
Wayne Morgan
Microsoft Access MVP
<jd****@yahoo.c om> wrote in message news:75******** *************** **@posting.goog le.com...

Thank you for your reply. I tried to respond back to you on Friday
from home, but I kept getting error messages from my browser.

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message

news:<pz******* ***********@new ssvr31.news.pro digy.com>...

Where did you place this code? We may be able to move it and make a small change to get it to work, depending on the following answer.

I put the code in the OnClose event of the subform. When I tried it
in the OnClose event of the main form, I got Run-time error 438 -
Object doesn't support this property or method.

Do you want it to check all records before closing the form or check each record as the Supervisor moves to the next record? The latter would save having to go back through all of the unchecked records, although you could filter the form to show only those.

I want to check all the records before closing the form.

There are a few ways to disallow the supervisor from checking both boxes.

3) Use code that will only allow one of the check boxes to be checked at a time, basically simulating the option group.

I want to try it this way. Can you supply me with the code to use in
this case? Your help is very much appreciated.

Thanks!
JD

[jdph40]

Thank you, Wayne. You are a great help!

JD

[jdph40]

Wayne, I've run into a problem and I hope you'll help me with it. My
subform is based on a parameter query with an expression in the
criteria field that asks the user what year the vacation days are
scheduled in. When I use the code you gave me in the unload event of
my main form, I get the following un-numbered error message: "The
object doesn't contain the Automation object 'Enter year in which days
are scheduled.' You tried to run a Visual Basic procedure to set a
property or method for an object. However, the component doesn't make
the property or method available for Automation operations." When I
debug, the following line of code is highlighted:

Me!sbfrmPostVac Dates.Form.Filt er = "[ApprovedField] <> True And [RejectedField] <> True"

I've been trying for a couple of days to figure this out for myself,
but I'm stumped. Please help!

Thanks,
JD

[jdph40]

Wayne, something I just thought of...I need to give the supervisor the
option of closing the form without approving or rejecting a date just
in case he needs to look at a report of his department's days off
before doing so. So I need to say vbOKCancel instead of vbOKOnly in
the line of code below, close the form if he selects cancel and
proceed with the code if he selects OK.

"> If DCount("*", "TableName" , "[ApprovedField] <> True And
[RejectedField] <> True") > 0

Then
MsgBox "You missed approving or rejecting a requested date!", vbOkOnly+vbInfo rmation,
"Supervisor Action Required"

Thanks in advance!
JD

[Wayne Morgan]

If DCount("*", "TableName" , "[ApprovedField] <> True And [RejectedField] <> True") > 0
Then
If MsgBox("You missed approving or rejecting a requested date!",
vbOkCancel+vbIn formation,
"Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
Me!sbfrmPostBac Dates.Form.Filt erOn = False
End If
'At this point you would filter the form to show only those records
'where both checkboxes are No or Null
'If <> True then it is No or Null
Me!sbfrmPostBac Dates.Form.Filt er = "[ApprovedField] <> True And [RejectedField] <> True"
Me!sbfrmPostBac Dates.Form.Filt erOn = True

--
Wayne Morgan
Microsoft Access MVP
<jd****@yahoo.c om> wrote in message
news:75******** *************** ***@posting.goo gle.com...

Wayne, something I just thought of...I need to give the supervisor the
option of closing the form without approving or rejecting a date just
in case he needs to look at a report of his department's days off
before doing so. So I need to say vbOKCancel instead of vbOKOnly in
the line of code below, close the form if he selects cancel and
proceed with the code if he selects OK.

"> If DCount("*", "TableName" , "[ApprovedField] <> True And
[RejectedField] <> True") > 0

Then
MsgBox "You missed approving or rejecting a requested date!", vbOkOnly+vbInfo rmation, "Supervisor Action Required"

Thanks in advance!
JD

[Wayne Morgan]

The easiest way around this is probably going to be a pop-up form. When you open your
form, instead of the query asking for the input, the pop-up form would. The query would
then refer to the textboxes on the pop-up form for the parameter values.

Make a form with 2 textboxes and a label asking for the input. Have an Ok button and a
Cancel button. In the OnOpen event of your current main form you would open the pop-up
with the acDialog window mode argument. This will halt everything until you close or hide
the pop-up. If you choose the Cancel button, you would also tell the OnOpen code of the
main form to Cancel its opening. If you choose the Ok button, you would check the values
entered into the textboxes and, if valid, hide the pop-up (Me.Visible = False). The
parameters in the query would refer to these textboxes for their values, that way they
will still be there later when you filter the subform and hopefully you won't get prompted
then. Close the pop-up in the OnClose event of the main form.

--
Wayne Morgan
Microsoft Access MVP
<jd****@yahoo.c om> wrote in message
news:75******** *************** ***@posting.goo gle.com...

Wayne, I've run into a problem and I hope you'll help me with it. My
subform is based on a parameter query with an expression in the
criteria field that asks the user what year the vacation days are
scheduled in. When I use the code you gave me in the unload event of
my main form, I get the following un-numbered error message: "The
object doesn't contain the Automation object 'Enter year in which days
are scheduled.' You tried to run a Visual Basic procedure to set a
property or method for an object. However, the component doesn't make
the property or method available for Automation operations." When I
debug, the following line of code is highlighted:

Me!sbfrmPostVac Dates.Form.Filt er = "[ApprovedField] <> True And [RejectedField] <>

True"
I've been trying for a couple of days to figure this out for myself,
but I'm stumped. Please help!

Thanks,
JD

[jdph40]

Wayne, I'm sorry but I keep running into walls. What I've run into
now is when after the message comes up "You missed approving..." and
OK is clicked, the records are filtered showing the record he missed
plus the blank record that is generated in the query because, I
suppose, of the Autonumber field. Anyway, of course ApprovedField and
RejectedField are null in this record, so Access thinks he missed
approving or rejecting it. Is there any way I can make Access ignore
this blank record?

Thanks,
JD

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:<41******* ***********@new ssvr31.news.pro digy.com>...

If DCount("*", "TableName" , "[ApprovedField] <> True And [RejectedField] <> True") > 0
Then
If MsgBox("You missed approving or rejecting a requested date!",
vbOkCancel+vbIn formation,
"Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
Me!sbfrmPostBac Dates.Form.Filt erOn = False
End If
'At this point you would filter the form to show only those records
'where both checkboxes are No or Null
'If <> True then it is No or Null
Me!sbfrmPostBac Dates.Form.Filt er = "[ApprovedField] <> True And [RejectedField] <> True"
Me!sbfrmPostBac Dates.Form.Filt erOn = True

--
Wayne Morgan
Microsoft Access MVP