On click of the following button it is suppose to call a sub function (either searchdepartment or searchname) the issue is: When searchname is called it returns the following error -2147467259 (80004005))
Private Sub cmdcheck_Click()
button = "cmdcheck"
If cbodepartment.Text <> "" And cbodepartment.Enabled = True Then
searchdepartment
ElseIf cboname.Text <> "" And cboname.Enabled = True Then
searchname
Else
MsgBox "Do you want to search by department or Individual!!!"
Exit Sub
End If
End Sub
For comparison here are the two subs
Private Sub searchname()
'On Error Resume Next
t = 0
Dim SearchSQLString As String
SearchSQLString = " "
frmlogindetails.LstSearchResult.ListItems.Clear
With DataEnvironment1.rsLoginDetails
SearchSQLString = "SELECT * FROM logindetails WHERE names like '" & cboname.Text & "' and approved = '" & "NO" & " ' "
PASSES THE RESULTS TO THE FOLLOWING FUNCTION
SearchFucnction SearchSQLString, cboname.Text
.MoveFirst
.Open "select hoursin from logindetails where names like '" & cboname.Text & "' and approved = '" & "NO" & " ' "
Do While Not .EOF
t = t + !hoursin
.MoveNext
Loop
txttotal.Text = t
.Close
End With
End Sub
Public Sub searchdepartment()
On Error Resume Next
t = 0
Dim SearchSQLString As String
SearchSQLString = ""
frmlogindetails.LstSearchResult.ListItems.Clear
With DataEnvironment1.rsLoginDetails
SearchSQLString = "SELECT * FROM logindetails WHERE department like '" & cbodepartment & "' and ldate >= '" & dtdate.Value & "' and approved = '" & "NO" & " ' ORDER BY ldate ASC, hoursin ASC"
SearchFucnction SearchSQLString, cbodepartment.Text
.MoveFirst
.Open "select hoursin from logindetails where department='" & cbodepartment.Text & "' and ldate>= '" & dtdate.Value & " ' and approved = '" & "NO" & " ' "
Do While Not .EOF
t = t + !hoursin
.MoveNext
Loop
txttotal.Text = t
.Close
End With
End Sub
THE FUNCTION THAT RECEIVES THE VALUES
Public Function SearchFucnction(sqlstring As String, ComboValue As String)
Set rssearch = Nothing
If DataEnvironment1.ConnEmployee.State = adStateClosed Then DataEnvironment1.ConnEmployee.Open
If rssearch.State <> adStateClosed Then rssearch.Close
With rssearch
.ActiveConnection = DataEnvironment1.ConnEmployee
.Open sqlstring
End With
If rssearch.RecordCount = 0 Then
frmlogindetails.LstSearchResult.ListItems.Clear
frmlogindetails.LstSearchResult.ForeColor = vbRed
frmlogindetails.txttotal = 0
Set lvs = frmlogindetails.LstSearchResult.ListItems.Add(, , " No")
lvs.SubItems(1) = "Matching"
lvs.SubItems(2) = "Records"
lvs.SubItems(3) = " Found"
lvs.SubItems(4) = " Check the dates "
lvs.SubItems(5) = " again and"
lvs.SubItems(6) = " continue"
frmlogindetails.LstSearchResult.GridLines = False
frmlogindetails.LstSearchResult.Enabled = False
Else
FillSearchListView ComboValue
End If
End Function
The error arises in the underlined part of the following bit of the above function:
With rssearch
.ActiveConnection = DataEnvironment1.ConnEmployee
.Open sqlstring
End With
THE ERROR READS:
runtime error -2147467259 (80004005)
Method 'open' of object '_recordset' failed
please help.