Dim conn As ADODB.Connection
Dim stud As ADODB.Recordset
Dim a As Long
Dim AddNewStatus As Boolean
Dim sql As String
Private Sub Form_Load()
connserver
Set stud = New ADODB.Recordset
sql = "select * from studrec"
stud.CursorLocation = adUseClient
stud.Open sql, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGrid1.DataSource = stud
DataGrid1.Refresh
stud.MoveFirst
sortname
End Sub
Private Sub connserver()
  Set conn = New ADODB.Connection
  conn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;Database=advising;User=root;Password=12345; OPTION=3"
conn.Open
End Sub
Private Sub saddcmd_Click()
sortno
stud.MoveLast
snumber.Text = Val(stud!studno) + 1
spass.Text = "0000" + Format(Val(stud!OrdNo) + 1)
sname.Text = ""
ssched.Text = ""
scourse.Text = ""
syear.Text = ""
sname.Locked = False
ssched.Locked = False
scourse.Locked = False
syear.Locked = False
AddNewStatus = True
sortname
stud.MoveFirst
End Sub
Private Sub sname_Change()
CheckEmpty
End Sub
Private Sub scourse_Change()
CheckEmpty
End Sub

Private Sub snumber_Change()
CheckEmpty
End Sub

Private Sub spass_Change()
CheckEmpty
End Sub

Private Sub ssched_Change()
CheckEmpty
End Sub



Private Sub syear_Change()
CheckEmpty
End Sub
Private Sub ssavecmd_Click()
If DupCheck(sname.Text) = True And AddNewStatus = True Then
MsgBox "Duplicate Record ", , "Warning"
Set DataGrid1.DataSource = stud
Else
stud.AddNew
stud!StudName = sname.Text
stud!studno = snumber.Text
stud!OrdNo = spass.Text
stud!Sched = ssched.Text
stud!Course = scourse.Text
stud!Year = syear.Text
stud.Update
stud.UpdateBatch adAffectAll
End If
End Sub
Private Sub studcmd_Click()
studform.Visible = True
gradeform.Visible = False
schedform.Visible = False
End Sub
Private Sub schedcmd_Click()
studform.Visible = False
gradeform.Visible = True
schedform.Visible = False
End Sub
Private Sub gradecmd_Click()
studform.Visible = False
gradeform.Visible = False
schedform.Visible = True
End Sub
Private Sub Text1_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        If CodeSearch(Text1) = False Then
            NameSearch Text1
        End If
        Text1 = ""
    End If
End Sub
Private Function CodeSearch(xSearch As String) As Boolean
    Dim stud As ADODB.Recordset
    Dim sql As String
    Set stud = New ADODB.Recordset
    stud.CursorLocation = adUseServer
    sql = "select * from studrec where StudNo LIKE '" & xSearch & "%'"
    stud.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
    If stud.EOF = True Then
        stud.Close
        Set stud = Nothing
        Exit Function
    End If
    If IsNull(stud!StudName) Then
        sname.Text = ""
        snumber.Text = ""
        spass.Text = ""
        ssched.Text = ""
        scourse.Text = ""
        syear.Text = ""
        CodeSearch = False
    Else
        sname.Text = stud!StudName
        snumber.Text = stud!studno
        spass.Text = stud!OrdNo
        ssched.Text = stud!Sched
        scourse.Text = stud!Course
        syear.Text = stud!Year
       CodeSearch = True
    End If
    stud.Close
    Set stud = Nothing
End Function
Private Function NameSearch(xSearch As String) As Boolean
    Dim stud As ADODB.Recordset
    Dim sql As String
    Set stud = New ADODB.Recordset
    stud.CursorLocation = adUseServer
    sql = "select * from studrec where StudName LIKE '" & xSearch & "%'"
    stud.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
    If stud.EOF = True Then
        stud.Close
        Set stud = Nothing
        Exit Function
    End If
    If IsNull(stud!StudName) Then
        sname.Text = ""
        snumber.Text = ""
        spass.Text = ""
        ssched.Text = ""
        scourse.Text = ""
        syear.Text = ""
        NameSearch = False
    Else
        sname.Text = stud!StudName
        snumber.Text = stud!studno
        spass.Text = stud!OrdNo
        ssched.Text = stud!Sched
        scourse.Text = stud!Course
        syear.Text = stud!Year
     NameSearch = True
    End If
    stud.Close
    Set stud = Nothing
End Function
Private Function DupCheck(chkID As String) As Boolean
    Dim stud As ADODB.Recordset
    Dim sql As String
    Set stud = New ADODB.Recordset
    stud.CursorLocation = adUseServer
    sql = "select * from studrec where StudName = '" & chkID & "'"
    stud.Open sql, conn, adOpenStatic, adLockOptimistic, adCmdText
    If stud.EOF = True Then
        stud.Close
        Set rs = Nothing
        Exit Function
    End If
    If chkID = stud!StudName Then
        DupCheck = True
    Else
        DupCheck = False
    End If
    stud.Close
    Set stud = Nothing
End Function
Private Sub CheckEmpty()
    If AddNewStatus = True Then
        If Len(sname.Text) = 0 Or Len(snumber.Text) = 0 Or spass.Text = "" Or scourse.Text = "" Or syear.Text = "" Then
            ssavecmd.Enabled = False
        Else
            ssavecmd.Enabled = True
        End If
    End If
End Sub
Private Sub sortname()
Dim ColIndex As Integer
Dim sortField As String
Dim sortString As String
sortField = DataGrid1.Columns("StudName").Caption
    sortString = sortField & " Asc"
stud.Sort = sortString
End Sub
Private Sub sortno()
Dim ColIndex As Integer
Dim sortField As String
Dim sortString As String
sortField = DataGrid1.Columns("StudNo").Caption
    sortString = sortField & " Asc"
stud.Sort = sortString
End Sub