DLookup and queries

[Eric G]

I have a select query named StatTeachRanked as follows:

SELECT Count(Detentions.DateDet) AS [CountOf#Detentions],
Detentions.TeacherID
FROM Detentions
GROUP BY Detentions.TeacherID
HAVING (((Detentions.TeacherID) Is Not Null))
ORDER BY Count(Detentions.DateDet) DESC , Detentions.TeacherID;

Can the following form coding be used that relies on the
StatTeachRanked query?

''If Nz(DLookup("[CountOf#Detentions]", "StatTeachRanked",
"TeacherID='" & UserID & "'"), 0) < 4 Then
''DoCmd.OpenForm "frmCheckList"

It seems that this code can be used but it is not returning the
desired results.
For example TeacherID EGR who has a CountOf#Detentions of 7
should not have frmCheckList opening, but such is the case right now.

Is there something obviously wrong that sticks out here?

TIA Eric

 

[John Vinson]

I have a select query named StatTeachRanked as follows:

SELECT Count(Detentions.DateDet) AS [CountOf#Detentions],
Detentions.TeacherID
FROM Detentions
GROUP BY Detentions.TeacherID
HAVING (((Detentions.TeacherID) Is Not Null))
ORDER BY Count(Detentions.DateDet) DESC , Detentions.TeacherID;

Can the following form coding be used that relies on the
StatTeachRanked query?

''If Nz(DLookup("[CountOf#Detentions]", "StatTeachRanked",
"TeacherID='" & UserID & "'"), 0) < 4 Then
''DoCmd.OpenForm "frmCheckList"

It seems that this code can be used but it is not returning the
desired results.
For example TeacherID EGR who has a CountOf#Detentions of 7
should not have frmCheckList opening, but such is the case right now.

I'm not sure: if you open StatTeachRanked does it return the correct
number of detentions?

You code could use the DCount() function and avoid the query. The
check for TeacherID NOT NULL is unnecessary since you're using a
criterion on it anyway (which a NULL record will not match):

If DCount("*", "[Detentions]", "[TeacherID] = '" & UserID & "'") < 4

 

[Guest]

Try doing it all in a DCOUNT function

Dim i As Intege
i = DCOUNT("[DateDet]","Detentions","[TeacherID]='" & UserID & "'"

If i < ..

Hope this helps

Howard Brod


----- Eric G (YAC) wrote: ----

I have a select query named StatTeachRanked as follows

SELECT Count(Detentions.DateDet) AS [CountOf#Detentions]
Detentions.TeacherI
FROM Detention
GROUP BY Detentions.TeacherI
HAVING (((Detentions.TeacherID) Is Not Null)
ORDER BY Count(Detentions.DateDet) DESC , Detentions.TeacherID

Can the following form coding be used that relies on th
StatTeachRanked query

''If Nz(DLookup("[CountOf#Detentions]", "StatTeachRanked"
"TeacherID='" & UserID & "'"), 0) < 4 The
''DoCmd.OpenForm "frmCheckList

It seems that this code can be used but it is not returning th
desired results
For example TeacherID EGR who has a CountOf#Detentions of
should not have frmCheckList opening, but such is the case right now

Is there something obviously wrong that sticks out here

TIA Eri

 

[Eric G]

Hi John,

I'm not sure: if you open StatTeachRanked does it return the correct
number of detentions?

Yes the StatTeachRanked query returns the correct totals for all the
teachers who have entered detentions.

You code could use the DCount() function and avoid the query. The
check for TeacherID NOT NULL is unnecessary since you're using a
criterion on it anyway (which a NULL record will not match):

If DCount("*", "[Detentions]", "[TeacherID] = '" & UserID & "'") < 4

It's interesting that you have made this suggestion.
A while back I had asked for possible code to use and the following
two were suggested:


'If DCount("[DateDet]", "Detentions", "[TeacherID]='" & [cboUserID] &
"'") < 4 Then
'DoCmd.OpenForm "frmCheckList"

If Nz(DLookup("[CountOf#Detentions]", "StatTeachRanked", "TeacherID='"
& UserID & "'"), 0) < 4 Then
DoCmd.OpenForm "frmCheckList"


I have been using the first one above (which is very close to what you
suggested) , but as the Detention table grows, it takes longer for the
count to be completed.

Without giving much thought, I started trying out the second one above
(which isn't working), but now I see it won't improve the speed issue.
Because the same growing Detentions table must first be queried before
a DLookup can take place.

So.... what I am now trying to get to work (I can use some help here)
is the following. I've added a CLCount field to my Teachers table.
As the Teacher logs in on the Password form
(UserID and Password), I need the CLCount field in the Teachers table
to be checked for a value <4 and then, for that value to be increased
by one. The following code was suggested by PCDatasheet to accomplish
this, but nothing happens when I use it:

If IsNull(Me!CLCount) Or Me!ClCount < 4 Then
<<Do XYZ >>
End If
Me!CLCount = Me!CLCount +1

TIA Eric

 

[Eric G]

Hi Howard,

Thanks for your help!
It's interesting that the code you suggest is very similar to the code
I have been using up until now.
Please see my response to John V.

Eric