Dim conn As ADODB.Connection
Dim subject, sched, stud0, admin As ADODB.Recordset
Dim subsql, sec, sql, adm As String
Dim addcs, addact, AddNewStatus, Adding As Boolean
Private Sub actcmd_Click()
dbguide.Text = "ACT11"
cscode.Locked = True
csdesc.Locked = True
csunits.Locked = True
csadd.Enabled = True
actcmd.Enabled = False
bscscmd.Enabled = True
cssave.Enabled = False
csyear.Locked = False
cssem.Locked = False
actsched.Visible = True
cssched.Visible = False
csshowgrid
csdatashow
End Sub

Private Sub actsched_Click()
ysguide.Text = "ACT11"
secguide.Text = "ACT11a"
schfrm.Visible = True
schedform.Visible = False
schedcmd.Enabled = True
schactshowgrid
subdatashow
schdatashow
End Sub

Private Sub admincmd_Click()
adminfrmpass.Show vbModal
z:
On Error GoTo a
Set admin = New ADODB.Recordset
adm = "select * from adminrec"
admin.CursorLocation = adUseClient
admin.Open adm, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGrid8.DataSource = admin
DataGrid8.Refresh
adminshow
Exit Sub
a:
conn.Execute "CREATE TABLE adminrec(AdminName char(50), UserName char(50), Password char(10), Position char(10))", , adExecuteNoRecords
GoTo z
End Sub
Private Sub adminshow()
If admin.BOF Then
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
pos.Text = ""
amname.Text = ""
alname.Text = ""
afname.Text = ""
Text2.Locked = False
Text3.Locked = False
Text4.Locked = False
pos.Visible = False
alname.Visible = False
afname.Visible = False
amname.Visible = False
Text2.Visible = True
Text5.Visible = True
cmdadmadd.Enabled = True
cmdadmdel.Enabled = False
cmdadmsave.Enabled = False
Adding = False
Else
sortField = DataGrid8.Columns("AdminName").Caption
    sortString = sortField & " Asc"
admin.Sort = sortString
Text2.Text = admin!AdminName
Text3.Text = admin!UserName
Text4.Text = admin!Password
Text5.Text = admin!Position
Text2.Locked = False
Text3.Locked = False
Text4.Locked = False
pos.Visible = False
alname.Visible = False
afname.Visible = False
amname.Visible = False
Text2.Visible = True
Text5.Visible = True
cmdadmadd.Enabled = True
cmdadmdel.Enabled = True
cmdadmsave.Enabled = False
Adding = False
End If
DataGrid8.Enabled = True
End Sub
Private Sub bscscmd_Click()
dbguide.Text = "CS11"
cscode.Locked = True
csdesc.Locked = True
csunits.Locked = True
csadd.Enabled = True
actcmd.Enabled = True
bscscmd.Enabled = False
cssave.Enabled = False
csyear.Locked = False
cssem.Locked = False
actsched.Visible = False
cssched.Visible = True
csshowgrid
csdatashow
End Sub

Private Sub cmdadmadd_Click()
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
pos.Text = ""
amname.Text = ""
alname.Text = ""
afname.Text = ""
Text2.Locked = False
Text3.Locked = False
Text4.Locked = False
pos.Visible = True
alname.Visible = True
afname.Visible = True
amname.Visible = True
Text2.Visible = False
Text5.Visible = False
cmdadmadd.Enabled = False
cmdadmdel.Enabled = False
DataGrid8.Enabled = False
Adding = True
End Sub
Private Sub cmdadmcan_Click()
adminshow
End Sub
Private Sub cmdadmdel_Click()
conn.Execute "DELETE FROM adminrec Where AdminName = '" & Text2.Text & "' and UserName ='" & Text3.Text & "' and Position ='" & Text5.Text & "'"
Set admin = New ADODB.Recordset
adm = "select * from adminrec"
admin.CursorLocation = adUseClient
admin.Open adm, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGrid8.DataSource = admin
DataGrid8.Refresh
adminshow
End Sub

Private Sub cmdadmsave_Click()
Text2.Text = Format(alname.Text) + ", " + Format(afname.Text) + " " + Format(amname.Text) + "."
If dupadmin(Text2.Text, Text3.Text) = True Then
MsgBox "duplicate record!"
Else
admin.AddNew
admin!AdminName = Text2.Text
admin!UserName = Text3.Text
admin!Password = Text4.Text
admin!Position = pos.Text
admin.Update
admin.UpdateBatch adAffectAll
adminshow
Adding = False
End If
DataGrid8.Enabled = True
End Sub
Private Function dupadmin(chkname As String, chkuname As String) As Boolean
Dim dupsch As ADODB.Recordset
Dim sqldupsch As String
    Set dupsch = New ADODB.Recordset
    sqldupsch = "select * from adminrec where AdminName= '" & chkname & "' or UserName= '" & chkuname & "'"
    dupsch.CursorLocation = adUseServer
    dupsch.Open sqldupsch, conn, adOpenStatic, adLockOptimistic, adCmdText
    If dupsch.EOF = True Then
        dupsch.Close
        Set dupsch = Nothing
        Exit Function
    End If
    If chkname = dupsch!AdminName Or chkuname = dupsch!UserName Then
        dupadmin = True
    Else
        dupadmin = False
    End If
    dupsch.Close
    Set dupsch = Nothing
End Function
Private Sub Command1_Click()
End
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 CheckEmpty()
    If AddNewStatus = True Then
        If Len(mname.Text) = 0 Or Len(lname.Text) = 0 Or Len(gname.Text) = 0 Or Len(snumber.Text) = 0 Or Len(spass.Text) = 0 Or Len(scourse.Text) = 0 Or Len(syear.Text) = 0 Or Len(ssched.Text) = 0 Then
            ssavecmd.Enabled = False
        Else
            ssavecmd.Enabled = True
        End If
    End If
End Sub
Private Sub DataGrid1_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub DataGrid8_Click()
Text2.Text = DataGrid8.Columns(0)
Text3.Text = DataGrid8.Columns(1)
Text4.Text = DataGrid8.Columns(2)
Text5.Text = DataGrid8.Columns(3)
End Sub

Private Sub DataGrid8_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub gradecmd_Click()
Text6.Text = "s" + snumber.Text
gradefrm.Text6.Text = "s" + snumber.Text
gradefrm.gfname.Text = stud0!StudName
gradefrm.gfnumber.Text = stud0!StudNo
gradefrm.gfsched.Text = stud0!sched
gradefrm.gfcourse.Text = stud0!Course
gradefrm.gfyear.Text = stud0!Year
gradefrm.Show vbModal
End Sub
Private Sub sortname()
sortField = DataGrid1.Columns("StudName").Caption
    sortString = sortField & " Asc"
stud0.Sort = sortString
End Sub
Private Sub sortno()
sortField = DataGrid1.Columns("StudNo").Caption
    sortString = sortField & " Asc"
stud0.Sort = sortString
End Sub
Private Sub saddcmd_Click()
DataGrid1.Enabled = False
sortno
On Error GoTo a
stud0.MoveLast
snumber.Text = Val(stud0!StudNo) + 1
spass.Text = "0000" + Format(Val(stud0!OrdNo) + 1)
z:
sname.Text = ""
ssched.Text = ""
scourse.Text = ""
syear.Text = ""
gradecmd.Enabled = False
saddcmd.Enabled = False
sdelcmd.Enabled = False
ssched.Locked = False
scourse.Locked = False
syear.Locked = False
gname.Locked = False
lname.Locked = False
mname.Locked = False
sname.Visible = False
gname.Visible = True
lname.Visible = True
mname.Visible = True
AddNewStatus = True
sortname
stud0.MoveFirst
Exit Sub
a:
snumber.Text = "10001"
spass.Text = "00001"
GoTo z
End Sub

Private Sub scancelcmd_Click()
clear
End Sub
Private Sub clear()
sname.Text = ""
gname.Text = ""
lname.Text = ""
mname.Text = ""
snumber.Text = ""
spass.Text = ""
scourse.Text = ""
ssched.Text = ""
syear.Text = ""
ssched.Locked = True
scourse.Locked = True
syear.Locked = True
saddcmd.Enabled = True
sdelcmd.Enabled = True
ssavecmd.Enabled = False
gname.Visible = False
lname.Visible = False
mname.Visible = False
sname.Visible = True
DataGrid1.Enabled = True
gshow
dshow
End Sub
Private Sub sdelcmd_Click()
conn.Execute "DELETE FROM stud Where StudName = '" & sname.Text & "'"
gshow
dshow
End Sub
Private Sub snumber_Change()
CheckEmpty
End Sub
Private Sub spass_Change()
CheckEmpty
End Sub
Private Sub gname_Change()
CheckEmpty
End Sub
Private Sub gname_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case Asc(vbCr)
            KeyAscii = 0
        Case 8, 32
        Case 97 To 122
        Case 65 To 90
        Case Else
            KeyAscii = 0
    End Select
End Sub
Private Sub scourse_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub ssched_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub mname_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub syear_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub lname_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case Asc(vbCr)
            KeyAscii = 0
        Case 8, 32
        Case 97 To 122
        Case 65 To 90
        Case Else
            KeyAscii = 0
    End Select
End Sub
Private Sub lname_Change()
CheckEmpty
End Sub
Private Sub mname_Click()
CheckEmpty
End Sub
Private Function DupCheck(chkID As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    b = "select * from stud where StudName = '" & chkID & "'"
    a.CursorLocation = adUseServer
    a.Open b, conn, adOpenStatic, adLockOptimistic, adCmdText
    If a.EOF = True Then
        a.Close
        Set a = Nothing
        Exit Function
    End If
    If chkID = stud0!StudName Then
        DupCheck = True
    Else
        DupCheck = False
    End If
    a.Close
    Set a = Nothing
End Function
Private Sub ssavecmd_Click()
sname.Text = Format(lname.Text) + ", " + Format(gname.Text) + " " + Format(mname.Text) + "."
If DupCheck(sname.Text) = True And AddNewStatus = True Then
MsgBox "Duplicate Record ", , "Warning"
Set DataGrid1.DataSource = stud0
Else
stud0.AddNew
sname.Text = Format(lname.Text) + ", " + Format(gname.Text) + " " + Format(mname.Text) + "."
stud0!StudName = sname.Text
stud0!StudNo = snumber.Text
stud0!OrdNo = spass.Text
stud0!sched = ssched.Text
stud0!Course = scourse.Text
stud0!Year = syear.Text
stud0.Update
stud0.UpdateBatch adAffectAll
clear
gradecmd.Enabled = True
sdelcmd.Enabled = True
AddNewStatus = False
gshow
dshow
End If
DataGrid1.Enabled = True
End Sub

Private Sub ssched_Click()
CheckEmpty
End Sub
Private Sub scourse_Click()
CheckEmpty
End Sub
Private Sub syear_Click()
CheckEmpty
End Sub
Private Sub csadd_Click()
cscode.Text = ""
csdesc.Text = ""
csunits.Text = ""
cscode.Locked = False
csdesc.Locked = False
csunits.Locked = False
csyear.Locked = True
cssem.Locked = True
csdel.Enabled = False
addcs = True
DataGridcs.Enabled = False
End Sub
Private Sub cscan_Click()
csdatashow
cscode.Locked = True
csdesc.Locked = True
csunits.Locked = True
csadd.Enabled = True
cssave.Enabled = False
csyear.Locked = False
cssem.Locked = False
DataGrid1.Enabled = True
End Sub
Private Sub csdel_Click()
conn.Execute "DELETE FROM " & dbguide.Text & " Where Code = '" & cscode.Text & "' and Description ='" & csdesc.Text & "' and Units ='" & csunits.Text & "'"
csshowgrid
csdatashow
End Sub
Private Sub cscode_Change()
cscheck
End Sub
Private Sub csdesc_Change()
cscheck
End Sub
Private Sub cssched_Click()
ysguide.Text = "CS11"
secguide.Text = "CS11a"
schfrm.Visible = True
schedform.Visible = False
schedcmd.Enabled = True
schcsshowgrid
subdatashow
schdatashow
End Sub
Private Sub csunits_Click()
cscheck
End Sub
Private Sub cssave_Click()
If dupsched(cscode.Text) = True Then
MsgBox "duplicate record"
Else
subject.AddNew
subject!Code = cscode.Text
subject!Description = csdesc.Text
subject!Units = csunits.Text
subject.Update
subject.UpdateBatch adAffectAll
cs11add = False
csdatashow
csshowgrid
csyear.Locked = False
cssem.Locked = False
cssave.Enabled = False
addcs = False
End If
DataGrid1.Enabled = True
End Sub
Private Sub csunits_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub csyear_Click()
csshowgrid
csdatashow
End Sub
Private Sub cssem_Click()
csshowgrid
csdatashow
End Sub
Private Sub csyear_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub cssem_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub DataGrid1_Click()
sname.Text = DataGrid1.Columns(0)
snumber.Text = DataGrid1.Columns(1)
spass.Text = DataGrid1.Columns(2)
ssched.Text = DataGrid1.Columns(3)
scourse.Text = DataGrid1.Columns(4)
syear.Text = DataGrid1.Columns(5)
End Sub
Private Sub DataGridcs_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub DataGridsch_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub DataGridsub_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub Form_Load()
On Error GoTo a
connserver
Exit Sub
a:
CreateDatabase
End Sub
Private Sub CreateDatabase()
conn.Execute "CREATE TABLE CS11(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS12(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS21(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS22(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT11(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT12(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT21(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT22(Code char(10), Description char(200), Units char(1))", , adExecuteNoRecords

conn.Execute "CREATE TABLE CS11a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS11b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS12a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS12b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS21a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS21b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS22a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE CS22b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords

conn.Execute "CREATE TABLE ACT11a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT11b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT12a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT12b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT21a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT21b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT22a(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
conn.Execute "CREATE TABLE ACT22b(Code char(10), Description char(200), Time char(15) ,Days char(10) ,Room char(10) ,Units char(1))", , adExecuteNoRecords
End Sub
Private Sub schclear_Click()
conn.Execute "DELETE FROM " & secguide.Text & " Where Code is not NULL"
If bscscmd.Enabled = False Then
schcsshowgrid
subdatashow
schdatashow
Else
If actcmd.Enabled = False Then
schactshowgrid
subdatashow
schdatashow
End If
End If
End Sub

Private Sub schedcmd_Click()
csshowgrid
csdatashow
schedform.Visible = True
schfrm.Visible = False
studform.Visible = False

schedcmd.Enabled = False
studcmd.Enabled = True
admincmd.Enabled = True
End Sub
Private Sub csdatashow()
On Error GoTo a
cscode.Text = subject!Code
csdesc.Text = subject!Description
csunits.Text = subject!Units
csdel.Enabled = True
Exit Sub
a:
cscode.Text = ""
csdesc.Text = ""
csunits.Text = ""
csdel.Enabled = False
End Sub
Private Sub subdatashow()
On Error GoTo a
subcode.Text = subject!Code
subdesc.Text = subject!Description
subunits.Text = subject!Units
Exit Sub
a:
subcode.Text = ""
subdesc.Text = ""
subunits.Text = ""
End Sub
Private Sub schdatashow()
On Error GoTo a
schcode.Text = sched!Code
schdesc.Text = sched!Description
schunits.Text = sched!Units
schtime.Text = sched!Code
schdays.Text = sched!Description
schroom.Text = sched!Units
schdel.Enabled = True
Exit Sub
a:
schcode.Text = ""
schdesc.Text = ""
schunits.Text = ""
schtime.Text = ""
schdays.Text = ""
schroom.Text = ""
schdel.Enabled = False
End Sub
Private Sub schactshowgrid()
Set subject = New ADODB.Recordset
subject.CursorLocation = adUseClient
Set sched = New ADODB.Recordset
sched.CursorLocation = adUseClient
If schyear.Text = "1st" And schsem.Text = "1st" Then
 ysguide.Text = "ACT11"
 If schsec.Text = "A" Then
 secguide.Text = "ACT11a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "ACT11b"
 End If
 End If
Else
If schyear.Text = "1st" And schsem.Text = "2nd" Then
 ysguide.Text = "ACT12"
 If schsec.Text = "A" Then
 secguide.Text = "ACT12a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "ACT12b"
 End If
 End If
Else
If schyear.Text = "2nd" And schsem.Text = "1st" Then
 ysguide.Text = "ACT21"
 If schsec.Text = "A" Then
 secguide.Text = "ACT21a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "ACT21b"
 End If
 End If
Else
If schyear.Text = "2nd" And schsem.Text = "2nd" And schsec.Text = "A" Then
 ysguide.Text = "ACT22"
 If schsec.Text = "A" Then
 secguide.Text = "ACT22a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "ACT22b"
 End If
 End If
End If
End If
End If
End If
subsql = "select * from " & ysguide.Text & ""
secsql = "select * from " & secguide.Text & ""
subject.Open subsql, conn, adoOpenDynamic, adLockBatchOptimistic
sched.Open secsql, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGridsub.DataSource = subject
DataGridcs.Refresh
Set DataGridsch.DataSource = sched
DataGridsch.Refresh
sortField = DataGridsch.Columns("Code").Caption
    sortString = sortField & " Asc"
sched.Sort = sortString
sortField = DataGridsub.Columns("Code").Caption
    sortString = sortField & " Asc"
subject.Sort = sortString
End Sub
Private Sub schcsshowgrid()
Set subject = New ADODB.Recordset
subject.CursorLocation = adUseClient
Set sched = New ADODB.Recordset
sched.CursorLocation = adUseClient
If schyear.Text = "1st" And schsem.Text = "1st" Then
 ysguide.Text = "CS11"
 If schsec.Text = "A" Then
 secguide.Text = "CS11a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "CS11b"
 End If
 End If
Else
If schyear.Text = "1st" And schsem.Text = "2nd" Then
 ysguide.Text = "CS12"
 If schsec.Text = "A" Then
 secguide.Text = "CS12a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "CS12b"
 End If
 End If
Else
If schyear.Text = "2nd" And schsem.Text = "1st" Then
 ysguide.Text = "CS21"
 If schsec.Text = "A" Then
 secguide.Text = "CS21a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "CS21b"
 End If
 End If
Else
If schyear.Text = "2nd" And schsem.Text = "2nd" And schsec.Text = "A" Then
 ysguide.Text = "CS22"
 If schsec.Text = "A" Then
 secguide.Text = "CS22a"
 Else
 If schsec.Text = "B" Then
 secguide.Text = "CS22b"
 End If
 End If
End If
End If
End If
End If
subsql = "select * from " & ysguide.Text & ""
secsql = "select * from " & secguide.Text & ""
subject.Open subsql, conn, adoOpenDynamic, adLockBatchOptimistic
sched.Open secsql, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGridsub.DataSource = subject
DataGridcs.Refresh
Set DataGridsch.DataSource = sched
DataGridsch.Refresh
sortField = DataGridsch.Columns("Code").Caption
    sortString = sortField & " Asc"
sched.Sort = sortString
sortField = DataGridsub.Columns("Code").Caption
    sortString = sortField & " Asc"
subject.Sort = sortString
End Sub
Private Sub csshowgrid()
Set subject = New ADODB.Recordset
subject.CursorLocation = adUseClient
If bscscmd.Enabled = False Then
 If csyear.Text = "1st" And cssem.Text = "1st" Then
 dbguide.Text = "CS11"
 Else
 If csyear.Text = "1st" And cssem.Text = "2nd" Then
 dbguide.Text = "CS12"
 Else
 If csyear.Text = "2nd" And cssem.Text = "1st" Then
 dbguide.Text = "CS21"
 Else
 If csyear.Text = "2nd" And cssem.Text = "2nd" Then
 dbguide.Text = "CS22"
 End If
 End If
 End If
 End If
Else
If actcmd.Enabled = False Then
 If csyear.Text = "1st" And cssem.Text = "1st" Then
 dbguide.Text = "ACT11"
 Else
 If csyear.Text = "1st" And cssem.Text = "2nd" Then
 dbguide.Text = "ACT12"
 Else
 If csyear.Text = "2nd" And cssem.Text = "1st" Then
 dbguide.Text = "ACT21"
 Else
 If csyear.Text = "2nd" And cssem.Text = "2nd" Then
 dbguide.Text = "ACT22"
 End If
 End If
 End If
 End If
End If
End If
subsql = "select * from " & dbguide.Text & ""
subject.Open subsql, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGridcs.DataSource = subject
DataGridcs.Refresh
sortField = DataGridcs.Columns("Code").Caption
    sortString = sortField & " Asc"
subject.Sort = sortString
End Sub
Private Sub Command4_Click()
On Error GoTo a
connserver
conn.Execute "delete from CS11 where Code<1"
conn.Execute "delete from CS12 where Code<1"
conn.Execute "delete from CS21 where Code<1"
conn.Execute "delete from CS22 where Code<1"
conn.Execute "delete from ACT11 where Code<1"
conn.Execute "delete from ACT12 where Code<1"
conn.Execute "delete from ACT21 where Code<1"
conn.Execute "delete from ACT22 where Code<1"
conn.Execute "delete from stud where OrdNo>0"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/cs11.txt' INTO TABLE CS11"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/cs12.txt' INTO TABLE CS12"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/cs21.txt' INTO TABLE CS21"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/cs22.txt' INTO TABLE CS22"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/act11.txt' INTO TABLE ACT11"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/act12.txt' INTO TABLE ACT12"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/act21.txt' INTO TABLE ACT21"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/act22.txt' INTO TABLE ACT22"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/studrec.txt' INTO TABLE stud"
conn.Execute "LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Susan/Desktop/Orange Application/adminrec.txt' INTO TABLE adminrec"
MsgBox "Default Data Loaded! Please Recall Program!"
a:
conn.Execute "Create table"

End Sub
Private Sub Command3_Click()
connserver
conn.Execute "delete from CS11 where Code<1"
conn.Execute "delete from CS12 where Code<1"
conn.Execute "delete from CS21 where Code<1"
conn.Execute "delete from CS22 where Code<1"
conn.Execute "delete from ACT11 where Code<1"
conn.Execute "delete from ACT12 where Code<1"
conn.Execute "delete from ACT21 where Code<1"
conn.Execute "delete from ACT22 where Code<1"
conn.Execute "delete from stud where OrdNo>0"
conn.Execute "delete from adminrec where Password>0"
MsgBox "Cleared Subject Records! Please Recall Program!"
End
End Sub
Private Function dupsched(chkID As String) As Boolean
Dim dupsch As ADODB.Recordset
Dim sqldupsch As String
    Set dupsch = New ADODB.Recordset
    sqldupsch = "select * from " & dbguide & " where Code = '" & chkID & "'"
    dupsch.CursorLocation = adUseServer
    dupsch.Open sqldupsch, conn
    If dupsch.EOF = True Then
        dupsch.Close
        Set dupsch = Nothing
        Exit Function
    End If
    If chkID = dupsch!Code Then
        dupsched = True
    Else
        dupsched = False
    End If
    dupsch.Close
    Set dupsch = Nothing
End Function
Private Sub Datagridcs_Click()
cscode.Text = DataGridcs.Columns(0)
csdesc.Text = DataGridcs.Columns(1)
csunits.Text = DataGridcs.Columns(2)
End Sub
Private Sub cscheck()
 If addcs = True Then
        If Len(cscode.Text) = 0 Or Len(csdesc.Text) = 0 Or Len(csunits.Text) = 0 Then
            cssave.Enabled = False
        Else
            cssave.Enabled = True
        End If
    End If
End Sub
Private Sub studcmd_Click()
On Error GoTo tableerror
z:
gshow
dshow
sortname
schedform.Visible = False
schfrm.Visible = False
studform.Visible = True

schedcmd.Enabled = True
studcmd.Enabled = False
admincmd.Enabled = True
Exit Sub
tableerror:
conn.Execute "CREATE TABLE stud(StudName char(50), StudNo char(5), OrdNo char(5) ,Sched char(10) ,Course char(5) ,Year char(3))", , adExecuteNoRecords
GoTo z
End Sub
Private Sub gshow()
Set stud0 = New ADODB.Recordset
sql = "select * from stud"
stud0.CursorLocation = adUseClient
stud0.Open sql, conn, adoOpenDynamic, adLockBatchOptimistic
Set DataGrid1.DataSource = stud0
DataGrid1.Refresh
End Sub
Private Sub dshow()
On Error GoTo a
sname.Text = stud0!StudName
snumber.Text = stud0!StudNo
spass.Text = stud0!OrdNo
ssched.Text = stud0!sched
scourse.Text = stud0!Course
syear.Text = stud0!Year
sdelcmd.Enabled = True
gradecmd.Enabled = True
Exit Sub
a:
sname.Text = ""
snumber.Text = ""
spass.Text = ""
ssched.Text = ""
scourse.Text = ""
syear.Text = ""
sdelcmd.Enabled = False
gradecmd.Enabled = False
End Sub
Private Sub Text1_LostFocus()
Text1 = 0
End Sub
Private Sub Text15_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        If ANameSearch(Text15) = False Then
            UNameSearch Text15
        End If
        Text15 = ""
    End If
End Sub
Private Sub txtsearch_LostFocus()
txtsearch = 0
End Sub
Private Sub txtsearch2_LostFocus()
txtsearch2 = 0
End Sub
Private Sub txtsearch_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        If CodeSearch1(txtsearch) = False Then
            DescSearch1 txtsearch
        End If
        txtsearch = ""
    End If
End Sub
Private Function CodeSearch1(xSearch As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    a.CursorLocation = adUseServer
    b = "select * from " & dbguide.Text & " where Code LIKE '" & xSearch & "%'"
    a.Open b, conn, adOpenStatic, adLockReadOnly, adCmdText
    If a.EOF = True Then
    a.Close
    Set a = Nothing
        Exit Function
    End If
    If IsNull(a!Code) Then
        CodeSearch2 = False
    Else
        cscode.Text = a!Code
        csdesc.Text = a!Description
        csunits.Text = a!Units
       CodeSearch2 = True
    End If
        a.Close
    Set a = Nothing
End Function
Private Function DescSearch1(xSearch As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    a.CursorLocation = adUseServer
    b = "select * from " & dbguide.Text & " where Description LIKE '" & xSearch & "%'"
    a.Open b, conn, adOpenStatic, adLockReadOnly, adCmdText
    If a.EOF = True Then
        a.Close
    Set a = Nothing
        Exit Function
    End If
    If IsNull(a!Code) Then
        DescSearch = False
    Else
        cscode.Text = a!Code
        csdesc.Text = a!Description
        csunits.Text = a!Units
     DescSearch = True
    End If
        a.Close
    Set a = Nothing
End Function
Private Function CodeSearch2(xSearch As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    a.CursorLocation = adUseServer
    b = "select * from " & ysguide.Text & " where Code LIKE '" & xSearch & "%'"
    a.Open b, conn, adOpenStatic, adLockReadOnly, adCmdText
    If a.EOF = True Then
    a.Close
    Set a = Nothing
        Exit Function
    End If
    If IsNull(a!Code) Then
        CodeSearch2 = False
    Else
        subcode.Text = a!Code
        subdesc.Text = a!Description
        subunits.Text = a!Units
       CodeSearch2 = True
    End If
        a.Close
    Set a = Nothing
End Function
Private Function DescSearch2(xSearch As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    a.CursorLocation = adUseServer
    b = "select * from " & ysguide.Text & " where Description LIKE '" & xSearch & "%'"
    a.Open b, conn, adOpenStatic, adLockReadOnly, adCmdText
    If a.EOF = True Then
        a.Close
    Set a = Nothing
        Exit Function
    End If
    If IsNull(a!Code) Then
        DescSearch = False
    Else
        subcode.Text = a!Code
        subdesc.Text = a!Description
        subunits.Text = a!Units
     DescSearch = True
    End If
        a.Close
    Set a = Nothing
End Function



Private Sub DataGridsub_Click()
subcode.Text = DataGridsub.Columns(0)
subdesc.Text = DataGridsub.Columns(1)
subunits.Text = DataGridsub.Columns(2)
End Sub
Private Sub DataGridsch_Click()
schcode.Text = DataGridsch.Columns(0)
schdesc.Text = DataGridsch.Columns(1)
schtime.Text = DataGridsch.Columns(2)
schdays.Text = DataGridsch.Columns(3)
schroom.Text = DataGridsch.Columns(4)
schunits.Text = DataGridsch.Columns(5)
End Sub
Private Sub schyear_Click()
If bscscmd.Enabled = False Then
schcsshowgrid
subdatashow
schdatashow
Else
If actcmd.Enabled = False Then
schactshowgrid
subdatashow
schdatashow
End If
End If
End Sub
Private Sub schsem_Click()
If bscscmd.Enabled = False Then
schcsshowgrid
subdatashow
schdatashow
Else
If actcmd.Enabled = False Then
schactshowgrid
subdatashow
schdatashow
End If
End If
End Sub
Private Sub schsec_Click()
If bscscmd.Enabled = False Then
schcsshowgrid
subdatashow
schdatashow
Else
If actcmd.Enabled = False Then
schactshowgrid
subdatashow
schdatashow
End If
End If
End Sub
Private Sub schyear_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub schsem_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub schsec_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub schdel_Click()
conn.Execute "DELETE FROM " & secguide.Text & " Where Code = '" & cscode.Text & "' and Description ='" & csdesc.Text & "' and Units ='" & csunits.Text & "'"
If bscscmd.Enabled = False Then
schcsshowgrid
subdatashow
schdatashow
Else
If actcmd.Enabled = False Then
schactshowgrid
subdatashow
schdatashow
End If
End If
End Sub
Private Sub day1_Click()
secCheckEmpty
day2.Locked = False
End Sub
Private Sub day2_Click()
secCheckEmpty
If day2.Text = day1.Text Then
MsgBox "Invalid Day Setting!"
day2.Text = ""
End If
End Sub
Private Sub rom_Click()
secCheckEmpty
End Sub
Private Sub time1_Click()
secCheckEmpty
time2.Locked = False
End Sub
Private Sub time2_Click()
secCheckEmpty
End Sub
Private Sub time1_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub time2_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub day1_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub day2_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub rom_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub
Private Sub secCheckEmpty()
If Len(time1.Text) = 0 Or Len(time2.Text) = 0 Or Len(rom.Text) = 0 Or (Len(day1.Text) = 0 And Len(day2.Text) = 0) Or Len(rom.Text) = 0 Or Len(subcode.Text) = 0 Then
    schadd.Enabled = False
Else
    schadd.Enabled = True
End If
End Sub
Private Function Check4dup(chkID As String) As Boolean
Dim dupsch As ADODB.Recordset
Dim sqldupsch As String
    Set dupsch = New ADODB.Recordset
    sqldupsch = "select * from " & secguide.Text & " where Code = '" & chkID & "'"
    dupsch.CursorLocation = adUseServer
    dupsch.Open sqldupsch, conn, adOpenStatic, adLockOptimistic, adCmdText
    If dupsch.EOF = True Then
        dupsch.Close
        Set dupsch = Nothing
        Exit Function
    End If
    If chkID = dupsch!Code Then
        Check4dup = True
    Else
        Check4dup = False
    End If
    dupsch.Close
    Set dupsch = Nothing
End Function
Private Sub schadd_Click()
If Check4dup(subcode.Text) = True Then
MsgBox "Duplicate Record!"
Else
sched.AddNew
sched!Code = subcode.Text
sched!Description = subdesc.Text
sched!Units = subunits.Text
sched!Time = time1.Text + "-" + time2.Text
sched!days = Format(day1.Text) + Format(day2.Text)
sched!room = rom.Text
sched.Update
sched.UpdateBatch adAffectAll
If bscscmd.Enabled = False Then
schcsshowgrid
subdatashow
schdatashow
Else
If actcmd.Enabled = False Then
schactshowgrid
subdatashow
schdatashow
End If
End If
End If
time1.Text = ""
time2.Text = ""
time2.Locked = True
day1.Text = ""
day2.Text = ""
day2.Locked = True
rom.Text = ""
End Sub
Private Sub schcan_Click()
time1.Text = ""
time2.Text = ""
time2.Locked = True
day1.Text = ""
day2.Text = ""
day2.Locked = True
rom.Text = ""
schadd.Enabled = False
End Sub
Private Sub txtsearch2_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        If CodeSearch2(txtsearch2) = False Then
            DescSearch2 txtsearch2
        End If
        txtsearch2 = ""
    End If
End Sub
Private Sub Text3_Change()
cempty
End Sub
Private Sub Text4_Change()
cempty
End Sub
Private Sub alname_Change()
cempty
End Sub
Private Sub afname_Change()
cempty
End Sub
Private Sub amname_Click()
cempty
End Sub
Private Sub pos_Click()
cempty
End Sub
Private Sub cempty()
If Adding = True Then
        If Len(amname.Text) = 0 Or Len(alname.Text) = 0 Or Len(afname.Text) = 0 Or Len(Text3.Text) = 0 Or Len(Text4.Text) = 0 Or Len(pos.Text) = 0 Then
            cmdadmsave.Enabled = False
        Else
            cmdadmsave.Enabled = True
        End If
End If
End Sub
Private Function ANameSearch(xSearch As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    a.CursorLocation = adUseServer
    b = "select * from adminrec where AdminName LIKE '" & xSearch & "%'"
    a.Open b, conn, adOpenStatic, adLockReadOnly, adCmdText
    If a.EOF = True Then
    a.Close
    Set a = Nothing
        Exit Function
    End If
    If IsNull(a!AdminName) Then
        ANameSearch = False
    Else
       Text2.Text = a!AdminName
       Text3.Text = a!UserName
       Text4.Text = a!Password
       Text5.Text = a!Position
       ANameSearch = True
    End If
        a.Close
    Set a = Nothing
End Function
Private Function UNameSearch(xSearch As String) As Boolean
    Dim a As ADODB.Recordset
    Dim b As String
    Set a = New ADODB.Recordset
    a.CursorLocation = adUseServer
    b = "select * from adminrec where UserName LIKE '" & xSearch & "%'"
    a.Open b, conn, adOpenStatic, adLockReadOnly, adCmdText
    If a.EOF = True Then
    a.Close
    Set a = Nothing
        Exit Function
    End If
    If IsNull(a!AdminName) Then
        UNameSearch = False
    Else
       Text2.Text = a!AdminName
       Text3.Text = a!UserName
       Text4.Text = a!Password
       Text5.Text = a!Position
       UNameSearch = True
    End If
        a.Close
    Set a = Nothing
End Function