Разработка базы данных для ведения учета успеваемости студентов в электронном виде

    Unload Me

Else

    MsgBox "Выберите  преподавателя и группу", vbInformation, App.Title

End If

End If

End Sub

Private Sub flxFreeGr_Click()

    Dim rsGr As New Recordset

    Dim sqlGr As String

    Dim NumIdCity As Integer

    If flxFreeGr.Rows > 1 Then

        If flxFreeGr.Col = 1 Then NumIdCity = 1 Else NumIdCity = 2

        rsGr.CursorLocation = adUseClient

        sqlGr = "select IdGroup from GroupS where NGroup=" & flxFreeGr.TextMatrix(flxFreeGr.Row, 0) & " and IdCity=" & NumIdCity

        rsGr.Open sqlGr, BDSt.cnnBDSt, adOpenDynamic, adLockOptimistic

        If rsGr.RecordCount > 0 Then IdGrForTeacher = rsGr!IdGroup

        rsGr.Close

    End If

End Sub

Private Sub Form_Load()

    flxFreeGr.TextMatrix(0, 0) = "Группа"

    flxFreeGr.TextMatrix(0, 1) = "Бокс."

    flxFreeGr.TextMatrix(0, 2) = "Тихв."

    Dim rsGr As New Recordset

    Dim sqlGr As String

    rsGr.CursorLocation = adUseClient

    sqlGr = "Transform  iif(sum(IdGroup>0),'Свободна','Нет') " & _

    "select  NGroup from GroupS where IdGroup in ( " & _

    "select IdGroup from Student where IdFak=" & BDSt.rsPlan!IdFak & "And" &_

    "IdKurs=" & BDSt.rsPlan!IdKurs & ") and " & _

    "IdGroup not in (SELECT Nagruz.IdGroup FROM InPlan RIGHT JOIN " & _

    "(OnHour RIGHT JOIN Nagruz ON OnHour.IdRazb = Nagruz.IdRazb) " & _

    "ON InPlan.KodInPlan = OnHour.IdInPlan " & _

    "WHERE InPlan.IdPlan=" & BDSt.rsPlan!IdPlan & " AND " & _

    "InPlan.IdDis=" & frmNagr.flxNagr.TextMatrix(frmNagr.flxNagr.Row, 2) & " AND " & _

    "OnHour.Semestr=" & frmNagr.opt1.Value & " AND " & _

    "OnHour.IdTypLes=" & IdTypeLesForNagr & ") " & _

    "group by NGroup " & _

    "Pivot IdCity"

    rsGr.Open sqlGr, BDSt.cnnBDSt, adOpenDynamic, adLockOptimistic

    For f = 1 To rsGr.RecordCount

        flxFreeGr.Rows = flxFreeGr.Rows + 1

        flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 1) = "Нет"

        flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 2) = "Нет"

        flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 0) = rsGr!NGroup

        Select Case rsGr.Fields.Count

        Case 3

            flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 1) = rsGr.Fields(1)

            flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 2) = rsGr.Fields(2)

        Case 2

            If rsGr.Fields(1).Name = "1" Then flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 1) = rsGr.Fields(1) Else flxFreeGr.TextMatrix(flxFreeGr.Rows - 1, 2) = rsGr.Fields(1)

        End Select

        If rsGr.EOF <> True Then rsGr.MoveNext

    Next f

    rsGr.Close

End Sub

Форма для  выбора ведомости

     На  форме представлен список факультетов  и курсов. После выбора факультета и курса в таблице будет  отображен список доступных дисциплин. После выбора дисциплины можно открыть ведомость.

Рисунок 8. Форма “Ведомости”

Код формы:

Private Sub LoadDis(ByVal Kurs As Long, ByVal Fakult As Long)

    Dim rsDisForRes As New Recordset

    Dim sqlDisForRes As String

    flxDis.Rows = 1

sqlDisForRes = "SELECT Dis.IdDis, Dis.NameDis, Student.IdFak," & _

               " Student.IdKurs, TypLesson.MiniNameTypLes, " & _

               "TypLesson.Res, OnHour.IdRazb, IIf(OnHour.Semestr=" & _

               "True,Kurs.Kurs*2-1,Kurs.Kurs*2) as Smr FROM ((Kurs RIGHT" & _

               " JOIN Plan ON Kurs.IdKurs = Plan.IdKurs) RIGHT JOIN" & _

               " (Dis RIGHT JOIN (TypLesson RIGHT JOIN (InPlan RIGHT" & _

               " JOIN OnHour ON InPlan.KodInPlan = OnHour.IdInPlan)" & _

               " ON TypLesson.IdTypLes = OnHour.IdTypLes) ON Dis.IdDis =" & _

               " InPlan.IdDis) ON Plan.IdPlan = InPlan.IdPlan) RIGHT" & _

               " JOIN (Student RIGHT JOIN Result ON Student.IdStud =" & _

               " Result.IdStud) ON OnHour.IdRazb = Result.IdRazb GROUP" & _

               " BY Dis.IdDis, Dis.NameDis, Student.IdFak, Student." & _

               "IdKurs, TypLesson.MiniNameTypLes, TypLesson.Res, " & _

               "OnHour.IdRazb, OnHour.Semestr, IIf(OnHour.Semestr=" & _

               "True,Kurs.Kurs*2-1,Kurs.Kurs*2) HAVING Student.IdFak=" & _

               Fakult & " AND Student.IdKurs=" & Kurs & " AND TypLesson.Res=True" & _

               " order by OnHour.Semestr desc"

    rsDisForRes.CursorLocation = adUseClient

    rsDisForRes.Open sqlDisForRes, BDSt.cnnBDSt, adOpenDynamic, adLockOptimistic

    For i = 1 To rsDisForRes.RecordCount

        flxDis.Rows = flxDis.Rows + 1

        flxDis.TextMatrix(flxDis.Rows - 1, 0) = rsDisForRes!IdDis

        flxDis.TextMatrix(flxDis.Rows - 1, 1) = rsDisForRes!IdRazb

        flxDis.TextMatrix(flxDis.Rows - 1, 2) = rsDisForRes!NameDis

        flxDis.TextMatrix(flxDis.Rows - 1, 3) = rsDisForRes!MiniNameTypLes

        flxDis.TextMatrix(flxDis.Rows - 1, 4) = rsDisForRes!Smr

        If Not rsDisForRes.EOF Then rsDisForRes.MoveNext

    Next i

    rsDisForRes.Close

End Sub 
 

Private Sub dcbFakult_Change()

If (Len(dcbFakult.BoundText) > 0) And (Len(dcbKurs.BoundText) > 0) Then LoadDis dcbKurs.BoundText, dcbFakult.BoundText

End Sub 

Private Sub dcbKurs_Change()

If (Len(dcbFakult.BoundText) > 0) And (Len(dcbKurs.BoundText) > 0) Then LoadDis dcbKurs.BoundText, dcbFakult.BoundText

End Sub 

Private Sub flxDis_DblClick()

    frmMag.Show vbModal

End Sub 

Private Sub Form_Load()

flxDis.Cols = 5

flxDis.ColWidth(0) = 0

flxDis.ColWidth(1) = 0

flxDis.ColWidth(2) = 5000

flxDis.ColWidth(3) = 500

flxDis.TextMatrix(0, 2) = "Дисциплина"

flxDis.TextMatrix(0, 3) = "Тип" 

End Sub 

Private Sub Form_Unload(Cancel As Integer)

frmMAin.Show

End Sub

Рисунок 9. Форма “Ведомость”

Код формы:

Dim rsBall As New Recordset 

Private Sub flxMag_DblClick()

Dim flxRow As Integer

Dim flxCol As Integer

flxRow = flxMag.Row

flxCol = flxMag.Col 

Select Case flxCol

Case 3

    rsBall.MoveFirst

    If flxMag.TextMatrix(flxRow, flxCol) = "" Then

        flxMag.TextMatrix(flxRow, flxCol) = rsBall!MiniBall

        flxMag.TextMatrix(flxRow, 2) = rsBall!IdBall

        Exit Sub

    End If

    rsBall.Find "IdBall=" & flxMag.TextMatrix(flxRow, 2)

    If Not rsBall.EOF Then rsBall.MoveNext

    If rsBall.EOF Then rsBall.MoveFirst

    flxMag.TextMatrix(flxRow, flxCol) = rsBall!MiniBall

    flxMag.TextMatrix(flxRow, 2) = rsBall!IdBall

End Select

End Sub 

Private Sub flxMag_KeyPress(KeyAscii As Integer)

Select Case KeyAscii

Case 8

    If flxMag.Col > 0 Then flxMag.Text = ""

End Select

If flxMag.Col = 3 Then

    Select Case KeyAscii

    Case 53

        rsBall.MoveFirst

        rsBall.Find "Ball=5"

        flxMag.TextMatrix(flxMag.Row, 3) = rsBall!MiniBall

        flxMag.TextMatrix(flxMag.Row, 2) = rsBall!IdBall

    Case 52

        rsBall.MoveFirst

        rsBall.Find "Ball=4"

        flxMag.TextMatrix(flxMag.Row, 3) = rsBall!MiniBall

        flxMag.TextMatrix(flxMag.Row, 2) = rsBall!IdBall

    Case 51

        rsBall.MoveFirst

        rsBall.Find "Ball=3"

        flxMag.TextMatrix(flxMag.Row, 3) = rsBall!MiniBall

        flxMag.TextMatrix(flxMag.Row, 2) = rsBall!IdBall

    Case 112, 80, 231, 199

        rsBall.MoveFirst

        rsBall.Find "Ball='Зачтено'"

        flxMag.TextMatrix(flxMag.Row, 3) = rsBall!MiniBall

        flxMag.TextMatrix(flxMag.Row, 2) = rsBall!IdBall

    Case 237, 205, 121, 89

        rsBall.MoveFirst

        rsBall.Find "Ball='Не явился'"

        flxMag.TextMatrix(flxMag.Row, 3) = rsBall!MiniBall

        flxMag.TextMatrix(flxMag.Row, 2) = rsBall!IdBall

    End Select

End If

End Sub 

Private Sub flxMag_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)

    Dim flxRow As Integer

    Dim flxCol As Integer

    flxRow = flxMag.Row

    flxCol = flxMag.Col

    If flxCol = 1 Then

        If flxMag.TextMatrix(flxRow, flxCol) = "" Then

            flxMag.TextMatrix(flxRow, flxCol) = CStr(Date)

            Exit Sub

        End If

        If Button = 1 Then flxMag.TextMatrix(flxRow, flxCol) = CStr(CDate(flxMag.TextMatrix(flxRow, flxCol)) + 1)

        If Button = 2 Then flxMag.TextMatrix(flxRow, flxCol) = CStr(CDate(flxMag.TextMatrix(flxRow, flxCol)) - 1)

    End If

End Sub 

Private Sub Form_Load()

flxMag.Rows = 1

flxMag.ColWidth(0) = 3000

flxMag.ColWidth(1) = 1000

flxMag.ColWidth(2) = 0

flxMag.ColWidth(3) = 600

flxMag.ColWidth(4) = 5000

flxMag.ColWidth(5) = 0 

flxMag.TextMatrix(0, 0) = "ФИО"

flxMag.TextMatrix(0, 1) = "Дата"

flxMag.TextMatrix(0, 3) = "Оценка"

flxMag.TextMatrix(0, 4) = "Тема" 

Dim rsVed As New Recordset

Dim sqlVed As String

sqlVed = "SELECT Student.FIOStud, Result.DateRec, Ball.MiniBall, Result.IdBall" & _

", Result.Themes, Result.IdRec FROM Student RIGHT JOIN (Ball RIGHT JOIN Result ON" & _

" Ball.IdBall = Result.IdBall) ON Student.IdStud = Result.IdStud" & _

" WHERE Result.IdRazb=" & frmResult.flxDis.TextMatrix(frmResult.flxDis.Row, 1) & " Order By Student.FIOStud"

rsVed.CursorLocation = adUseClient

rsVed.Open sqlVed, BDSt.cnnBDSt, adOpenDynamic, adLockOptimistic

For i = 1 To rsVed.RecordCount

    flxMag.Rows = flxMag.Rows + 1

    If Not IsNull(rsVed!FIOStud) Then flxMag.TextMatrix(i, 0) = rsVed!FIOStud

    If Not IsNull(rsVed!DateRec) Then flxMag.TextMatrix(i, 1) = rsVed!DateRec

    If Not IsNull(rsVed!IdBall) Then flxMag.TextMatrix(i, 2) = rsVed!IdBall

    If Not IsNull(rsVed!MiniBall) Then flxMag.TextMatrix(i, 3) = rsVed!MiniBall

    If Not IsNull(rsVed!Themes) Then flxMag.TextMatrix(i, 4) = rsVed!Themes

    flxMag.TextMatrix(i, 5) = rsVed!IdRec

    If Not rsVed.EOF Then rsVed.MoveNext

Next i

rsVed.Close 

rsBall.CursorLocation = adUseClient

rsBall.Open "Select * from Ball", BDSt.cnnBDSt, adOpenDynamic, adLockOptimistic

End Sub 

Private Sub Form_Resize()

If frmMag.Height < 3000 Then frmMag.Height = 3000

flxMag.Height = frmMag.Height - 1600

flxMag.Width = frmMag.Width - 100

End Sub 

Private Sub Form_Unload(Cancel As Integer)

rsBall.Close

End Sub 

Private Sub tlbMag_ButtonClick(ByVal Button As MSComctlLib.Button)

Select Case Button.Index

Case 4 'Выход

    Unload Me

Case 2 'Сохранить