Help with DCount function and filter

[jdph40]

I posted this problem previously and received excellent help from
Wayne Morgan. However, I still have an unanswered question. My form
(frmVacationWeeks) is opened from the OnClick event of a button on
another form (frmEmpList) which has a list box that contains the names
of all employees. When a name is selected in the list box and the
button is clicked, frmVacationWeeks opens and is filtered using the
following: "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")". What I'm trying to accomplish is when
frmVacationWeeks is closed, if all vacations posted in the subform
(sbfrmVacWeeks) for the employee shown have not been approved or
rejected by the supervisor, I want a message box to pop up and tell
the supervisor he missed a record. Here's the code that's in the
Unload event of the form:

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>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
End If

But what's happening is records for all employees are in the table, so
when I unload the form for, say, 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. 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?

Thanks,
JD

[Wayne Morgan]

Hi JD,

You need to add another filter item to the DCount statement to limit the
count to only John's items. You already have an "And" clause in the DCount
statement, you just need to add another one. You would probably do this
using the ID field for that person.

Example:

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2") > 0 Then
If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]=" & Me.txtPersonID) > 0 Then
Where Me.txtPersonID is the textbox on the form that has the value of the ID
field. If the ID is a number this will work, if it is text then it will need
to be enclosed in quotes.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]='" & Me.txtPersonID & "'") > 0 Then

--
Wayne Morgan
MS Access MVP
<jd****@yahoo.com> wrote in message
news:75*************************@posting.google.co m... I posted this problem previously and received excellent help from
Wayne Morgan. However, I still have an unanswered question. My form
(frmVacationWeeks) is opened from the OnClick event of a button on
another form (frmEmpList) which has a list box that contains the names
of all employees. When a name is selected in the list box and the
button is clicked, frmVacationWeeks opens and is filtered using the
following: "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")". What I'm trying to accomplish is when
frmVacationWeeks is closed, if all vacations posted in the subform
(sbfrmVacWeeks) for the employee shown have not been approved or
rejected by the supervisor, I want a message box to pop up and tell
the supervisor he missed a record. Here's the code that's in the
Unload event of the form:

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>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
End If

But what's happening is records for all employees are in the table, so
when I unload the form for, say, 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. 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?

Thanks,
JD

[jdph40]

Thank you so much, Wayne. Perfect! I knew I needed another filter, I
just didn't know how to go about doing it. Now I have a question
about closing the form. I have a button on the form to close it. I
have tried assigning a macro to the button to close the form and I
have used DoCmd.Close in the OnClick event of the button. With both,
after I click OK in the message box I get because of the Unload event
of the form, I get error message 3021 - no current record. If I close
the form with the x in the upper right-hand corner, the Unload event
of the form runs and everything works perfectly. According to the
online help, using the close action (macro) is the same as using the x
to close the form. If so, why do I get an error message? I could
delete the button and require the users to use the x to close the
form, but some of the users may have a problem with this. Any
suggestions?

Many thanks!
JD
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<yn****************@newssvr23.news.prodigy.co m>...

Hi JD,

You need to add another filter item to the DCount statement to limit the
count to only John's items. You already have an "And" clause in the DCount
statement, you just need to add another one. You would probably do this
using the ID field for that person.

Example:

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

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]=" & Me.txtPersonID) > 0 Then
Where Me.txtPersonID is the textbox on the form that has the value of the ID
field. If the ID is a number this will work, if it is text then it will need
to be enclosed in quotes.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]='" & Me.txtPersonID & "'") > 0 Then

--
Wayne Morgan
MS Access MVP

[Wayne Morgan]

Sometimes the easiest way around this sort of thing would be to just trap
the error in the Unload event IF EVERYTHING else works as expected. To do
this you would need to include an error handler in the event, which is a
good idea anyway because it keeps you from breaking into the code if there
is an error. The user will get an error message, but they won't be sent into
your code. Of course, for any error you handle, they won't even get the
error message if you don't want them to.

At the top of the event insert a line similar to

On Error GoTo HandleError
Then at the bottom, after your code place something similar to

'Last line of event code

CleanUp:
On Error Resume Next
'Place your cleanup code here, that way it will run even
'if you have an error.
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleError:
If Err.Number = 3021 Then Resume Next
MsgBox "Error # " & Err.Number & vbCrLf & Err.Description, vbOKOnly +
vbCritical, "Error"
Resume CleanUp

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

Thank you so much, Wayne. Perfect! I knew I needed another filter, I
just didn't know how to go about doing it. Now I have a question
about closing the form. I have a button on the form to close it. I
have tried assigning a macro to the button to close the form and I
have used DoCmd.Close in the OnClick event of the button. With both,
after I click OK in the message box I get because of the Unload event
of the form, I get error message 3021 - no current record. If I close
the form with the x in the upper right-hand corner, the Unload event
of the form runs and everything works perfectly. According to the
online help, using the close action (macro) is the same as using the x
to close the form. If so, why do I get an error message? I could
delete the button and require the users to use the x to close the
form, but some of the users may have a problem with this. Any
suggestions?

Many thanks!
JD
"Wayne Morgan" <co***************************@hotmail.com> wrote in

message news:<yn****************@newssvr23.news.prodigy.co m>...

Hi JD,

You need to add another filter item to the DCount statement to limit the
count to only John's items. You already have an "And" clause in the DCount statement, you just need to add another one. You would probably do this
using the ID field for that person.

Example:

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

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]=" & Me.txtPersonID) > 0 Then
Where Me.txtPersonID is the textbox on the form that has the value of the ID field. If the ID is a number this will work, if it is text then it will need to be enclosed in quotes.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]='" & Me.txtPersonID & "'") > 0 Then

--
Wayne Morgan
MS Access MVP

[jdph40]

I appreciate your help, Wayne. Merry Christmas!

JD

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<T3****************@newssvr22.news.prodigy.co m>...

Sometimes the easiest way around this sort of thing would be to just trap
the error in the Unload event IF EVERYTHING else works as expected. To do
this you would need to include an error handler in the event, which is a
good idea anyway because it keeps you from breaking into the code if there
is an error. The user will get an error message, but they won't be sent into
your code. Of course, for any error you handle, they won't even get the
error message if you don't want them to.

At the top of the event insert a line similar to

On Error GoTo HandleError
Then at the bottom, after your code place something similar to

'Last line of event code

CleanUp:
On Error Resume Next
'Place your cleanup code here, that way it will run even
'if you have an error.
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleError:
If Err.Number = 3021 Then Resume Next
MsgBox "Error # " & Err.Number & vbCrLf & Err.Description, vbOKOnly +
vbCritical, "Error"
Resume CleanUp

--
Wayne Morgan
MS Access MVP

[jdph40]

I am trying to add another criteria to the DCount statement below. I
am trying to add YearTaken, which is a text field. When I execute the
code as written below, I get a "Compile Error". Thanks in advance for
advice.

JD

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [EmployeeNumber]=" & Me.EmployeeNumber And
[YearTaken]='" & Me.YearTaken & "'") > 0 Then
If MsgBox("You failed to approve or reject a requested date(s).",
vbOKCancel, "Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
End If
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<yn****************@newssvr23.news.prodigy.co m>...

Hi JD,

You need to add another filter item to the DCount statement to limit the
count to only John's items. You already have an "And" clause in the DCount
statement, you just need to add another one. You would probably do this
using the ID field for that person.

Example:

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

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]=" & Me.txtPersonID) > 0 Then
Where Me.txtPersonID is the textbox on the form that has the value of the ID
field. If the ID is a number this will work, if it is text then it will need
to be enclosed in quotes.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]='" & Me.txtPersonID & "'") > 0 Then

--
Wayne Morgan
MS Access MVP

[fredg]

jd****@yahoo.com wrote:

I am trying to add another criteria to the DCount statement below. I
am trying to add YearTaken, which is a text field. When I execute the
code as written below, I get a "Compile Error". Thanks in advance for
advice.

JD

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [EmployeeNumber]=" & Me.EmployeeNumber And
[YearTaken]='" & Me.YearTaken & "'") > 0 Then
If MsgBox("You failed to approve or reject a requested date(s).",
vbOKCancel, "Supervisor Action Required") = vbCancel Then Exit Sub
Cancel = True
Else
End If
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:<yn****************@newssvr23.news.prodigy.co m>...

Hi JD,

You need to add another filter item to the DCount statement to limit the
count to only John's items. You already have an "And" clause in the DCount
statement, you just need to add another one. You would probably do this
using the ID field for that person.

Example:

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

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]=" & Me.txtPersonID) > 0 Then
Where Me.txtPersonID is the textbox on the form that has the value of the ID
field. If the ID is a number this will work, if it is text then it will need
to be enclosed in quotes.

If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [PersonID]='" & Me.txtPersonID & "'") > 0 Then

--
Wayne Morgan
MS Access MVP

You are missing the & " symbols after
= " & Me.EmployeeNumber
should have been:
= " & Me.EmployeeNumber & "

Also, since the field's are all a member of the form object's family,
Me! (bang) would be more appropriate here than Me. (dot).
And, since there is no Else in the If..Then.. Else statement, you don't
need to include it.
I also think then Cancel = True comes before the Exit Sub if there were
to be additional code in the Sub procedure you didn't want to run.
You don't need the Exit Sub if there is no other code.

Try:
If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [EmployeeNumber]=" & Me!EmployeeNumber & " And
[YearTaken]='" & Me!YearTaken & "'") > 0 Then

If MsgBox("You failed to approve or reject a requested date(s).",
vbOKCancel, "Supervisor Action Required") = vbCancel Then
Cancel = True
Exit Sub ' Optional if no other code to run.
End If

--
Fred
Please reply only to this newsgroup.
I do not respond to personal e-mail.

[jdph40]

Thanks for your help, Fred!

JD

fredg <fg******@example.invalid> wrote in message news:<1P**********************@bgtnsc05-news.ops.worldnet.att.net>...

You are missing the & " symbols after
= " & Me.EmployeeNumber
should have been:
= " & Me.EmployeeNumber & "

Also, since the field's are all a member of the form object's family,
Me! (bang) would be more appropriate here than Me. (dot).
And, since there is no Else in the If..Then.. Else statement, you don't
need to include it.
I also think then Cancel = True comes before the Exit Sub if there were
to be additional code in the Sub procedure you didn't want to run.
You don't need the Exit Sub if there is no other code.

Try:
If DCount("*", "tblPostVacDates", "Nz([Approved],0)<>1 And
Nz([Approved],0)<>2 And [EmployeeNumber]=" & Me!EmployeeNumber & " And
[YearTaken]='" & Me!YearTaken & "'") > 0 Then

If MsgBox("You failed to approve or reject a requested date(s).",
vbOKCancel, "Supervisor Action Required") = vbCancel Then
Cancel = True
Exit Sub ' Optional if no other code to run.
End If