Search Facility that searches but doesn't retrieve records

04-16-2004, 02:37 AM
Hi everyone,

I wonder if there is someone who will be able to help me with this. I'm creating a program which uses a SQLServer table (tblReturn) to hold data on employees. The VB front end is meant to allow the user to search, view and edit the data in the table.

I am having a little trouble with the Search facility in that it doesn't appear to Search!! The user has the option to choose from a list box to Search by either employee surname or employee number they then enter in a text box (SearchKey) the name or number that they are looking for. The results should then be displayed in a list view box (lvwPersonSearch). No errors occur and my facility appears to go away and look for the records as a scroll bar appears on the side of the list view box once the Search is complete, however the problem is it does not bring back any records of people that I know exist!!

I had thought that I have used similar code to this in the past but there is obviously some fundamental problem. I have tried to take as much of the unnecessary code out as possible. I would be grateful if anyone has any ideas of where I am going wrong.


Option Explicit
Dim strSQL As String
Dim Rs As Recordset
Dim SQL As String
Dim strSQLTable As String
Dim strSQLWhere As String
Dim strSearchField As String
Dim lngSearchField As Long
Dim ValidSearch As Boolean

Private Sub cmdSearch_Click(Index As Integer)
'Search for Employees and fill lvwPersonSearch
On Error GoTo ErrHandler
strSQLTable = "tblReturn"

Call ValSearchField 'Validate Search Field/s
If ValidSearch Then
Call LoadlvwPersonSearch
End If

End Sub

Private Sub lstSearchKey_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
'Switch the Focus to the SearchKey text box when search field selected
If strSearchField <> lstSearchKey.List(lstSearchKey.ListIndex) Then
strSearchField = lstSearchKey.List(lstSearchKey.ListIndex)
txtSearchKey.Enabled = True
Else 'Toggle Field selection
strSearchField = ""
lstSearchKey.ListIndex = -1
txtSearchKey.Enabled = False
End If

'Initialise Search Key Fields
txtSearchKey.Text = ""

Private Sub ValSearchField()
'If required, Validate Search Key/s
ValidSearch = True
strSQLWhere = ""
If Trim(strSearchField) = "" Then GoTo ExitHandler

If Trim(txtSearchKey.Text) = "" Then
MsgBox "Please Enter Search Key", vbExclamation + vbOKOnly, "Employee Search"
ValidSearch = False
GoTo ExitHandler
End If

If Trim(strSearchField) = "EmployeeSurname" Then
strSQLWhere = " WHERE Surname = '" & Trim(txtSearchKey.Text) & "'"
End If

If Trim(strSearchField) = "EmployeeNumber" Then
strSQLWhere = " WHERE PayrollReference = '" & Trim(txtSearchKey.Text) & "'"
End If

Private Sub LoadlvwPersonSearch()
'Load lvw\personSearch with List of Employees
Dim lngCount As Long
Dim lngRecordCount As Long
Dim lngRow As Long
Dim Rs As ADODB.Recordset
Dim strSQL As String

On Error GoTo ErrHandler

strSQL = "SELECT Posting, NINumber, Surname, Initials, Sex, EmploymentType FROM "
strSQL = strSQL & strSQLTable
If strSQLWhere <> "" Then
strSQL = strSQL & strSQLWhere
End If
strSQL = strSQL & " ORDER BY Surname"

Set Rs = New ADODB.Recordset
Rs.Open strSQL, DBEnv.Payrolldb.ConnectionString, adOpenStatic, adLockReadOnly
lngRecordCount = Rs.RecordCount

If lngRecordCount <> 0 Then
'Fill Person Search List View
With lvwPersonSearch.ListItems
For lngRow = 1 To lngRecordCount
.Add , , Trim(Rs.Fields(0)) 'Server Name
With lvwPersonSearch.ListItems(lngRow).ListSubItems
.Add , , "" & Trim(Rs.Fields(1)) 'Posting
.Add , , "" & Trim(Rs.Fields(2)) 'NINumber
.Add , , "" & Trim(Rs.Fields(3)) 'Surname
.Add , , "" & Trim(Rs.Fields(4)) 'Initials
.Add , , "" & Trim(Rs.Fields(5)) 'Sex
End With
Next lngRow
End With
Else 'No Employees found
lvwPersonSearch.ListItems.Add , , "0"
lvwPersonSearch.ListItems(1).ListSubItems.Add , , "No Employees Found"
End If


End Sub

04-16-2004, 03:09 AM

Could you output the whole strSQL (after you have composed the whole string and passed parameters) into the immediate window and show it here
(please use vb-tags foe a better readability)


04-16-2004, 05:10 AM
Sorry if I have posted this in the wrong format and thanks for replying so soon but it seems as if my program has decided to work now after 2 days of refusing to do anything, which i strange since I don't think I have made any changes!!!

04-16-2004, 05:12 AM
Good news...

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum