Filter data in a subform (1 Viewer)

[brainox]

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.

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

 

[James Dudden]

Try adding a requery to your code as follows:

Me.frm_staffSub.Form.requery

 

[brainox]

Try adding a requery to your code as follows:

Me.frm_staffSub.Form.requery

Cheers, but it is still doing the same thing :/

 

[James Dudden]

Are you sure that there should be more records using the filter criteria? If you are then maybe something is wrong in your SQL?

Have you tried stepping through the code to check what values are being passed and what the SQL ends up looking like?

In this situation I often hard write the values I am passing the SQL to see if the results are as expected.

 

[brainox]

Are you sure that there should be more records using the filter criteria? If you are then maybe something is wrong in your SQL?

Have you tried stepping through the code to check what values are being passed and what the SQL ends up looking like?

In this situation I often hard write the values I am passing the SQL to see if the results are as expected.

I ran the sqlstr and sqlstrwhat through the immediate window and passed it through SQL and got an error
"Syntax error in union query"
However I am unsure what that means, thanks for ya help

 

[James Dudden]

Not sure why the error relates to a Union query as that's not what you're doing but give this a go:

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 = " WHERE tbl_Structure_Staff_hierarchy.shy_empno = " & Me!emp_no
End If
If sqlstrwhat = "" Then
sqlstr = sqlstr
Else
sqlstr = sqlstr & sqlstrwhat
End If
Me.frm_staffSub.Form.RecordSource = sqlstr
End Sub