My form isn't filtering

[Seth Schrock]

I'm attempting to filter a subform based on a user ID, but no filter is taking place. I get no errors (run-time or compiler), but nothing happens. I don't work with filters much so I would assume that I'm doing something wrong, but I can't find it.

Here is the query that is the recordsource for the subform:

SELECT tblLoans.LoanID
, tblLoans.LoanNumber
, tblLoans.Borrower
, tblAppraisals.DateOrdered
, tblAppraisals.DateReceived
, tblLoans.Processor
, tblLoans.LoanOfficer
FROM tblAppraisals 
INNER JOIN tblLoans ON tblAppraisals.LoanID = tblLoans.LoanID
WHERE (((IsNull([CompletedDate]))<>False));

The Processor and LoanOfficer fields are what I'm trying to filter. However, the form doesnt' contain these fields. I didn't think that it needed to, but I could be wrong.

Here is my code to filter the form:

Private Sub Form_Load()
On Error GoTo Form_Error

Dim strFilter As String


Call GetUserID

If IsNull(Application.TempVars("UserID").Value) Then
    Call LockSystem
    Exit Sub
End If

Select Case Application.TempVars("UserType").Value
    Case 1 'Loan Operations
    'No filter
    Me.cmdAdministrativeForms.Visible = True
    Me.cmdAddressSearch.Visible = True
    
    strFilter = ""
    
    Case 2 'Loan Processor
    Me.cmdAdministrativeForms.Visible = False
    Me.cmdAddressSearch.Visible = False
    
    strFilter = "Processor = " & Application.TempVars("UserID").Value
    
    Case 3 'Loan Officer
    Me.cmdAdministrativeForms.Visible = False
    Me.cmdAddressSearch.Visible = False
    
    strFilter = "LoanOfficer = " & Application.TempVars("UserID").Value
    
    Case 4 'Administrator
    'No filter
    Me.cmdAdministrativeForms.Visible = True
    Me.cmdAddressSearch.Visible = True
    
    strFilter = ""
    
End Select
Me.sfrmAppraisalsSummary.Form.FilterOn = True
Me.sfrmAppraisalsSummary.Form.Filter = strFilter

Me.sfrmCentralizedProcessingSummary.Form.FilterOn = True
Me.sfrmCentralizedProcessingSummary.Form.Filter = strFilter

Me.sfrmMortgageLogSummary.Form.FilterOn = True
Me.sfrmMortgageLogSummary.Form.Filter = strFilter


'MsgBox "User ID: " & Application.TempVars("UserID").Value & vbCrLf & _
       "User Type: " & Application.TempVars("UserType").Value
       


Form_Exit:
    Exit Sub


Form_Error:
    MsgBox "Error Description: " & Err.Description & vbCrLf & _
           "Number: " & Err.Number & vbCrLf
        
    Resume Form_Exit
           
    
End Sub

I know my TempVars are working properly, because the buttons are correctly being visible or invisiable. My problem is that when I have a loan processor log on (case 2), I can still see all the records. I haven't tried a loan officer yet, but I would assume that since the code is the same, I will get the same result.

[Rabbit]

Are those fields string values? Because if they are you need to surround the values in quotes.

[Seth Schrock]

The table is storing them as numbers. However in the table I have them with a combo box lookup from a different table so the visible value is a string. I'm attempting to filter based in the numerical value as I believe that is the correct way to do it.

Also, if my filter was wrong, wouldn't it filter out all of the records instead of showing them all?

[Rabbit]

I don't know if this is the issue but try flipping the order of setting the filter and turning the filter on.

[zmbd]

Rabbit more than likely has it for you!

Another thing I've had to do when changing a filter is to "refresh" the form (subform). You don't need a "requery" as you're simply filtering.

[Seth Schrock]

I will try both your ideas when I get to work tomorrow. I have seen the both ways online with the filter being set before turning it on and also after turning it on, but I will give it a shot. The refresh also seems likely.

[NeoPa]

The only value your code is trying to filter by is the UserID. From your other questions I'm guessing this is the Windows User ID - which is a text field. The filter string you are using is treating it as a numeric literal rather than a text one. You need something like (although I suspect the Application. part is redundant) :

strFilter = "[LoanOfficer] = '" & Application.TempVars("UserID") & "'"

See Quotes (') and Double-Quotes (") - Where and When to use them.

It also makes better sense to turn the filter on only after it has a value set (As Rabbit suggested).

[Seth Schrock]

I actually have a table that lists the users that are allowed in my database. I use the windows user name to get the primary key for that employee (UserID). All of the loans (the records that I'm filtering) have a loan officer and a loan processor assigned to it. Part of the employee table is an Employee Type, hence the UserType TempVar. If the user is a Loan Processor, then I filter based on the Processor field. If the user is a Loan Officer, I filter based on the LoanOfficer field. Both of these fields are tied to the Employee table's EmployeeID field, thus I need number fields not strings. But you are correct in tying my other questions in with this one as this is all part of the same project.

[NeoPa]

Ah. In that case just switch the two property settings around, but don't always set .FilterOn to True. If there's no filter then it doesn't make good sense. Try :

Me.sfrmAppraisalsSummary.Form.Filter = strFilter
Me.sfrmAppraisalsSummary.Form.FilterOn = (strFilter > "")

Me.sfrmCentralizedProcessingSummary.Form.Filter = strFilter
Me.sfrmCentralizedProcessingSummary.Form.FilterOn = (strFilter > "")

Me.sfrmMortgageLogSummary.Form.Filter = strFilter
Me.sfrmMortgageLogSummary.Form.FilterOn = (strFilter > "")

[Seth Schrock]

Well I certainly feel better that I wasn't too far off with my attempt. I will give this a try tomorrow and will let you know the results.

[Seth Schrock]

That did it. Thanks everyone. I also used your solution of only turning on the filter if necessary, which makes perfect sense.

[NeoPa]

8-) Thanks for the update.

It does more than just that though Seth. It also turns it off (Not in this situation of course, but in code that gets run more than once.) after it's been used before, but is not required on this particular iteration.