Suddenly can't open a form (due to filtering, I think) (1 Viewer)

[Jonathan Kok]

Allright, so I've got a Purchase Order database that I created a number of years ago. It's been working fine, up until last week. When a user other than myself attempts to open the 'receive po' form from their workstation, I can see in the status bar that it calculates, then filters the form, but nothing actually appears, and I'm back to the switchboard. The form is designed to filter based on a user's security level (stored in a table) using the 'applyfilter' function. The username is pulled from their network userid using a function that runs from the switchboard, and matched to a userid in a table. Here's the code:

'When form is first opened, adjusts it to suit user
'Last update 10/07/00
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    Dim UsersName
    
    UsersName = GetCurrentUserName()
         
    If Forms!Switchboard!secLevel = 5 Then
       
    ElseIf Forms![Switchboard]![secLevel] = 4 Then
        DoCmd.ApplyFilter , "OrdSecure = False Or OrdEnteredBy = '" & UsersName & "'"
    ElseIf Forms![Switchboard]![secLevel] = 3 Then
        DoCmd.ApplyFilter , "OrdEnteredBy = '" & UsersName & "'"
    Else
        DoCmd.Close
    End If
    DoCmd.Maximize

Exit_Form_Open:
    Exit Sub

Err_Form_Open:
    MsgBox Err.Description
    Resume Exit_Form_Open

End Sub

Like I said, this worked until last week. Suddenly, only *I* have access to this particular form. I can log into any workstation on the network, and it works. No-one else can. All the machines run Windows XP and Office 2003, at various service pack levels, with or without .NET installed. Whether it's WinXP SP1 and the core Office, no .NET, or WinXP SP2 and Office SP1 with .NET 1.1, the same thing happens. I am an administrator on the domain, and all the users are administrators of the local machine(s).

I have other forms performing the exact same operation, word for word, and there's no problems with them. Just this one. I've commented out the close function, and the same thing happens, so it's not that. I've dropped my DB security level to match the other users, and I can still open it without a problem. The only way other users can open it is if I comment out the filter function. Which is a potential solution, but the point of the filtering is so that people can't mess up other people's PO's.

The only thing I can think of is that it has something to do with the fact I'm an admin on the domain, but that shouldn't make a difference whatsoever. Yet it does. Which makes no sense.

Any ideas?

 

[WayneRyan]

Jonathon,

What is the user's secLevel?

You're missing a line of code after "If secLevel = 5".

You also are obviously doing the DoCmd.Close. Did you add a new user
with a single-quote in their username? If so, you are probably not
getting their "proper" secLevel.

Have you run it with the debugger and inspected the appropriate values?

Wayne

 

[Jonathan Kok]

Thanks for the post! Did a little more digging, and found out that it has nothing to do with my administrative status. Changing my secLevel to 3 or 4 prevents me from being able to open it as well. Which means the error is in the filtering code.

I added msgbox commands to show the values of UsersName and SecLevel prior to running the IF, and both came back with the correct values, so that's not the issue. Again, commenting out the filters allows it to run as well.

As I stated before, I have another form doing the exact same thing, with no problem. Here's the code from that form:

Private Sub Form_Open(Cancel As Integer)
    Dim UsersName
    
    UsersName = GetCurrentUserName()
         
    If Forms!Switchboard!secLevel = 5 Then
        
    ElseIf Forms![Switchboard]![secLevel] = 4 Then
        DoCmd.ApplyFilter , "OrdSecure = False Or OrdEnteredBy = '" & UsersName & "'"
    ElseIf Forms![Switchboard]![secLevel] = 3 Then
        DoCmd.ApplyFilter , "OrdEnteredBy = '" & UsersName & "'"
    Else
        DoCmd.Close
    End If

End Sub

Can you see a difference that I can't?

It is definitely *not* doing the 'docmd.close' function, as I put a break in there, and the code never stopped running. In case it ignored the break for whatever reason, I commented that line out, and it still closed.

One last thing. I made an error in the msgbox command (referencing 'username' instead of 'usersname'), and it would not open, irrelevant of secLevel, and gave me *no* error messages. This leads me to believe that an error has somehow been caused in the code, and the debugger is not prompting me to fix it for some reason.


Of course, I've been troubleshooting as I've been typing this post. And now, it works. I don't know why, it just does. Everything is exactly as it was, but after adding/removing message boxes, commenting out lines, etc....it works. Here's the code that works:

'When form is first opened, adjusts it to suit user
'Last update 10/07/00
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    Dim UsersName
    
    UsersName = GetCurrentUserName()
 
 
    If Forms!Switchboard!secLevel = 5 Then
       
    ElseIf Forms!Switchboard!secLevel = 4 Then
        DoCmd.ApplyFilter , "OrdSecure = False Or OrdEnteredBy = '" & UsersName & "'"
    ElseIf Forms!Switchboard!secLevel = 3 Then
        DoCmd.ApplyFilter , "OrdEnteredBy = '" & UsersName & "'"
    Else
        DoCmd.Close
    End If
    DoCmd.Maximize

Exit_Form_Open:
    Exit Sub
Err_Form_Open:
    MsgBox Err.Description
    Resume Exit_Form_Open


End Sub

 

[WayneRyan]

J,

That's Access!

Had you done a Compact/Repair?

Had you compiled the code?

Oh well ...

Wayne

 

[Jonathan Kok]

Actually, at one point, it did do a compact/repair as a result of an outright crash. It crashed shortly after that again, so I automatically discounted it as having solved the problem. Perhaps it did, though. Meh, well, whatever. So long as it works!