By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

MsgBox if Checkbox IsNull

P: n/a
Question 1: In Access 97, I have a form (frmVacationWeeks) with a
subform (sbfrmPostVacDates). The subform can have up to 33 records
and each record has 2 checkboxes, one for approved and one for
rejected. A supervisor opens frmVacationWeeks and either approves or
rejects dates the employee has requested for vacation. When the
supervisor closes frmVacationWeeks, 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 frmVacationWeeks cannot be found. Can someone steer
me in the right direction?

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

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

Thanks,
JD
Nov 12 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
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.com> wrote in message
news:75**************************@posting.google.c om...
Question 1: In Access 97, I have a form (frmVacationWeeks) with a
subform (sbfrmPostVacDates). The subform can have up to 33 records
and each record has 2 checkboxes, one for approved and one for
rejected. A supervisor opens frmVacationWeeks and either approves or
rejects dates the employee has requested for vacation. When the
supervisor closes frmVacationWeeks, 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 frmVacationWeeks cannot be found. Can someone steer
me in the right direction?

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

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

Thanks,
JD

Nov 12 '05 #2

P: n/a
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******************@newssvr31.news.prodigy. 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
Nov 12 '05 #3

P: n/a
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+vbInformation,
"Supervisor Action Required"
Cancel = True
Else
Me!sbfrmPostBacDates.Form.FilterOn = 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!sbfrmPostBacDates.Form.Filter = "[ApprovedField] <> True And [RejectedField] <> True"
Me!sbfrmPostBacDates.Form.FilterOn = 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.com> wrote in message news:75*************************@posting.google.co m...
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******************@newssvr31.news.prodigy. 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

Nov 12 '05 #4

P: n/a
Thank you, Wayne. You are a great help!

JD
Nov 12 '05 #5

P: n/a
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!sbfrmPostVacDates.Form.Filter = "[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
Nov 12 '05 #6

P: n/a
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+vbInformation,
"Supervisor Action Required"


Thanks in advance!
JD
Nov 12 '05 #7

P: n/a
If DCount("*", "TableName", "[ApprovedField] <> True And [RejectedField] <> True") > 0
Then
If MsgBox("You missed approving or rejecting a requested date!",
vbOkCancel+vbInformation,
"Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
Me!sbfrmPostBacDates.Form.FilterOn = 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!sbfrmPostBacDates.Form.Filter = "[ApprovedField] <> True And [RejectedField] <> True"
Me!sbfrmPostBacDates.Form.FilterOn = True

--
Wayne Morgan
Microsoft Access MVP
<jd****@yahoo.com> wrote in message
news:75**************************@posting.google.c om...
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+vbInformation, "Supervisor Action Required"


Thanks in advance!
JD

Nov 12 '05 #8

P: n/a
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.com> wrote in message
news:75**************************@posting.google.c om...
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!sbfrmPostVacDates.Form.Filter = "[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

Nov 12 '05 #9

P: n/a
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******************@newssvr31.news.prodigy. com>...
If DCount("*", "TableName", "[ApprovedField] <> True And [RejectedField] <> True") > 0
Then
If MsgBox("You missed approving or rejecting a requested date!",
vbOkCancel+vbInformation,
"Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
Me!sbfrmPostBacDates.Form.FilterOn = 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!sbfrmPostBacDates.Form.Filter = "[ApprovedField] <> True And [RejectedField] <> True"
Me!sbfrmPostBacDates.Form.FilterOn = True

--
Wayne Morgan
Microsoft Access MVP

Nov 12 '05 #10

P: n/a
I just tried it on both a query and a table and the field that shows as a new field (the
blank row at the bottom) is ignored. I included an autonumber field to make sure. If the
record hasn't been entered yet it just shows (AutoNumber) in that field for the new
record, not an actual value. If you are showing a value, there is something else going on.
However, you could add one more field to the test if you wanted. One that will always have
a value if the record has been saved and will never have a value in a new, unused record
and see if it is Null or not.

--
Wayne Morgan
Microsoft Access MVP
<jd****@yahoo.com> wrote in message
news:75**************************@posting.google.c om...
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?

Nov 12 '05 #11

P: n/a
I've completely reworked by database trying to solve my problem with
Access apparently not ignoring the blank row at the bottom. I changed
from 2 Yes/No fields in my table for Approved and Rejected to a single
field named Approved with a data type of Number. I deleted the check
boxes in my form and made an option group with the value of Approved
being 1 and Rejected 2. There are absolutely no values in the blank
row, but for some reason, my message box comes up even when all
records have been approved/rejected. When the records are filtered,
the only one showing is the blank record. I am totally at my wits
end. I've copied and pasted the modified code below. Is there
something I'm missing? I added another field to the test, but still
got the same results, unless I had my code wrong. DateTaken is a
field that will never have a value in an unused record. I typed
"[Approved]<>1 Or [Approved]<>2 Or [DateTaken]=Null". Thanks in
advance for your help. (BTW, I'm using Access 2K2 now - recently and
reluctantly upgraded from Access 97).

JD

If DCount("*", "tblPostVacDates", "[Approved]<>1 Or [Approved]<>2") >
0 Then
If MsgBox("You missed approving or rejecting a requested date.
Click OK to locate the record, or click Cancel to leave this form.",
vbOKCancel, "Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
Me!sbfrmPostVacDates.Form.FilterOn = False
End If
Me!sbfrmPostVacDates.Form.Filter = "[Approved]<>1 And
[Approved]<>2"
Me!sbfrmPostVacDates.Form.FilterOn = True
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<WV*******************@newssvr32.news.prodigy .com>...
I just tried it on both a query and a table and the field that shows as a new field (the
blank row at the bottom) is ignored. I included an autonumber field to make sure. If the
record hasn't been entered yet it just shows (AutoNumber) in that field for the new
record, not an actual value. If you are showing a value, there is something else going on.
However, you could add one more field to the test if you wanted. One that will always have
a value if the record has been saved and will never have a value in a new, unused record
and see if it is Null or not.

Nov 12 '05 #12

P: n/a
<jd****@yahoo.com> wrote in message
news:75**************************@posting.google.c om...
I've completely reworked by database trying to solve my problem with
Access apparently not ignoring the blank row at the bottom. I changed
from 2 Yes/No fields in my table for Approved and Rejected to a single
field named Approved with a data type of Number. I deleted the check
boxes in my form and made an option group with the value of Approved
being 1 and Rejected 2. There are absolutely no values in the blank
row, but for some reason, my message box comes up even when all
records have been approved/rejected. When the records are filtered,
the only one showing is the blank record. I am totally at my wits
end. I've copied and pasted the modified code below. Is there
something I'm missing? I added another field to the test, but still
got the same results, unless I had my code wrong. DateTaken is a
field that will never have a value in an unused record. I typed
"[Approved]<>1 Or [Approved]<>2 Or [DateTaken]=Null". Thanks in
advance for your help. (BTW, I'm using Access 2K2 now - recently and
reluctantly upgraded from Access 97).

JD

If DCount("*", "tblPostVacDates", "[Approved]<>1 Or [Approved]<>2") >
0 Then


This needs to be an "AND" not an "OR". Otherwise all of the "1"s are
counted because they are not "2"s and all of the "2"s are counted because
they are not "1"s.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #13

P: n/a
Thanks, Rick, I changed the "or" to "and", but now my message box does
not pop up when it should - when there's a record the supervisor has
neither approved or rejected. What am I missing?

JD

"Rick Brandt" <ri*********@hotmail.com> wrote in message news:<bp*************@ID-98015.news.uni-berlin.de>...

If DCount("*", "tblPostVacDates", "[Approved]<>1 Or [Approved]<>2") >
0 Then


This needs to be an "AND" not an "OR". Otherwise all of the "1"s are
counted because they are not "2"s and all of the "2"s are counted because
they are not "1"s.

Nov 12 '05 #14

P: n/a
> "Rick Brandt" <ri*********@hotmail.com> wrote in message
news:<bp*************@ID-98015.news.uni-berlin.de>...

If DCount("*", "tblPostVacDates", "[Approved]<>1 Or [Approved]<>2") >
0 Then
This needs to be an "AND" not an "OR". Otherwise all of the "1"s are
counted because they are not "2"s and all of the "2"s are counted because
they are not "1"s.

<jd****@yahoo.com> wrote in message
news:75**************************@posting.google.c om... Thanks, Rick, I changed the "or" to "and", but now my message box does
not pop up when it should - when there's a record the supervisor has
neither approved or rejected. What am I missing?

What is the value of [Approved] when it is not a 1 or a 2? If it's Null then
you need to deal with that differently.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 Or Nz([Approved],0)<>2") 0 Then

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #15

P: n/a
Sorry it took me so long to respond to you to Thank You - I took 10
days of vacation during the Thanksgiving holidays. Another question -
in tblPostVacDates, I have a field for the year the days were taken
in. Is there any way in the statement below to tell Access to look
only at a certain year?

JD

"Rick Brandt" <ri*********@hotmail.com> wrote in message news:<bp*************@ID-98015.news.uni-berlin.de>...
What is the value of [Approved] when it is not a 1 or a 2? If it's Null then
you need to deal with that differently.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 Or Nz([Approved],0)<>2")
0 Then

Nov 12 '05 #16

P: n/a
If DCount("*", "tblPostVacDates", "(Nz([Approved],0)<>1 Or
Nz([Approved],0)<>2) AND Year([Approved])=2003")

.... maybe.
Nov 12 '05 #17

P: n/a
Wayne, it finally dawned on me yesterday why it seems that Access is
not ignoring the blank row in the table. Of course, it's ignoring it
- there are no values in it. What's happening is records for all
employees are in this table, so when I unload the form for John Doe's
vacations, Access is not only looking for John's records that are not
equal to the value of Approved, it's coming across the records of all
employees, some of which have not been approved or rejected by their
supervisors. So the message box pops up, even if all of John's
vacations were approved. How can I get it to look only for John's
records? The form I'm using is opened from an OnClick event of
another form and is filtered using the following:
"[Employees].[EmployeeNumber] IN (" & Left(strSQL, Len(strSQL) - 1) &
")".

Many thanks for your patience with me.

JD

jd****@yahoo.com (jd****@yahoo.com) wrote in message news:<75**************************@posting.google. com>...
I've completely reworked by database trying to solve my problem with
Access apparently not ignoring the blank row at the bottom. I changed
from 2 Yes/No fields in my table for Approved and Rejected to a single
field named Approved with a data type of Number. I deleted the check
boxes in my form and made an option group with the value of Approved
being 1 and Rejected 2. There are absolutely no values in the blank
row, but for some reason, my message box comes up even when all
records have been approved/rejected. When the records are filtered,
the only one showing is the blank record. I am totally at my wits
end. I've copied and pasted the modified code below. Is there
something I'm missing? I added another field to the test, but still
got the same results, unless I had my code wrong. DateTaken is a
field that will never have a value in an unused record. I typed
"[Approved]<>1 Or [Approved]<>2 Or [DateTaken]=Null". Thanks in
advance for your help. (BTW, I'm using Access 2K2 now - recently and
reluctantly upgraded from Access 97).

JD

If DCount("*", "tblPostVacDates", "[Approved]<>1 Or [Approved]<>2") >
0 Then
If MsgBox("You missed approving or rejecting a requested date.
Click OK to locate the record, or click Cancel to leave this form.",
vbOKCancel, "Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
Me!sbfrmPostVacDates.Form.FilterOn = False
End If
Me!sbfrmPostVacDates.Form.Filter = "[Approved]<>1 And
[Approved]<>2"
Me!sbfrmPostVacDates.Form.FilterOn = True
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<WV*******************@newssvr32.news.prodigy .com>...
I just tried it on both a query and a table and the field that shows as a new field (the
blank row at the bottom) is ignored. I included an autonumber field to make sure. If the
record hasn't been entered yet it just shows (AutoNumber) in that field for the new
record, not an actual value. If you are showing a value, there is something else going on.
However, you could add one more field to the test if you wanted. One that will always have
a value if the record has been saved and will never have a value in a new, unused record
and see if it is Null or not.

Nov 12 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.