student violation monitoring

[briandon]

how to compute the number of violations per student and per date and gives warning and turn into offenses. Please help me about my system

[Nightwalker83]

What have you tried so far?

[briandon]

Here's the code:

Private Sub lvButtons_H2_Click()
Err.Clear
 
If txtstudentidno.Text = "" Or _
txtlastname.Text = "" Or txtfirstname.Text = "" Or txtmi.Text = "" Or txtcourse.Text = "" Or txtsection.Text = "" Or _
cboViolation.Text = "" Then
 
MsgBox "Please Fill All The Fields", vbInformation, "Fill All the Fields"
 
Exit Sub
Else
 
Set conn = New ADODB.Connection
conn.Open ConnectString
sql = "SELECT * FROM STUDENTVIOLATION"
Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockOptimistic
    With rs
        .AddNew
        !StudNo = txtstudentidno.Text
        !LastName = txtlastname.Text
        !FirstName = txtfirstname.Text
        !Mi = txtmi.Text
        !course = txtcourse.Text
        !section = txtsection.Text
        !ViolationDesc = cboViolation.Text
        !Date = Label6.Caption
        !Time = Label3.Caption
        .Update
 
 
End With
conn.Close
Set conn = Nothing
Set conn = New ADODB.Connection
conn.Open ConnectString
Dim StudNo As String
Dim Violation As String
StudNo = txtstudentidno.Text
Violation = cboViolation.Text
sql = "select * from studentviolation where studentviolation.StudNo ='" & Trim$(StudNo) & "' And studentviolation.ViolationDesc ='" & Violation & "' Order By StudNo='" & Trim$(txtstudentidno.Text) & "'"
    Set rs = New ADODB.Recordset
    rs.Open sql, conn, adOpenForwardOnly, adLockReadOnly
    StudNo = rs.RecordCount
    rs.Filter = "StudNo ='" & rs!StudNo & "'"
        vio = vio + 1
        If vio = 1 Then
        Form1.Label3.Caption = "This is First Violation"
        Form1.Label2.Caption = "Report to the SSO"
        Form1.Show
        Set conn = New ADODB.Connection
        conn.Open ConnectString
        sql = "select concat (student.LastName, ', ', student.FirstName, ' ', student.Mi, '. ') as StudName from student"
        Set rs = New ADODB.Recordset
        rs.Open sql, conn
        End If
        If vio = 2 Then
        Form1.Label3.Caption = "This is Second Violation"
        Form1.Label2.Caption = "Report to the SSO"
        Form1.Show
        Set conn = New ADODB.Connection
        conn.Open ConnectString
        sql = "select concat (student.LastName, ', ', student.FirstName, ' ', student.Mi, '. ') as StudName from student"
        Set rs = New ADODB.Recordset
        rs.Open sql, conn
        End If
        If vio = 3 Then
        Form1.Label3.Caption = "This is Third Violation"
        Form1.Label2.Caption = "Report to the SSO"
        Form1.Show
        Set conn = New ADODB.Connection
        conn.Open ConnectString
        sql = "select concat (student.LastName, ', ', student.FirstName, ' ', student.Mi, '. ') as StudName from student"
        Set rs = New ADODB.Recordset
        rs.Open sql, conn
        End If
 
        If vio = 4 Then
                frmMain.Label5.Caption = "Warning!!!"
                frmMain.Label5.Visible = True
                frmMain.Label5.Enabled = True
                End If
        If vio = 5 Then
                Form5.Show
                frmOffenses.txtstudentidno.Text = rs!StudNo
                frmOffenses.txtlastname.Text = rs!LastName
                frmOffenses.txtfirstname.Text = rs!FirstName
                frmOffenses.txtmi.Text = rs!Mi
                frmOffenses.txtcourse.Text = rs!course
                frmOffenses.txtsection.Text = rs!section
                Call GetOffenseInfo
                frmMain.Label5.Caption = ""
                frmMain.Label5.Visible = False
                frmMain.Label5.Enabled = False
                txtstudentidno.Text = ""
        End If
        Form1.Label2.Caption = "Report to the SSO"
        Form1.Show
        txtstudentidno.Text = ""
        txtlastname.Text = ""
        txtfirstname.Text = ""
        txtmi.Text = ""
        txtcourse.Text = ""
        txtsection.Text = ""
        Image2.Visible = False
        Image1.Visible = True
    conn.Close
End If
 
End Sub

[Nightwalker83]

Could you please wrap your code in [highlight="vb"][/highlight] tags so it is easier for forum members too read. Thank you! Although, I don't think you have the ability to edit your posts being a new user so I will tell a staff member to do it.

Edit:

What is vio? You need to define it as string, etc (whatever type it is suppose to be). Also it doesn't appear that you initialize vio anywhere? Why not save vio in the database too?

[briandon]

how?

[Nightwalker83]

how?

How to save it in the database?

Edit:

In answer to my question check the Database Development FAQ.

[briandon]

yes, saving into database

[Nightwalker83]

I edited my previous post! Check the link I posted.