Filter Conflicts

[Stang70Fastback]

Hello folks. I'm sure this is another simple one, but I can't seem to find the solution anywhere.

I'm using VBA code to set a form's filter and sort order based on certain criteria (mostly, which user is logging into the database). For example:

    If DCount("[Username]", "USER", "[Username] = '" & fOSUserName & "' AND [Authority] = 'Normal'") = 1 Then
        Me.Filter = "[CreatedName] = '" & fOSUserName & "'"
        Me.FilterOn = True
        Me.OrderBy = "[WeekEnding] DESC"
        Me.OrderByOn = True
    ElseIf DCount("[Username]", "USER", "[Username] = '" & fOSUserName & "' AND [Authority] = 'Admin'") = 1 Then
        Me.Filter = "Not IsNull([SubmittedName]) OR [CreatedName] = '" & fOSUserName & "'"
        Me.FilterOn = True
        Me.OrderBy = "[ManagerName] IS NULL ASC, [WeekEnding] DESC, [LastName] ASC"
        Me.OrderByOn = True
    Else
        Me.Filter = "Not IsNull([ManagerName]) OR [CreatedName] = '" & fOSUserName & "'"
        Me.FilterOn = True
        Me.OrderBy = "[TimekeeperName] IS NULL ASC, [WeekEnding] DESC, [LastName] ASC"
        Me.OrderByOn = True
    End If

This works fine. HOWEVER, these filter criteria "stick" in the form's filter and order properties in the right-hand pane. What this means is that if I switch from one user to another, such that the VBA code now wants to apply different filters and sorts, the form just shows up blank (as in a giant white box). I think it's because the last used filter and order criteria are sticking in the form properties, and are conflicting with the new filter and order criteria that my VBA code is trying to apply. If I clear out the filter and order stuff in the form properties, then the VBA code works fine again.

I tried adding Me.FilterOn = False, and Me.OrderByOn = False ahead of that chunk of code, thinking if I turned off the filters before resetting them, it should work but it doesn't help. How can I get the form to NOT remember filter settings the next time the ACCDE file is opened?

 

[pbaldy]

I'm more likely to set the form's record source, but you can try setting the properties to blank:

Me.Filter = ""

 

[Stang70Fastback]

I'm more likely to set the form's record source, but you can try setting the properties to blank:

Me.Filter = ""

I just tried that and it didn't help, unfortunately. I still get a giant blank white space where the form is supposed to be, and if I edit the form, I still see the previous filters in the form's properties.

:banghead:

 

[pbaldy]

Where did you try it? I was thinking the on load event, but not sure of your situation.

 

[Stang70Fastback]

This whole chunk of code is in the Form Load event. I'm not doing anything outside of this event with regards to the VBA code.

 

[pbaldy]

Curious. Since presumably you're logging off the computer to change users, I'd expect the filter to work for the current user. If you want to switch, I just change the record source, like:

Me.RecordSource = "SELECT Blah FROM TableName WHERE [CreatedName] = '" & fOSUserName & "' ORDER BY [WeekEnding] DESC"

 

[Stang70Fastback]

So I added several "MsgBox" markers throughout my Form_Load code to see if it is working as intended, and it turns out that NONE of them appeared. So the Form_Load event doesn't seem to be even firing. So it looks like I'm getting white space where that form is supposed to be because the form isn't loading AT ALL.

I'm even more confused now...

(FYI, I tried your RecordSource method, but obviously it didn't work either given what I just stated, lol.)

EDIT: Just to re-iterate, if I clear out the Filter and Order statements from the form's properties pane, then it loads fine, and the Form_Load event fires and everything works.

 

[pbaldy]

Really? That's bizarre. Can you attach the db here? Or clear out the filter and order by properties, save the form, then try my method (since it never sets them, shouldn't be an ongoing problem).

 

[Stang70Fastback]

Really? That's bizarre. Can you attach the db here? Or clear out the filter and order by properties, save the form, then try my method (since it never sets them, shouldn't be an ongoing problem).

Ohh, that's a good idea. Let me try that instead. Will report back in a few. I appreciate the help. Is that a better method to use in general? Is there a reason to use one method over the other?

 

[pbaldy]

I'd say it's personal preference, though in my head I worry that the filter method pulls all records and then filters, where the record source method will only pull filtered records, thus making it more efficient. I'm not saying that's what happens, but I figure why chance it? Let's stick with personal preference since I can't back up my fear.

 

[Stang70Fastback]

I love you long time!!! <3

 

[pbaldy]

Happy ending?

 

[Galaxiom]

My impression is that the Form_Load Event runs after the form is loaded with whatever properties it had initially. Consequently the previous filter would apply until the Load event subsequently replaced them.

The Form_Open Event runs earlier so is potentially a better place to implement a dynamic RecordSource since any original RecordSource query would run if the change was made in the Load event.

However the OP's real problem is with the Form_Load Event not actually being triggered. This suggests the form is corrupted. I have experienced this same problem with the Load Event once myself, quite recently.

I had to rebuild the form (using copy and paste of the controls and code to a new form) because the problem persisted after both Compact and Repair, and a Decompile

I would strongly recommend the OP also does this with their form because a corrupted object may have other issues.