Filter Problem on a subform

[captainlove]

I have a main form that has two combo boxes, txtuser and txtqcp. My subform has revision date and revision text controls

I want to filter revision date and revision based on the selection I make for a txtuser and txtqcp(i.e user BP and qcp 10000)

so that on my sub form it would have the revision date and revision that applies only to a particular user.


This is the code I got off microsoft site , but I need to twick it to suite my purpose can anyone hep

My controls are txtrevisiondate, txtrevision

Private Sub Set_Filter_Click()
Dim strSQL as String, intCounter as Integer

' Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " _
And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptCustomers].Filter = strSQL
Reports![rptCustomers].FilterOn = True
End If
End Sub


Thanks

 

[RuralGuy]

' Set the Filter property.
Me.SubFormControlName.FORM.Filter = strSQL
Me.SubFormControlName.FORM.FilterOn = True
...using YOUR SubFormControlName of course.

 

[captainlove]

Rural guy,
What should the sql statement look like

 

[captainlove]

Me.SubFormControlName.FORM.Filter = strSQL
Me.SubFormControlName.FORM.FilterOn = True

would go

Me.txtrevisiondate.sfrmhistory.filter=strSQl
Me.txtrevision.sfrmhistory.filterOn=True

Would this go on the main form or subform

 

[RuralGuy]

This is the suggestion:

Private Sub Set_Filter_Click()
   Dim strSQL As String, intCounter As Integer

   ' Build SQL String.
   For intCounter = 1 To 5
      If Me("Filter" & intCounter) <> "" Then

         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
                  & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
                  And ""
      End If
   Next

   If strSQL <> "" Then
      ' Strip Last " And ".
      strSQL = Left(strSQL, (Len(strSQL) - 5))

      [COLOR="Red"]' Set the Filter property.
      Me.sfrmhistory.Form.Filter = strSQL
      Me.sfrmhistory.Form.FilterOn = True[/COLOR]
   End If
End Sub

...assuming sfrmhistory is the name of your SubFormControl.

 

[captainlove]

The controls I want to filter from are txtuser and txtqcp from the main form.


This filters into the subform that has two controls txtrevision and txtrevison date.

My code look like this and I am getting an error on the third and fourth line


'Build SQl String
If Me("txtuser" & txtQcp) <> "" Then
StrSQl = StrSQl & "[" & Me("txtuser" & txtQcp) & "]"
&" = "& char(34) & Me("txtUser" & txtQcp) & char (34) &"_
And "
End If

If StrSQl <> "" Then
'Strip Last "And".
StrSQl = Left(StrSQl, (Len(StrSQl) - 2))

'Set the Filter Property.

Me![sfrmHistory].Form.filter = "qcpId = " & Me.txtQcp
Me![sfrmHistory].Form.filter = "UserId = " & Me.txtUser
Me![sfrmHistory].Form.FilterOn = True

 

[RuralGuy]

Try:

Private Sub Set_Filter_Click()
   Dim strSQL As String

   ' Build SQL String.
   strSQL = "[qcpId] = '" & Me.txtQcp & "' AND [UserId] = '" & Me.txtUser & "'"

   ' Set the Filter property.
   Me.sfrmHistory.Form.Filter = strSQL
   Me.sfrmHistory.Form.FilterOn = True
End Sub

 

[captainlove]

I am getting an click when I click the command button saying you cancelled the previous operation

Private Sub cmdDisplay_Click()
On Error GoTo Err_cmdDisplay_Click
Dim strSQL As String
Dim Sql As String ' <-------- 'Declarations
DoCmd.SetWarnings off

'Delete data from temp tables

Sql = "DELETE * from tblTempRef"
DoCmd.RunSQL Sql
Sql = "DELETE * from tblTempResp"
DoCmd.RunSQL Sql

If Len(Me.txtQcp) = 0 Then
Exit Sub
End If

If Len(Me.txtUser) = 0 Then
Exit Sub
End If

'Insert Data into the tempresp table and tempref table

Sql = "INSERT INTO tblTempResp (qcpID, Responsibilities, Accepted, ID) SELECT " & _
"tblResponsibilities.qcpID, tblResponsibilities.Responsibilities, IIf(IsNull([UserID]),False,True) " & _
"AS Accepted, tblResponsibilities.Resp_Id FROM (tblQcp INNER JOIN tblResponsibilities ON " & _
"tblQcp.qcpID = tblResponsibilities.qcpID) LEFT JOIN qryRespLinkUser ON " & _
"tblResponsibilities.Resp_id = qryRespLinkUser.RespID WHERE tblQcp.qcpID = " & Me.txtQcp & _
" GROUP BY tblResponsibilities.qcpID, tblResponsibilities.Responsibilities, IIf(IsNull([UserID]),False,True), " & _
"tblResponsibilities.Resp_Id"
DoCmd.RunSQL Sql

Sql = "INSERT INTO tblTempRef (qcpID, References, Accepted,ID ) SELECT tblReferences.qcpID, " & _
"tblReferences.References, IIf(IsNull([UserID]),False,True) AS Accepted, tblReferences.Ref_id " & _
"FROM (tblQcp INNER JOIN tblReferences ON tblQcp.qcpID = tblReferences.qcpID) LEFT JOIN " & _
"qryRefLinkUser ON tblReferences.Ref_id = qryRefLinkUser.RefID WHERE tblQcp.qcpID = " & Me.txtQcp & _
" GROUP BY tblReferences.qcpID, " & _
"tblReferences.References, IIf(IsNull([UserID]),False,True), tblReferences.Ref_id "
DoCmd.RunSQL Sql




' Build SQL String.
strSQL = "[qcpId] = '" & Me.txtQcp & "' AND [UserId] = '" & Me.txtUser & "'"
' Set the Filter property.
Me.sfrmHistory.Form.filter = strSQL
Me.sfrmHistory.Form.FilterOn = True



Me![sfrmHistory].Form.Requery
Me![frmResponsibilities].Form.Requery
Me![frmreferences].Form.Requery
DoCmd.SetWarnings True
Exit_cmdDisplay_Click:
Exit Sub
Err_cmdDisplay_Click:
MsgBox Err.Description
Resume Exit_cmdDisplay_Click

End Sub

 

[RuralGuy]

If you use CurrentDb.Execute instead of DoCmd.RunSQL there is no reason to turn off the Warnings and maybe it will tell you something. You also need:
Option Compare Database
Option Explicit
...at the top of your code module which will force you to define your variables before you use them. It will also catch typo's for you.