VBA Code

[tangoman66]

Ive had a problem solved by namliam but I need a really really good explanation of the VBA behind the process, can somebody help me??

This is the coding...

Private Sub btnGo_Click()
    Dim db As DAO.Database
    Dim rsCL1 As DAO.Recordset, rsCL2 As DAO.Recordset, rsCL3 As DAO.Recordset
    Dim rsHappy As DAO.Recordset
    Dim myPeriod As Integer, myActionCounter As Integer
    Dim myNotIn As String
    Dim myClass1 As Integer, myClass2 As Integer, myClass3 As Integer, myHappy As Integer
    Dim mySQL As String
    myNotIn = "0"
    myPeriod = 1
    Set db = CurrentDb
    DoCmd.Hourglass True
    Do While myPeriod <= 4
        myHappy = 0
        mySQL = "SELECT * " & _
                "FROM tblSubjectMailman " & _
                "WHERE SubjectIDNumber Not In (" & myNotIn & ")"
        Set rsCL1 = db.OpenRecordset(mySQL)

        Do While Not rsCL1.EOF
            mySQL = "SELECT * " & _
                    "FROM tblSubjectMailman " & _
                    "WHERE SubjectIDNumber Not In (" & myNotIn & _
                                                   "," & rsCL1!SubjectIDNumber & ")"
            Set rsCL2 = db.OpenRecordset(mySQL)
            Do While Not rsCL2.EOF
                mySQL = "SELECT * " & _
                        "FROM tblSubjectMailman " & _
                        "WHERE SubjectIDNumber Not In (" & myNotIn & _
                                                       "," & rsCL1!SubjectIDNumber & _
                                                       "," & rsCL2!SubjectIDNumber & ")"
                Set rsCL3 = db.OpenRecordset(mySQL)
                Do While Not rsCL3.EOF
                    mySQL = "Select Count(*) as Happy " & _
                            "From tblChoiceMailman " & _
                            "WHERE " & BuildWhere(rsCL1!SubjectIDNumber, _
                                                  rsCL2!SubjectIDNumber, _
                                                  rsCL3!SubjectIDNumber)
                    Set rsHappy = db.OpenRecordset(mySQL)
                    If myHappy < rsHappy!Happy _
                        And (rsCL1!Technical _
                             Xor rsCL2!Technical _
                             Xor rsCL3!Technical) Then
                        myClass1 = rsCL1!SubjectIDNumber
                        myClass2 = rsCL2!SubjectIDNumber
                        myClass3 = rsCL3!SubjectIDNumber
                        myHappy = rsHappy!Happy
                    End If
                    rsHappy.Close
                    rsCL3.MoveNext
                Loop
                rsCL3.Close
                rsCL2.MoveNext
            Loop
            rsCL2.Close
            rsCL1.MoveNext
        Loop
        rsCL1.Close
        myNotIn = myNotIn & "," & myClass1 & "," & myClass2 & "," & myClass3
        Me.Controls("P" & myPeriod & "C1") = myClass1
        mySQL = "Select Count(*) as Happy " & _
                "From tblChoiceMailman " & _
                "WHERE Choice1 = " & myClass1
        Set rsHappy = db.OpenRecordset(mySQL)
        Me.Controls("P" & myPeriod & "C1Count") = rsHappy!Happy
        rsHappy.Close
        Me.Controls("P" & myPeriod & "C2") = myClass2
        mySQL = "Select Count(*) as Happy " & _
                "From tblChoiceMailman " & _
                "WHERE Choice2 = " & myClass2 & _
                "  AND Choice1 <> " & myClass1
        Set rsHappy = db.OpenRecordset(mySQL)
        Me.Controls("P" & myPeriod & "C2Count") = rsHappy!Happy
        rsHappy.Close
        Me.Controls("P" & myPeriod & "C3") = myClass3
        mySQL = "Select Count(*) as Happy " & _
                "From tblChoiceMailman " & _
                "WHERE Choice3 = " & myClass3 & _
                "  AND Choice1 <> " & myClass1 & _
                "  AND Choice2 <> " & myClass2
        Set rsHappy = db.OpenRecordset(mySQL)
        Me.Controls("P" & myPeriod & "C3Count") = rsHappy!Happy
        rsHappy.Close
        
        Me.Repaint
        myHappy = 0
        myPeriod = myPeriod + 1
    Loop
    DoCmd.Hourglass False
End Sub
Function BuildWhere(S1 As Integer, S2 As Integer, S3 As Integer) As String
    BuildWhere = "   (Choice1 = " & S1 & " AND Choice2 not in (" & S2 & "," & S3 & ")" & _
                                         " AND Choice3 not in (" & S2 & "," & S3 & ") )" & _
                 "OR (Choice2 = " & S1 & " AND Choice1 not in (" & S2 & "," & S3 & ")" & _
                                         " AND Choice3 not in (" & S2 & "," & S3 & ") )" & _
                 "OR (Choice3 = " & S1 & " AND Choice1 not in (" & S2 & "," & S3 & ")" & _
                                         " AND Choice2 not in (" & S2 & "," & S3 & ") )"
End Function

If anybody could explain it to me so that I might be able to adapt it it would be greatly appreciated. Also if there is an easier way to perform the operation if would be helpful

 

[retro]

just a suggestion... you might find someone more expert on this frequenting the VBA coding section of the forum

good luck! i took a look and got a bit lost! hehe

 

[tangoman66]

Anybody?????