I have a subform in a form that displays all members of staffs Name and employee no. I am trying to find a way to search the subform on the main form it's on using textboxes to filter data, here is the code I have that searches the employee no.
I do not get any errors and I haver used the same code structure for other subform filters but when I search for an existing employee no. the subform only displays a blank record with the employee no. 1?
Any ideas?
Thanks a lot in advance
Code:
Private Sub Command178_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String
Set dbs = CurrentDb
sqlstr = "SELECT tbl_Structure_Staff_hierarchy.* FROM tbl_Structure_Staff_hierarchy"
If Nz(Me!emp_no, "") = "" Then
Else
sqlstrwhat = "((tbl_Structure_Staff_hierarchy.shy_empno) = " & Me!emp_no & ") AND "
End If
If sqlstrwhat = "" Then
sqlstr = sqlstr
Else
sqlstr = sqlstr & " WHERE (" & Left(sqlstrwhat, (Len(sqlstrwhat) - 4)) & ")"
End If
Me.frm_staffSub.Form.RecordSource = sqlstr
End Sub
I do not get any errors and I haver used the same code structure for other subform filters but when I search for an existing employee no. the subform only displays a blank record with the employee no. 1?
Any ideas?
Thanks a lot in advance