Change this code from List View to Flex Grid

[Encoder]

How do I initialize, load data to, and save data from a flex grid? I've never tried using that control, but I'm making a Student Grades System where I need to make the Grades Entry a Excel/worksheet-type of interface. I originally used listview to display the grades, then use a separate form for Grades Entry.

Public Sub setLVprof()

With lvStudList

    .ColumnHeaders.Clear

    .ColumnHeaders.Add , , "Student No.", .Width * 0.2
    .ColumnHeaders.Add , , "Name", .Width * 0.2
    .ColumnHeaders.Add , , "1st Gr.", .Width * 0.1
    .ColumnHeaders.Add , , "2nd Gr.", .Width * 0.1
    .ColumnHeaders.Add , , "3rd Gr.", .Width * 0.1
    .ColumnHeaders.Add , , "4th Gr.", .Width * 0.1
    .ColumnHeaders.Add , , "Average", .Width * 0.1
    .ColumnHeaders.Add , , "Remarks", .Width * 0.089

End With

End Sub

Public Sub loadStudList()

Dim lvInd As Integer

strSQL = "SELECT * FROM tblStudentList WHERE yr_section = '" & lblYr_Section & "'" _
        & " AND school_yr = '" & lblSchool_Yr & "'" _
        & " ORDER BY lname ASC, fname ASC, mname ASC"

With adoRst

    lvStudList.ListItems.Clear

    .Open strSQL, adoCon, adOpenStatic, adLockReadOnly

    Do Until .EOF

        If !bsubj = True Then

            If !subj = cmbSubj.Text Then

                GoTo addToList

            Else

                GoTo mNxt

            End If

        End If

addToList:

    lvInd = lvInd + 1

    lvStudList.ListItems.Add , , !stud_no & ""

    lvStudList.ListItems(lvInd).SubItems(1) = UCase(!lname & ", " _
                                            & !fname & " " _
                                            & VBA.Left(!mname, 1) & ".")

mNxt:

    .MoveNext

    Loop

    .Close

End With

For i = 1 To lvStudList.ListItems.Count

If cmbSubj.Enabled = False Then

    strSQL = "SELECT * FROM tblStudGrades WHERE stud_no = '" & lvStudList.ListItems(i).Text & "'" _
            & " AND school_yr = '" & lblSchool_Yr & "'" _
            & " AND yr_section = '" & lblYr_Section & "'" _
            & " AND subj = '" & cmbSubj & "'"

    With adoRst

        .Open strSQL, adoCon, adOpenKeyset, adLockPessimistic

        If Not .EOF Then

        lvStudList.ListItems(i).SubItems(2) = Format(.Fields("1st"), "Standard")
        lvStudList.ListItems(i).SubItems(3) = Format(.Fields("2nd"), "Standard")
        lvStudList.ListItems(i).SubItems(4) = Format(.Fields("3rd"), "Standard")
        lvStudList.ListItems(i).SubItems(5) = Format(.Fields("4th"), "Standard")

        Else

        lvStudList.ListItems(i).SubItems(2) = "0.00"
        lvStudList.ListItems(i).SubItems(3) = "0.00"
        lvStudList.ListItems(i).SubItems(4) = "0.00"
        lvStudList.ListItems(i).SubItems(5) = "0.00"

        End If

        .Close

    End With

'Else

    strSQL = "SELECT * FROM tblGradesAve WHERE stud_no = '" & lvStudList.ListItems(i).Text & "'" _
            & " AND school_yr = '" & lblSchool_Yr & "'" _
            & " AND yr_section = '" & lblYr_Section & "'" _
            & " AND subj = '" & cmbSubj & "'"

    With adoRst

        .Open strSQL, adoCon, adOpenKeyset, adLockPessimistic

        If Not .EOF Then

        lvStudList.ListItems(i).SubItems(6) = !ave & ""

        lvStudList.ListItems(i).SubItems(7) = !remarks & ""

        Else

        lvStudList.ListItems(i).SubItems(6) = "0.00"

        End If

        .Close

    End With

Else

Dim subCt As Integer

    subCt = 3

    Do Until subCt > lvStudList.ColumnHeaders.Count

    strSQL = "SELECT * FROM tblStudgrades WHERE stud_no = '" & lvStudList.ListItems(i).Text & "'" _
            & " AND school_yr = '" & lblSchool_Yr & "'" _
            & " AND yr_section = '" & lblYr_Section & "'" _
            & " AND subj = '" & lvStudList.ColumnHeaders(subCt).Text & "'"

    With adoRst

        .Open strSQL, adoCon, adOpenKeyset, adLockPessimistic

        If Not .EOF Then

            Select Case cmbGrPer.ListIndex

                Case 0

                    lvStudList.ListItems(i).SubItems(subCt - 1) = Format(.Fields("1st"), "Standard")

                Case 1

                    lvStudList.ListItems(i).SubItems(subCt - 1) = Format(.Fields("2nd"), "Standard")

                Case 2

                    lvStudList.ListItems(i).SubItems(subCt - 1) = Format(.Fields("3rd"), "Standard")

                Case 3

                    lvStudList.ListItems(i).SubItems(subCt - 1) = Format(.Fields("4th"), "Standard")

            End Select

        Else

        lvStudList.ListItems(i).SubItems(subCt - 1) = Format("0", "Standard")

        End If

        .Close

    End With

    subCt = subCt + 1

    Loop


End If

Next i

End Sub

Private Sub cmdSave_Click()

Dim total, ave As Double

For i = 1 To lvStudList.ListItems.Count

    strSQL = "SELECT * FROM tblStudGrades WHERE stud_no = '" & lvStudList.ListItems(i).Text & "'" _
            & " AND school_yr = '" & lblSchool_Yr & "'" _
            & " AND yr_section = '" & lblYr_Section & "'" _
            & " AND subj = '" & cmbSubj & "'"

    With adoRst

        .Open strSQL, adoCon, adOpenKeyset, adLockPessimistic

        If .EOF Then
            .AddNew
        End If

        !stud_no = lvStudList.ListItems(i).Text
        !school_yr = lblSchool_Yr
        !yr_section = lblYr_Section
        !yr = lblYr_Section.Tag
        !subj = cmbSubj


        .Fields("1st") = lvStudList.ListItems(i).SubItems(2)
        .Fields("2nd") = lvStudList.ListItems(i).SubItems(3)
        .Fields("3rd") = lvStudList.ListItems(i).SubItems(4)
        .Fields("4th") = lvStudList.ListItems(i).SubItems(5)

        .Update

        .Close

    End With

    strSQL = "SELECT * FROM tblGradesAve WHERE stud_no = '" & lvStudList.ListItems(i).Text & "'" _
            & " AND yr_section = '" & lblYr_Section & "'" _
            & " AND subj = '" & cmbSubj & "'"

    With adoRst

        .Open strSQL, adoCon, adOpenKeyset, adLockPessimistic

        If .EOF Then
            .AddNew
        End If

        !stud_no = lvStudList.ListItems(i).Text
        !school_yr = lblSchool_Yr
        !yr_section = lblYr_Section
        !yr = lblYr_Section.Tag
        !subj = cmbSubj

        Dim ctr As Integer

        ctr = 2

        total = 0

        Do Until ctr > 5
            total = total + Val(lvStudList.ListItems(i).SubItems(ctr))
            ctr = ctr + 1
        Loop

        ave = total / 4

        !ave = ave

        If ave < 75 Then
        !remarks = "FAILED"
        Else
        !remarks = "PASSED"
        End If

        .Update

        .Close


        strSQL = "SELECT a1.*, a2.unit FROM tblGradesAve a1, tblSubjects a2 WHERE stud_no = '" & lvStudList.ListItems(i).Text & "'" _
        & " AND a1.yr_section = '" & lblYr_Section & "'" _
        & " AND a1.subj = '" & cmbSubj & "'" _
        & " AND a1.subj = a2.subj"

        .Open strSQL, adoCon, adOpenKeyset, adLockPessimistic

        'Dim fldcount As Integer

        'Do Until fldcount = .Fields.Count

        '    MsgBox .Fields(fldcount).name

        '    fldcount = fldcount + 1

        'Loop

        Do Until .EOF

        If !ave < 75 Then

            .Fields("a1.unit") = .Fields("a2.unit")

        Else

            .Fields("a1.unit") = 0

        End If

        .Update

        .MoveNext

        Loop

        .Close

    End With

Next i

MsgBox "Record Successfully Saved!", vbOKOnly + vbInformation, "System Information"

Unload Me

End Sub

You could notice that I use multiple tables when I should have used one (for example table for Grades and Average is different when I could just use one), but that's because some tables are just added to the original design/project.

[akhileshbc]

Check this one: How to use FlexGrid ? ...