method or data member not found a form. error not able to understand why this is pooping up.

[JainAbraham]

Hi all,

I was running a macro already created. And there were an error that i faced i tried alot of options but i am not able to know where the problem is.

I have these forms that connect to MS Access as it database. its a huge macro when i run this macro the following error pops up.

method or data member not found

the below code is written in a form.
the part where the code fails is pasted below.

Private Sub Project_Status_report()


    Dim strReadQ        As String
    Dim sSQL            As String
    Dim rsReadEmaminee  As ADODB.Recordset
    Dim sSQLEmp         As String
    Dim rsEmpResults    As ADODB.Recordset
    Dim iCounter        As Integer
    Dim sTempUserId     As String
    Dim sTempUserName   As String
    Dim bPending        As Boolean
    Dim iAttempt        As Integer
    Dim bFail           As Boolean
    Dim iPercentage     As Integer
    Dim iFinalUserCount As Integer
    Dim iPendUserCount  As Integer
    
    CmdAnswerSheet.Enabled = False
    Label3.Visible = True
    LblStatus.Visible = True


    'RESET all Search controls
    cboLevel.Text = ""
    cboLevel.BackColor = vbWhite
    cboModule.Text = ""
    cboModule.BackColor = vbWhite
    CboName.Text = ""
    CboName.BackColor = vbWhite
    CboTestType.Text = ""
    CboTestType.BackColor = vbWhite


    If ConnectDB.State = adStateClosed Then
        ConnectDB.Open
    End If
    
    sSQLEmp = "SELECT * FROM Emp_Master WHERE EMP_Status = 1 ORDER BY Emp_First_Name"
    sSQL = "Select * from Emp_Test_Result ORDER BY Test_Date DESC"
    


       
    Set rsReadEmaminee = New ADODB.Recordset
    rsReadEmaminee.Open sSQLEmp, ConnectDB 'Create the recordset.
    Set rsReadEmaminee.ActiveConnection = Nothing


    Set rsEmpResults = New ADODB.Recordset
    rsEmpResults.Open sSQL, ConnectDB 'Create the recordset.
    Set rsEmpResults.ActiveConnection = Nothing
    
    ConnectDB.Close
    


[COLOR=#ff0000][B]    ReportSheet.ActiveSheet.Cells(1, 1).Select[/B][/COLOR]
    
    If rsReadEmaminee.State = 1 And rsReadEmaminee.RecordCount > 0 Then
        iFinalUserCount = rsReadEmaminee.RecordCount
        Call SetReportHeader("P")
        Let iCounter = 2
   
        While Not rsReadEmaminee.EOF
            sTempUserId = rsReadEmaminee.Fields("Emp_User_ID").Value
            rsEmpResults.Filter = adFilterNone
            rsEmpResults.Filter = "Test_Employee_Id = '" & sTempUserId & "'"
            sTempUserName = GetUserName(sTempUserId)
            
            ' Filling excel sheet with records...
            'User Name
            ReportSheet.Cells(iCounter, 2) = UCase(sTempUserName)
            'User Id
            ReportSheet.Cells(iCounter, 3) = LCase(sTempUserId)
            bPending = False
            bFail = False
            If rsEmpResults.RecordCount > 0 Then
                'Test Type
                rsTestType.Filter = adFilterNone
                rsTestType.Filter = " Test_Type = '" & rsEmpResults.Fields("Test_Type").Value & "'"
                
                
                
                If rsTestType.RecordCount > 0 Then
                    ReportSheet.Cells(iCounter, 4) = rsTestType.Fields("Test_Full_Form").Value
                Else
                    ReportSheet.Cells(iCounter, 4) = "-"
                End If
                'Result
                ' iPercentage = Val(rsEmpResults.Fields("Test_Marks").Value)
                If iPercentage >= 60 Then
                    ReportSheet.Cells(iCounter, 5) = rsEmpResults.Fields("Test_Result").Value
             ElseIf iPercentage < 60 Then
                    ReportSheet.Cells(iCounter, 5) = rsEmpResults.Fields("Test_Result").Value
                End If
                ReportSheet.Cells(iCounter, 1) = "Completed"
                'Comments
                ReportSheet.Cells(iCounter, 6) = rsEmpResults.Fields("Test_Comments").Value & ""
                
                'Level
                If (rsEmpResults.Fields("Test_Experience_level").Value = 1) Then
            
                    reportlevel = "Basic"
                    explevel = 1
                ElseIf (rsEmpResults.Fields("Test_Experience_level").Value = 2) Then
                    reportlevel = "Intermediate"
                    explevel = 2
                ElseIf (rsEmpResults.Fields("Test_Experience_level").Value = 3) Then
                    reportlevel = "Expert"
                    explevel = 3
                End If
            
            
                frmAnswerSheet.lblAttempted.Caption = "(Q's Attempted = " & rsEmpResults.Fields("Questions_Attempted").Value & ")"
            
            
                ReportSheet.Cells(iCounter, 7) = reportlevel
                
                'Attempt
                iAttempt = rsEmpResults.Fields("Test_Attempt_Number").Value
                ReportSheet.Cells(iCounter, 8) = rsEmpResults.Fields("Test_Attempt_Number").Value
                'Date
                
                ReportSheet.Cells(iCounter, 9) = rsEmpResults.Fields("Test_Date").Value
                'Marks
                ReportSheet.Cells(iCounter, 10) = "'" & rsEmpResults.Fields("Test_Correct_Answers_Count").Value & "/" & rsEmpResults.Fields("Test_Question_Count").Value
                'Obtd %
                iPercentage = Val(rsEmpResults.Fields("Test_Marks").Value)
                If iPercentage < 60 Then
                    bFail = True
                    bPending = True
                End If
                ReportSheet.Cells(iCounter, 11) = "'" & iPercentage & " %"
                
               ' ReportSheet.Cells(iCounter, 12) = rsEmpResults.Fields("moduleid").Value
                
                'Setting Status
                If bFail = True Then
               
                    If iAttempt = 1 And DateDiff("d", rsEmpResults.Fields("Test_Date").Value, Date) > 15 Then
                        bPending = True
                    End If
                    If iAttempt = 2 And DateDiff("d", rsEmpResults.Fields("Test_Date").Value, Date) > 30 Then
                        bPending = True
                    End If
                Else
                    '6 months
                    If DateDiff("d", rsEmpResults.Fields("Test_Date").Value, Date) > 180 Then
                        bPending = True
                    End If
                End If
                If bPending = True Then
                    ReportSheet.Cells(iCounter, 1) = "Pending"
                    ReportSheet.ActiveSheet.Rows(iCounter).Font.Color = "Red"
                    iPendUserCount = iPendUserCount + 1
                    ElseIf bPending = False Then
                    ReportSheet.ActiveSheet.Rows(iCounter).Font.Color = "Green"
                End If
            Else
                'Test Type
                ReportSheet.Cells(iCounter, 4) = ""
                'Result
                ReportSheet.Cells(iCounter, 5) = ""
                'Comments
                ReportSheet.Cells(iCounter, 6) = ""
                
                'Level
                ReportSheet.Cells(iCounter, 7) = ""
                'Attempt
                ReportSheet.Cells(iCounter, 8) = ""
                'Date
                ReportSheet.Cells(iCounter, 9) = ""
                'Marks
                ReportSheet.Cells(iCounter, 10) = ""
                'Obtd %
                ReportSheet.Cells(iCounter, 11) = ""
                'projectid
                'ReportSheet.Cells(iCounter, 12) = ""
                
                'Setting Status
                ReportSheet.Cells(iCounter, 1) = "Pending"
                iPendUserCount = iPendUserCount + 1
                ReportSheet.ActiveSheet.Rows(iCounter).Font.Color = "Red"
            End If
            rsReadEmaminee.MoveNext
            iCounter = iCounter + 1
        Wend
    End If
    ReportSheet.ActiveSheet.UsedRange.Font.Name = "Verdana"
    ReportSheet.ActiveSheet.UsedRange.Borders.Application = "True"
    Call DeleteSheets
    ReportSheet.ActiveSheet.UsedRange.Borders.Weight = xlThin
    ReportSheet.Caption = "Reports"
    CmdAnswerSheet.Enabled = False
    ReportSheet.Caption = "Project Status Report"
    
    lblExcelNote.Visible = False
    LblStatus.Caption = (iFinalUserCount - iPendUserCount) & "/" & iPendUserCount
    Call SetGridFormat
    
 End Sub

The part in red is where the error pops on debugging it highlights activesheet.

i tried to look what reportsheet is but i was not able to determine the same. As i did not find any sheet named the as ReportSheet

if you could point me in a direct to look even that would be helpful.

 

[JainAbraham]

sorry for the delayed response, was down with flu so was not online.

i am trying to still figure out what those line of code does, will keep you updated.

thanks for all the help.