message box to apperar IF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is what a I want to happen. When a teacher counts a student tardy in
their class for the second time, I want a message box to appear telling the
teacher to assign the student a 1 hour D-Hall. For the third offense, a 2
hour D-hall, fourth offense and every one after that, please send the student
to the office.

Currently I have frmTardy-Add set up to add the tardies and qryTardyCount to
count the tardies, but I have NO idea how to assign the message box different
messages. Can anyone help. I am using Access 2003 in access 2000 format.
 
There are a myriad of ways to do this; these are just two:

Select Case NoOfTardies
Case 1: MsgBox "Assign a 1 hour D-Hall"
Case > 2: MsgBox "Send student to office"
End Select

....or...

If NoOfTardies = 1 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf NoOfTardies > 2 Then
MsgBox "Send student to office"
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
 
Ok, tried to work the code in the AfterUpdate on the Tardy-Add form.

The query I am using to count the tardies has 3 fields, studentId,
TeacherID, and Count of TeacherId. This is the code I used to try to
activate the message boxes, but I get a compile "object required" error:

Private Sub Form_AfterUpdate()
If qryTardyCounter.TeacherCount = 2 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf qryTardyCounter.TeacherCount = 3 Then
MsgBox "Assign a 2 Hour D-Hall"
ElseIf qryTardyCounter.TeacherCount > 3 Then
MsgBox "Send student to office"
End If

End Sub

The StudentID field on the query is limited by the studentID combo box,
cboStudentID, from the form Tardy-Add.

What am I doing wrong?
--
Thanks As Always
Rip


Graham R Seach said:
There are a myriad of ways to do this; these are just two:

Select Case NoOfTardies
Case 1: MsgBox "Assign a 1 hour D-Hall"
Case > 2: MsgBox "Send student to office"
End Select

....or...

If NoOfTardies = 1 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf NoOfTardies > 2 Then
MsgBox "Send student to office"
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------
Click to expand...
 
OK, it seems you don't quite understand about how to use queries. You can't
just call the query; you need to use either a recordset or DLookup. Also,
you need to use StudentID and TeacherID as criteria for the query.

Assuming StudentID is given in a Textbox called "txtStudentID", and
TeacherID is given in a Textbox called "TeacherID" then...

Private Sub Form_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT [Count of TeacherId] " & _
"FROM qryTardyCounter " & _
"WHERE StudentID = " & Me!txtStudentID & " " & _
"AND TeacherID = " & Me!txtTeacherID

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
If rs.TeacherCount = 2 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf rs.TeacherCount = 3 Then
MsgBox "Assign a 2 Hour D-Hall"
ElseIf rs.TeacherCount > 3 Then
MsgBox "Send student to office"
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------

Ripper said:
Ok, tried to work the code in the AfterUpdate on the Tardy-Add form.

The query I am using to count the tardies has 3 fields, studentId,
TeacherID, and Count of TeacherId. This is the code I used to try to
activate the message boxes, but I get a compile "object required" error:

Private Sub Form_AfterUpdate()
If qryTardyCounter.TeacherCount = 2 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf qryTardyCounter.TeacherCount = 3 Then
MsgBox "Assign a 2 Hour D-Hall"
ElseIf qryTardyCounter.TeacherCount > 3 Then
MsgBox "Send student to office"
End If

End Sub

The StudentID field on the query is limited by the studentID combo box,
cboStudentID, from the form Tardy-Add.

What am I doing wrong?
Click to expand...
 
Graham you rock! You are correct, I don't understand how queries work. I am
self taught by an idiot =) Just trying to understand how this stuff goes
together. You recommend any light reading to pick up my skills?
--
Thanks As Always
Rip


Graham R Seach said:
OK, it seems you don't quite understand about how to use queries. You can't
just call the query; you need to use either a recordset or DLookup. Also,
you need to use StudentID and TeacherID as criteria for the query.

Assuming StudentID is given in a Textbox called "txtStudentID", and
TeacherID is given in a Textbox called "TeacherID" then...

Private Sub Form_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT [Count of TeacherId] " & _
"FROM qryTardyCounter " & _
"WHERE StudentID = " & Me!txtStudentID & " " & _
"AND TeacherID = " & Me!txtTeacherID

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
If rs.TeacherCount = 2 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf rs.TeacherCount = 3 Then
MsgBox "Assign a 2 Hour D-Hall"
ElseIf rs.TeacherCount > 3 Then
MsgBox "Send student to office"
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
Click to expand...
 
Sure,

Jeff Conrad MVP has a wide range of resources listed on his site:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------

Ripper said:
Graham you rock! You are correct, I don't understand how queries work. I
am
self taught by an idiot =) Just trying to understand how this stuff goes
together. You recommend any light reading to pick up my skills?
--
Thanks As Always
Rip


Graham R Seach said:
OK, it seems you don't quite understand about how to use queries. You
can't
just call the query; you need to use either a recordset or DLookup. Also,
you need to use StudentID and TeacherID as criteria for the query.

Assuming StudentID is given in a Textbox called "txtStudentID", and
TeacherID is given in a Textbox called "TeacherID" then...

Private Sub Form_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "SELECT [Count of TeacherId] " & _
"FROM qryTardyCounter " & _
"WHERE StudentID = " & Me!txtStudentID & " " & _
"AND TeacherID = " & Me!txtTeacherID

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
If rs.TeacherCount = 2 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf rs.TeacherCount = 3 Then
MsgBox "Assign a 2 Hour D-Hall"
ElseIf rs.TeacherCount > 3 Then
MsgBox "Send student to office"
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------

Ripper said:
Ok, tried to work the code in the AfterUpdate on the Tardy-Add form.

The query I am using to count the tardies has 3 fields, studentId,
TeacherID, and Count of TeacherId. This is the code I used to try to
activate the message boxes, but I get a compile "object required"
error:

Private Sub Form_AfterUpdate()
If qryTardyCounter.TeacherCount = 2 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf qryTardyCounter.TeacherCount = 3 Then
MsgBox "Assign a 2 Hour D-Hall"
ElseIf qryTardyCounter.TeacherCount > 3 Then
MsgBox "Send student to office"
End If

End Sub

The StudentID field on the query is limited by the studentID combo box,
cboStudentID, from the form Tardy-Add.

What am I doing wrong?
--
Thanks As Always
Rip


:

There are a myriad of ways to do this; these are just two:

Select Case NoOfTardies
Case 1: MsgBox "Assign a 1 hour D-Hall"
Case > 2: MsgBox "Send student to office"
End Select

....or...

If NoOfTardies = 1 Then
MsgBox "Assign a 1 hour D-Hall"
ElseIf NoOfTardies > 2 Then
MsgBox "Send student to office"
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------

Here is what a I want to happen. When a teacher counts a student
tardy
in
their class for the second time, I want a message box to appear
telling
the
teacher to assign the student a 1 hour D-Hall. For the third
offense,
a 2
hour D-hall, fourth offense and every one after that, please send
the
student
to the office.

Currently I have frmTardy-Add set up to add the tardies and
qryTardyCount
to
count the tardies, but I have NO idea how to assign the message box
different
messages. Can anyone help. I am using Access 2003 in access 2000
format.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top