VB code

  • Thread starter Thread starter Eric G
  • Start date Start date
E

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)
 
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)
 
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
 
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=?=
 
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
Click to expand...


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
 
Back
Top