JainAbraham
Board Regular
- Joined
- Feb 19, 2014
- Messages
- 92
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.
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.
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.
Code:
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 SubThe 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.
