Filter Form using VBA

[Xarkath]

Hello,



I am adding a button to a form that should filter the work items down to the the items that are open and that are also assigned to the current user. I thought that this would be simple but I have tried several variations of the following 2 code samples but I keep getting errors, mostly Run-Time error '13': Type Mismatch. I have a feeling that I am using the wrong number of quotes and/or applying them to the wrong places but so far none of the combos I have tried has worked so I am hoping someone can point out what I am doing wrong.

Sample 1:

Private Sub ECAOpenItems_Click()
Dim usr As String
usr = Environ("UserName")
Me.Filter = "[WIStatus] = 'Open'" And "[ECAAssigned] = '" & usr & "'"
End Sub

Sample 2:

Private Sub ECAOpenItems_Click()
Dim usr As String
usr = Environ("UserName")
DoCmd.ApplyFilter, "[WIStatus] = 'Open'" And "[ECAAssigned] = '" & usr & "'"
End Sub


Thanks

[orange]

See this post for syntax and example.

[ssanfu]

I have a feeling that I am using the wrong number of quotes

Yes, you have too many quotes (Before and after the AND).

'Open'" And "[ECAAssigned]

Try this:
Sample 1:

Private Sub ECAOpenItems_Click()
  Dim usr As String

  usr = Environ("UserName")

  Me.Filter = "[WIStatus] = 'Open' And [ECAAssigned] = '" & usr & "'"
  Me.FilterOn = TRUE    ' also need this line
End Sub

Never used this form, but there are too many quotes
Sample 2:

Private Sub ECAOpenItems_Click()
  Dim usr As String
  usr = Environ("UserName")
  DoCmd.ApplyFilter, "[WIStatus] = 'Open' And [ECAAssigned] = '" & usr & "'"
End Sub

[Xarkath]

Thanks for the responses. Much appriciated and as usual it seems I was over thinking/complicating things. Code works perfect now.