message box to apperar IF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ripper - 30 Jul 2005 16:01 GMT

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.

Signature

Thanks As Always
Rip

Reply to this Message

Graham R Seach - 30 Jul 2005 16:25 GMT

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
[quoted text clipped - 10 lines]
> different
> messages. Can anyone help. I am using Access 2003 in access 2000 format.

Reply to this Message

Ripper - 30 Jul 2005 18:48 GMT

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?

Signature

Thanks As Always
Rip

> There are a myriad of ways to do this; these are just two:
>
[quoted text clipped - 32 lines]
> > different
> > messages. Can anyone help. I am using Access 2003 in access 2000 format.

Reply to this Message

Graham R Seach - 31 Jul 2005 00:21 GMT

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)
---------------------------

> Ok, tried to work the code in the AfterUpdate on the Tardy-Add form.
>
[quoted text clipped - 58 lines]
>> > messages. Can anyone help. I am using Access 2003 in access 2000
>> > format.

Reply to this Message

Ripper - 31 Jul 2005 02:43 GMT

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?

Signature

Thanks As Always
Rip

> 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,
[quoted text clipped - 99 lines]
> >> > messages. Can anyone help. I am using Access 2003 in access 2000
> >> > format.

Reply to this Message

Graham R Seach - 31 Jul 2005 15:17 GMT

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#Database
Design101

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

> Graham you rock! You are correct, I don't understand how queries work. I
> am
[quoted text clipped - 109 lines]
>> >> > messages. Can anyone help. I am using Access 2003 in access 2000
>> >> > format.

Reply to this Message