searching for records but keeping filter on the form

[mindbender]

I have a form that is filtered to only show the records aligned to the user when they log in. ON that same form I have a search box with the following code I found

Private Sub CmdFilter_Click()
Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.AccountName) Then
        strWhere = strWhere & "([Account] Like ""*" & Me.AccountName & "*"") AND "
    End If
   
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then 
        MsgBox "No criteria entered", vbInformation, "Null Search."
    Else 
        strWhere = Left$(strWhere, lngLen)
        Debug.Print strWhere
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

The issue is when I execute the search it returns all the records that match and not just the ones that belong to the logged in user. I am assuming I need to add a second criteria to the strWhere to limit the results to the current user to the following second of the search code

strWhere = strWhere & "([Account] Like ""*" & Me.AccountName & "*"") AND "

but I have been unable to get it to work.

The main form is filtered by looking at the value in the login form

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[XeroxCanada_EmpNo] = '" & Forms!Logon_Form!cboUser.Column(0) & "'"""
 RunCommand acCmdApplyFilterSort
End Sub

Any help would be appreciated as always.

[pbaldy]

Try

strWhere = strWhere & "[Account] Like '*" & Me.AccountName & "*' AND [XeroxCanada_EmpNo] = '" & Forms!Logon_Form!cboUser.Column(0) & "'"

[mindbender]

I get RunTime error 13 Type Mismatch. They both are Short Text as far as I can tell.

[pbaldy]

Use this and see what strWhere ends up being:

http://www.baldyweb.com/ImmediateWindow.htm

can you attach the db here?

[mindbender]

I attached it. The code is on the onclick event of the Filter button at the top of frm_main

Thanks for looking into this for me

[pbaldy]

It's annoyingly hard to test when you hide the Access window, but this:

strWhere = strWhere & "[Account] Like '*" & Me.AccountName & "*' AND [XeroxCanada_EmpNo] = '" & Forms!Logon_Form!cboUser.Column(0) & "'" And ""

should be

strWhere = strWhere & "[Account] Like '*" & Me.AccountName & "*' AND [XeroxCanada_EmpNo] = '" & Forms!Logon_Form!cboUser.Column(0) & "' AND "

Note the change at the end.

[mindbender]

sorry about that. When I try to remove the last " it automatically puts it back everytime

[pbaldy]

Did you notice the others? Copy/paste that line and try it.

[mindbender]

Sorry I missed that one. It works as expected now.

Thank you very much for the help

[pbaldy]

Happy to help!

[mindbender]

sorry but I forgot the undo step.

When they hit the remove filter it shows all records instead of their records only.

I added this to the code but it returns 0 records

[CODE][/Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control
Dim strWhere As String

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's filter.
Me.Filter = strWhere = strWhere & "[Account] Like '*" & Me.AccountName & "*' AND [XeroxCanada_EmpNo] = '" & Forms!Logon_Form!cboUser.Column(0) & "'"

End Sub
CODE]

[mindbender]

sorry I figured it out fat fingered =strWhere