Case Variable Loses Value Randomly 1

[rmtiptoes]

Help!

I have an application that selects user and the user's security level. It works fine until for no reason it stops and when I stepped through it I found that the Case value was getting set to nothing but the user value reamained which is good. The case variable is global. Do I need to requery? How can I make access retain that value?

CODE
Private Sub Form_Open(Cancel As Integer)
Select Case (strSecLvl)
Case "Developer"
Debug.Print "User"
'Set form properties for Developer
Case "Administrator"
'Set form properties for Administrator
Case "Maintenance"
'Set form properties for Maintenance
Case "Receiving"
'Set form properties for Receiving
DoCmd.Close acForm, "UserLogIn"
Case "Read Only"
'Set form properties for Read Only
Case Else
MsgBox "The user name with which you have logged in " & _
"is not cleared for access to this screen." & vbCrLf & vbCrLf & _
"Please check your security settings.", vbCritical, "Security Error !"
DoCmd.Close acForm, "Assets_New"
End Select

End Sub

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[Eupher]

Have you tried setting a watch on your variable(s) to see when they are changing?

Ken S.

 

[rmtiptoes]

What I mean by random is I can open and close the form(s) several times and it will be okay and then I'll open the form again and it will happen. This occurs when I am workingin the form. I am using a global variable and another form to set the strSeclvl.

Using watcher also shows it changes if when the Access calls another application that prints a label. (Uses an ODCB driver) It ways that the value is 'OutofContext'

stAppName = "C:\Program Files\BARONE 6 Desktop\lppa.exe /F C:\test.lab /P /C=" & stBarcode



Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

How are ya rmtiptoes . . . . .

Definite [blue]scoping problem[/blue] here. Exactly where (what module) contains the global [purple]strSecLvl[/purple]?

If not already, put the global in the [blue]most used module[/blue] in the [blue]modules window.[/blue]

If the form is being opened using DoCmd.OpenForm . . . you can pass the value using [purple]OpenArgs[/purple] (the last arguement). The call would be:

[blue]   DoCmd.OpenForm "FormName", , , , , , "[purple][b]YourTextValue[/b][/purple]"[/blue]

In your code:

[blue]Select Case Me.[purple][b]OpenArgs[/b][/purple][/blue]

See Ya! . . . . . .

 

[rmtiptoes]

Fine. How are ya Aceman1?

I have put the global strSecLvl in the ModGlbVars module which is the most used but only has this variable declared. There is another module in the modules window, but I don't use it much. The rest are forms.

I am not using the DoCmd.OpenForm "FormName" to open the forms. I only open the form from the Switchboard. Do I need to add it or just simply follow the first example???


Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

[blue][purple]I am not using the DoCmd.OpenForm "FormName"[/purple] to open the forms. I only open the form from the Switchboard.[/blue]

No . . . but the particular button on the Switchboard is (using doCmd), and is responsible for setting or running code that sets the global.

Post the code for that button!

[blue]I can open and close the form(s) several times and it will be okay and then I'll open the form again and it will happen. [purple]This occurs when I am workingin the form.[/purple][/blue]

Can you expand on this a little . . .

See Ya! . . . . . .

 

[rmtiptoes]

I decided to refresh with the following public function to refresh the user level security. I cannot seem to pinpoint when it fails to refresh the strSecLvl so now each time a form is opened it will just run the lookup.

' DETERMINE USER SECURITY LEVEL
' (strSecLvl is a global variable set in the modGlbVars module)
If DLookup("[maint]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Maintenance"
End If
If DLookup("[receiving]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Receiving"
End If
If DLookup("[admin]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Administrator"
End If
If DLookup("[dev]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Developer"
End If

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

[blue]I decided to refresh with the following public function . . .[/blue]

Shouldn't be necessary!

I can help you better if you post the code I queried for! . . . unless its propritary . . .

One thing you can do is a global search for [blue]strSecLvl[/blue] from any code window:
[ol][li]Select [blue]Find[/blue] on the menubar.[/li]
[li]Be sure to select [blue]Current Project[/blue] in the find dialog window.[/li][/ol]
The culprit may show up in this way . . .

See Ya! . . . . . .

 

[rmtiptoes]

Here is the code
from the Swithcboard manager. I found the strSecLvl when I did the search. It is in all of the foem in the subfunction and the userlogin form where the strSecLvl is determined.

I also found that the user on the forms is defaulting to the database user and not updating to the current user.


Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

rmtiptoes . . . . .

The code you provided is strictly for the switchboard.

I need the code for the button that opens the form in question in this thread!

BTW . . . during your search did you run across any code like [blue]strSecLvl = ""[/blue] ?

Also I'm sure your aware, if your testing and opening/closing the form, its possible to lose the value of [blue]strSecLvl[/blue] when all forms are closed!

See Ya! . . . . . .

 

[rmtiptoes]

Oh. So sorry for the confusion. I did not run across strSecLvl = "" in the code. Only when I step into the debug. And no, I was not aware that my testing and opening/closing the form would lose the value of strSecLvl when all forms are closed! That is what is happening to the user. While in the application, they can navigate between forms from the swithboard as well as call an application to print a barcode.

Here is the original code to open the form:

Private Sub Form_Open(Cancel As Integer)
Select Case (strSecLvl)
Case "Developer"
'Set form properties for Developer
Case "Administrator"
'Set form properties for Administrator
Case "Maintenance"
'Set form properties for Maintenance
Case "Receiving"
'Set form properties for Receiving
DoCmd.Close acForm, "UserLogIn"
Case "Read Only"
'Set form properties for Read Only
Case Else
MsgBox "The user name with which you have logged in " & _
"is not cleared for access to this screen." & vbCrLf & vbCrLf & _
"Please check your security settings.", vbCritical, "Security Error !"
DoCmd.Close acForm, "Assets_New"
End Select

End Sub

++I was going to change it to the following. Which is not working. I think its because there is a user field that retains the value of the first user who created the record when I need it to update to the current user. In the field properties I have is set to default to current user but I guess I need to put in an Update statement....???

Private Sub Form_Open(Cancel As Integer)
' DETERMINE USER SECURITY LEVEL
' (strSecLvl is a global variable set in the modGlbVars module)
If DLookup("[maint]", "tblUserSecurity", "[userID]='" & Me.user.Value & "'") = -1 Then
strSecLvl = "Maintenance"
End If
If DLookup("[receiving]", "tblUserSecurity", "[userID]='" & Me.user.Value & "'") = -1 Then
strSecLvl = "Receiving"
End If
If DLookup("[admin]", "tblUserSecurity", "[userID]='" & Me.user.Value & "'") = -1 Then
strSecLvl = "Administrator"
End If
If DLookup("[dev]", "tblUserSecurity", "[userID]='" & Me.user.Value & "'") = -1 Then
strSecLvl = "Developer"
End If
Select Case (strSecLvl)
Case "Developer"
'Set form properties for Developer
Case "Administrator"
'Set form properties for Administrator
Case "Maintenance"
'Set form properties for Maintenance
Case "Receiving"
'Set form properties for Receiving
DoCmd.Close acForm, "UserLogIn"
Case "Read Only"
'Set form properties for Read Only
Case Else
MsgBox "The user name with which you have logged in " & _
"is not cleared for access to this screen." & vbCrLf & vbCrLf & _
"Please check your security settings.", vbCritical, "Security Error !"
DoCmd.Close acForm, "Assets_New"
End Select


Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

[blue] . . . I did not run across [purple]strSecLvl = ""[/purple] . . .[/blue]

On your return post (when I asked you to perform a search) you didn't say wether or not you found [blue]strSecLvl[/blue]. This should've been revealing as you stepped thru all locations found. If you havn't done it, you should do so & log all locations.

I was trying to find a critique on [blue]Variable Scope & LifeTime[/blue] in my library, but suffice for you is to say: [purple]All globals (at module level) loose their values when no code is runing and no form is open![/purple]

For you, it may be a simple matter of opening the [blue]Switchboard[/blue] before you close the current form! You should be able to easily test this with one of the forms.

A trick often used here is to [purple]show/hide[/purple] the [blue]Switchboard[/blue] with the forms [purple]Visible[/purple] property instead of opening/closing ([purple]its never closed so you should retain your globals[/purple]). This however requires you to find all instances of opening/closing the [blue]Switchboard[/blue] and replace it with the appropriate Visible Command.

See Ya! . . . . . .

 

[rmtiptoes]

Thank you for your research!!! And I see.

quote: "On your return post (when I asked you to perform a search) you didn't say whether or not you found strSecLvl. This should've been revealing as you stepped thru all locations found. If you havn't done it, you should do so & log all locations."

I have found that the strSeclvl = in the following based upon what I have in the userlogin form.

'DETERMINE USER SECURITY LEVEL
' (strSecLvl is a global variable set in the modGlbVars module)
If DLookup("[maint]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Maintenance"
End If
If DLookup("[receiving]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Receiving"
End If
If DLookup("[admin]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Administrator"
End If
If DLookup("[dev]", "tblUserSecurity", "[userID]='" & Me.User.Value & "'") = -1 Then
strSecLvl = "Developer"
End If

quote: "A trick often used here is to show/hide the Switchboard with the forms Visible property instead of opening/closing (its never closed so you should retain your globals). This however requires you to find all instances of opening/closing the Switchboard and replace it with the appropriate Visible Command"

I am opening and closing forms but the switch board remains opened. Sorry for the confusion.


I think I see the problem. When I open the form and create a record for the first time, I have the property setting for the user textbox default to this:

=DLookUp("userID","[CurrentUser]")

This updates the table with the user who has logged on. If I close the form and go to another and then go to the update forms to update the record, the user data remains static to the user who created the record and any user updating the information appears to not reset the strSecLvl value.

I also believe that the UserLogin form is selecting the correct user and thus the correct strSecLvl, BUT, I see in the watcher where the user changes to 'admin', the default for Access. I think that when I open the form I should run a sequel to update the form's record to the current or logged on user in the update forms, I may be okay. I think.



Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

rmtiptoes . . . . .

Big [blue]error[/blue] in latest posted code!

[blue]DLookUp[/blue] return either a [purple]Null[/purple], or the [purple]value of the field arguement[/purple] if the the record is found (text in your case). Pinging against [blue]-1/True[/blue] is not valid unless your looking-up a numeric field you expect to return [blue]-1/True[/blue].

Try the following in its place:

[blue]   Dim Criteria As String, Fld As String, x As Integer
   
   Criteria = "[userID]='" & Me!User.Value & "'"
   
   For x = 1 To 4
      Fld = Choose(x, "[maint]", "[receiving]", "[admin]", "[dev]")
      
      If Not IsNull(DLookup(Fld, "tblUserSecurity", Criteria)) Then
         strSecLvl = Choose(x, "Maintenance", "Receiving", _
                               "Administrator", "Developer")
         Exit For
      End If
   Next[/blue]

I'm not saying this'll fix the problem (maybe!), but the code is not valid as posted.

Was in full agreement with the synposis of your last post until I saw this. If this doesn't fix the problem, then unless something else shows itself, your probably right.

[blue]Check it out and let me know . . .[/blue]

See Ya! . . . . . .

 

[rmtiptoes]

Hi! Are you still there? This actually came close. I have been adjusting it. The strSecLvl kept changing to maintenance at the same point in the code, then I looked more closely and find that a this point

strSecLvl = Choose(x, "Maintenance", "Receiving", _
"Administrator", "Developer")

the application picks the first item in the list. I will contimue to play with this...

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[TheAceMan1]

[blue]I have been adjusting it. The strSecLvl kept changing to maintenance at the same point in the code . . .[/blue]

My fault here. I didn't follow your priority order of: [purple]Developer, Administrator, Receiving, Maintenance.[/purple]

To fix this just [blue]redistribute priority order in the Choose functions:[/blue]

[blue]   Dim Criteria As String, Fld As String, x As Integer
   
   Criteria = "[userID]='" & Me!User.Value & "'"
   
   For x = 1 To 4
      Fld = Choose(x, "[dev]", "[admin]", "[receiving]", ", "[maint]")
      
      If Not IsNull(DLookup(Fld, "tblUserSecurity", Criteria)) Then
         strSecLvl = Choose(x, "Developer", "Administrator", _
                               "Receiving", "Maintenance")
         Exit For
      End If
   Next[/blue]

See Ya! . . . . . .