VB code

[Eric G]

I have a password form that checks if the user password is correct and
if so, opens a checklist form as follows:

Private Sub passOK_Click()
On Error GoTo Err_passOK_Click

If Not IsNull(Me!cboUserID) Then
If Me!txtPassword = Me!cboUserID.Column(1) Then
DoCmd.OpenForm "frmCheckList"

I have a query named StatTeachRanked with two fields:
CountOf#Det and TeacherID (UserID)
Typical results of the select query might be,
4 EGR
or 3 ABO
or 2 JDI

I would like to add code so that if the UserID keyed in on the
password form is used as the TeacherID (UserID) in the StatTeachRanked
query with a result such that the CountOf#Det is 4 or greater, then
DoCmd.OpenForm "frmCheckList" would not executed above, and another
command would be run.

Guidance in setting this up would be much appreciated.
TIA Eric (A2K)

 

[Guest]

Hi Eric,

try these code before Docmd.Openform 'frm_CheckList"

If nz(dlookup("CountOf#Det","StatTeachRanked","TeacherID='" & UserID & "'"),0)<4 then
Docmd.Openform 'frm_CheckList"
else
'other command
end if

Be carefull when creating the criteria ("TeacherID=.....") since it is text/string you need to enclose the UserID with qoute, like this, TeacherID = 'ERG'


Hope this help.

----- Eric G wrote: -----

I have a password form that checks if the user password is correct and
if so, opens a checklist form as follows:

Private Sub passOK_Click()
On Error GoTo Err_passOK_Click

If Not IsNull(Me!cboUserID) Then
If Me!txtPassword = Me!cboUserID.Column(1) Then
DoCmd.OpenForm "frmCheckList"

I have a query named StatTeachRanked with two fields:
CountOf#Det and TeacherID (UserID)
Typical results of the select query might be,
4 EGR
or 3 ABO
or 2 JDI

I would like to add code so that if the UserID keyed in on the
password form is used as the TeacherID (UserID) in the StatTeachRanked
query with a result such that the CountOf#Det is 4 or greater, then
DoCmd.OpenForm "frmCheckList" would not executed above, and another
command would be run.

Guidance in setting this up would be much appreciated.
TIA Eric (A2K)

 

[Eric G]

Hi Anon,

I tried your code pretty much as you gave it and I'm getting this
message:

'Syntax error in date in query expression 'CountOf#Detentions'.

More background info. Here is the SQL behind the StatTeachRanked
select query:

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;

DateDet is a standard date field, (medium date format) in table
Detentions.

I hope this will help you understand the error message I'm getting.
TIA

Eric

 

[Guest]

Hi Eric

you need to put CountOf#Detentions between brackets []. I guess the code should look like this

If nz(dlookup("[CountOf#Detentions]","StatTeachRanked","TeacherID='" & UserID & "'"),0)<4 then
Docmd.Openform 'frm_CheckList
els
'other comman
end i

You can also used dcount for this purpose
If dcount("DateDet","Detentions","TeacherID='" & UserID & "'")<4 then
Docmd.Openform 'frm_CheckList
els
'other comman
end i

HTH. BTW, my name is Trias
----- Eric G wrote: ----

Hi Anon

I tried your code pretty much as you gave it and I'm getting thi
message

'Syntax error in date in query expression 'CountOf#Detentions'

More background info. Here is the SQL behind the StatTeachRanke
select query

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

DateDet is a standard date field, (medium date format) in tabl
Detentions

I hope this will help you understand the error message I'm getting
TI

Eri



On Tue, 2 Dec 2003 19:46:10 -0800, "=?Utf-8?B?VHJpYXM=?=

 

[Eric G]

Hi Trias,

Hi Eric,

you need to put CountOf#Detentions between brackets []. I guess the code should look like this:

If nz(dlookup("[CountOf#Detentions]","StatTeachRanked","TeacherID='" & UserID & "'"),0)<4 then
Docmd.Openform 'frm_CheckList"
else
'other command
end if

You can also used dcount for this purpose:
If dcount("DateDet","Detentions","TeacherID='" & UserID & "'")<4 then
Docmd.Openform 'frm_CheckList"
else
'other command
end if

Thanks for your tip. I messed around with it a bit and finally got it
to work with the following code using your second method:

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

I had to put [] around a few of the terms as well as use [cboUserID]
instead of [UserID]. The password form has [cboUserID] as the name of
the userID control.

Well I'm glad it's working now. Thanks again for your help!

Eric