VBA Error on AllowByPassKey

[JoeJr]

Hello All,

I am running in to an issue when running my database at the login screen. Currently I have code set to turn on allow the ByPassKey if the usertype is 1 which is the Administrator. Any number above 1 throws a run time error '3420' Object invalid or no longer set. the error is stopping on a section of code: (CurrentDb.Proerties.Append prop) when I hover over Append prop it says that prop= Nothing I cant figure it out.



Here is the section of code that I have

 TempVars("UserType") = rs!UserType_ID.Value
    
    If rs!UserType_ID = 1 Then
        Dim prop As Property
        On Error GoTo SetProperty
        Set prop = CurrentDb.CreateProperty("AllowByPassKey", dbBoolean, False)
    End If
    
    CurrentDb.Properties.Append prop 'THIS IS THE SECTION THAT THE CODE STOPS ON'
    
SetProperty:
    If MsgBox("Would you like to turn on the bypass Key?", vbYesNo, "Allow Bypass") = vbYes Then
        CurrentDb.Properties("AllowBypassKey") = True
    Else
        CurrentDb.Properties("AllowBypassKey") = False
    End If


End Sub

[Micron]

Your directive to go to SetProperty is within the If block where you see if the value = 1. If it's not 1, you are still trying to use the property because that statement is going to run regardless - it is outside of the IF block thus the property is not SET. Did you try stepping through this code? I would think you'd easily find the problem if you did.

[JoeJr]

Your directive to go to SetProperty is within the If block where you see if the value = 1. If it's not 1, you are still trying to use the property because that statement is going to run regardless - it is outside of the IF block thus the property is not SET. Did you try stepping through this code? I would think you'd easily find the problem if you did.

Thank you for the response. I am still very new to VBA and access so I am still learning. I took that line out and now if a user with a userType_ID of 2 or higher, the bypass key still turns on with the message which is should not. Are you saying that the line of code should be inside the if statement?

Thank you

[Micron]

To be sure I understand the goal, if user has level of 1, the bypass property should be set to True. If level is any other number, it should be set to False? Also, is there a chance that the level value for a user could be Null (maybe they can get this far without their identity being in the table/recordset)?

Or if you're in a rush, the simple answer is yes. Follow your code logic - if not just by reading it, by stepping through it as already suggested. It is a good way to learn what really happens versus what you expect to happen, especially if you test with variations of the conditions you expect.

The reason for my initial question is because typically you try to set the property first and if you get an error (because it doesn't exist) then you branch off into your error handler, create it and branch back to the line where the error occurred - then you set it. Once it has been created and set, you typically don't do this over and over again, which is what you are doing if the level is 1.

Post back if you want to do it that way and get stuck. I'm certain you will find many examples that are coded as I described if you search the subject.

[JoeJr]

To be sure I understand the goal, if user has level of 1, the bypass property should be set to True. If level is any other number, it should be set to False? Also, is there a chance that the level value for a user could be Null (maybe they can get this far without their identity being in the table/recordset)?

Or if you're in a rush, the simple answer is yes. Follow your code logic - if not just by reading it, by stepping through it as already suggested. It is a good way to learn what really happens versus what you expect to happen, especially if you test with variations of the conditions you expect.

The reason for my initial question is because typically you try to set the property first and if you get an error (because it doesn't exist) then you branch off into your error handler, create it and branch back to the line where the error occurred - then you set it. Once it has been created and set, you typically don't do this over and over again, which is what you are doing if the level is 1.

Post back if you want to do it that way and get stuck. I'm certain you will find many examples that are coded as I described if you search the subject.

Thank you again for the information and support. Yes, only admin users will have level 1. The value of the user can never be Null. The admin create the users and on that section of the DB i have it set so that the level has to have a value in order for the user to be created. I think since I am a novice at VBA and I know what I want it to do is making it hard for me lol.

[JoeJr]

I am still having trouble trying to figure this out. I have a tempVars that grabs the UserType ID. Then it moves to the next line of code. If that user ID is 1 then the ask the user if they want to allow the bypass key. If the user ID is NOT 1 then it shouldn't display or ask the user about the bypass key but it still is. I have removed

 CurrentDb.Properties.Append

from the code. This is were I am at now.

   TempVars("UserType") = rs!UserType_ID.Value
        
    
    If rs!UserType_ID = 1 Then
        Dim prop As Property
        Set prop = CurrentDb.CreateProperty("AllowByPassKey", dbBoolean, False)
    End If
    On Error GoTo SetProperty
SetProperty:
    If MsgBox("Would you like to turn on the bypass Key?", vbYesNo, "Allow Bypass") = vbYes Then
        CurrentDb.Properties("AllowBypassKey") = True
    Else
        CurrentDb.Properties("AllowBypassKey") = False
    End If


    End Sub

[Micron]

it looks to me as if you didn't grasp any of what I said

... typically you try to set the property first and if you get an error (because it doesn't exist) then you branch off into your error handler, create it and branch back to the line where the error occurred - then you set it. Once it has been created and set, you typically don't do this over and over again, which is what you are doing if the level is 1.

You are trying to set a property that may not exist, don't trap the error if it doesn't and don't have a way to create it if it doesn't.
It is pointless to set it to False every time this runs, only to set it False again if the prompt response is no. I don't even see the sense in prompting as the setting has no effect for the current session. You have to restart for this to be of any use, so if bypass is needed, it ought to have been deemed necessary before user opens the db thus the question is moot if it has a No option. Not only that, if they restart you're going to prompt them again? It will appear as though it is still not turned on.

IMHO you ought to have something that only developers know about, such as double clicking on some form label for example, to switch the setting. I'd make it red if bypass is on, normal text if not. Then they restart to do whatever it is they need to do development-wise. Hopefully users are not intrusive and smart, because this feature doesn't provide a whole lot of protection from those that know.

If you intend to keep the current course, this may be of some help

Dim prop As DAO.Property
On Error GoTo errHandler

Set prop = CurrentDb.Properties("AllowByPassKey")

If rs!UserType_ID = 1 Then
  If MsgBox("Would you like to turn on the bypass Key?", vbYesNo, "Allow Bypass") = vbYes Then
    prop("AllowBypassKey") = True
  Else
    prop("AllowByPassKey") = False
End If

exitHere:
Set prop = Nothing

errHandler:
If Err.Number = 3270 Then 'bypass property doesn't exist so create it
  Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)
  db.Properties.Append prop
  Resume
End If
MsgBox Err.Number & ": " & Err.Description
Resume exitHere

EDIT - forgot to mention that I did test this code for bugs but not to see if it turned the property on/off in the test db.