UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Password When Bypass Key Used, Access 2007    
 
   
cpaulk1
post Apr 17 2017, 09:36 AM
Post#1



Posts: 22
Joined: 17-May 16



I'm trying to find a way to check to see if the Shift key is depressed when the database opens, then prompting for a password if it is. In this way, I can set AllowBypassKey to "False" and only allow users to see Design View and open certain forms when they enter the correct password. I would prefer to have a login screen to change certain options when an Admin username and password are entered so they can edit parts of the front-end, then reopen the database without the custom ribbon and reset the options for the next time the database is opened. About a year ago, I had already programmed this into the database with usernames, passwords, and a user-level security system; however, the person above me decided she doesn't want users to have to login to get into the database every time they open it.

At this point, I'm really just trying to get the InputBox to work.

So, here's part of my code for the Form_Load subroutine:

CODE
Private Sub Form_Load(KeyCode As Integer, Shift As Integer)

   Dim dB As Database
   Dim Rst As Recordset
   Dim pWrd As String
  
   If (Shift And SHIFT_MASK) Then
      pWrd = InputBox(" Enter password to continue: ", " GPI Bypass ")
      If pWrd <> "mypass" Then
         MsgBox (" Invalid Password "), , " Access Denied "
         DoCmd.Quit
      End If
   End If

<Some unrelated code...>

End Sub


When this runs, I get an error saying that "Procedure declaration does not match description of event or procedure having the same name." I believe my problem is that Shift and KeyCode cannot be used in any procedure aside from KeyDown, KeyUp, or KeyPress events, but I'm not sure about that.

Is there any possible way to make something like this work?
This post has been edited by cpaulk1: Apr 17 2017, 09:42 AM
Go to the top of the page
 
theDBguy
post Apr 17 2017, 09:44 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,847
Joined: 19-June 07
From: SunnySandyEggo


Hi,

The funny thing about the Shift Bypass key is it's supposed to bypass any code from running. So, even if you manage to write a code without an error, there is no way to run it if the user presses the shift key at startup.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
cpaulk1
post Apr 17 2017, 09:50 AM
Post#3



Posts: 22
Joined: 17-May 16



Sorry - I wasn't very clear. I am working on a copy of the front-end that already has AllowBypassKey set to "False". I failed to mention that.
Go to the top of the page
 
theDBguy
post Apr 17 2017, 11:14 AM
Post#4


Access Wiki and Forums Moderator
Posts: 70,847
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Perhaps you misunderstood what I meant. If a user holds down the Shift key while opening the database, then all code will not be executed. So, there is no way, using code, to "check" if the user opened the database while holding down the Shift key. At least nothing I am aware at this point.

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 17 2017, 11:23 AM
Post#5


UA Admin
Posts: 30,734
Joined: 20-June 02
From: Newcastle, WA


I concur with what theDBguy is trying to explain.

You can't check for the Shift ByPass key press because it explicitly disables ALL code and macros. Nothing runs.

--------------------
Go to the top of the page
 
ADezii
post Apr 17 2017, 01:31 PM
Post#6



Posts: 1,811
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I am working on a copy of the front-end that already has AllowByPassKey set to "False".

  1. Given this statement, you can check and see if a User has pressed the SHIFT Key prior to opening your Database but I see no point in this Logic since this functionality is not enabled. In any event, this is how it can be done:
  2. In a Standard Code Module, Copy-n-Paste the following API Declaration:
    CODE
    Public Declare Function GetKeyState Lib "User32" (ByVal nVirtKey As Long) As Integer
  3. In the Load() Event of your StartUp Form, Copy-n-Paste the following Code:
    CODE
    Private Sub Form_Load()
    If GetKeyState(&H10) < 0 Then
      MsgBox "SHIFT Key was pressed!"
    Else
      MsgBox "SHIFT Key NOT pressed!"
    End If
    End Sub
  4. The rest should be self-explanatory.
Go to the top of the page
 
ScottGem
post Apr 17 2017, 01:33 PM
Post#7


UtterAccess VIP / UA Clown
Posts: 32,164
Joined: 21-January 04
From: LI, NY


You might try this utility in our Archives: http://www.UtterAccess.com/forum/index.php...p;hl=bypass+key. you may be able to modify it to allow a password to disable the key.


--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
theDBguy
post Apr 17 2017, 02:27 PM
Post#8


Access Wiki and Forums Moderator
Posts: 70,847
Joined: 19-June 07
From: SunnySandyEggo


Hmm, it seems I may have misread the question. Good luck.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
cpaulk1
post Apr 19 2017, 02:24 PM
Post#9



Posts: 22
Joined: 17-May 16



Thank you very much for your input. I finally solved the issue and will share what I did so other people can adapt the process for their own uses and, hopefully, make it better.

This is great for front-ends that need to keep users out of Design View without requiring them to log in or enter a password every time.

First, I copied ADezii's GetKeyState function posted above into a module:

CODE
Public Declare Function GetKeyState Lib "User32" (ByVal nVirtKey As Long) As Integer


Next, I made this sub to create a batch file for the purpose of restarting Access:

CODE
Public Sub DBRestart()
  
   Dim dbPath As String
   Dim lockDBPath As String
   Dim batFile As String
   Dim fNum As Byte
   Dim dB As Database
   Dim Rst As Recordset
  
   dbPath = CurrentProject.FullName
   lockDBPath = Replace(Dir([dbPath]), "accdb", "laccdb")
   batFile = CurrentProject.Path & "\" & "dbRestart.bat"
  
   fNum = FreeFile()
  
   Open batFile For Output As #fNum
   Print #fNum, "@echo off"
   Print #fNum, "title DBRestart"
   Print #fNum, "mode 50"
   Print #fNum, ""
   Print #fNum, ":FILECHECK"
   Print #fNum, "PING 192.0.2.1 -n 1 -w 1 >NUL"
   Print #fNum, "IF EXIST " & Chr(34) & lockDBPath & Chr(34) & " ("
   Print #fNum, "GOTO FILECHECK"
   Print #fNum, ") ELSE ("
   Print #fNum, Chr(34) & dbPath & Chr(34)
   Print #fNum, ")"
   Print #fNum, "EXIT"
   Close #fNum
  
   Shell batFile, vbHide

End Sub


After this, I created a table ("tStartupSettings") with a Boolean value ("EditMode") to store whether a proper password had been entered (which will be checked in the next step).

Finally, I added the following into the Load event of my frmMenu form (which I defined under "Access Options" as the form to display upon loading the database):

CODE
Private Sub Form_Load()
   On Error Resume Next
  
   Dim dB As Database
   Dim Rst As Recordset
   Dim pWrd As String
   Dim batFile As String
   Dim cmdClose As String
  
   Set dB = CurrentDb
   Set Rst = dB.OpenRecordset("tStartupSettings", dbOpenDynaset)
   Rst.MoveFirst
  
   If Rst!EditMode = True Then
      HideMenu (False)
      SetStrProp "CustomRibbonID", "HideTheRibbon"
      Rst.Edit
      Rst!EditMode = False
      Rst.Update
   Else
      HideMenu (True)
      Me.ShortcutMenu = False 'Disable right-click
   End If

   Rst.Close
   Set Rst = Nothing

   batFile = CurrentProject.Path & "\" & "dbRestart.bat"
   cmdClose = CreateObject("WScript.Shell").Run("taskkill /f /im cmd.exe", 0, True)
   DoEvents
   Kill batFile
   SetStartupProperties
  
   If GetKeyState(&H10) < 0 Then
      Me.Visible = False
      
PassEnter:
      pWrd = InputBox(" Enter password to enter edit mode: ", " Edit Mode Password ")
      
      If pWrd <> "myPass" Then
         MsgBox " Invalid Password " & vbCrLf & vbCrLf & " Click 'OK' to restart the database. ", , " Edit Mode Password "
        
         Call DBRestart
        
         DoCmd.Quit
        
      Else
         UnSetStartupProperties
         Set Rst = dB.OpenRecordset("tStartupSettings", dbOpenDynaset)
         With Rst
            .MoveFirst
            .Edit
            !EditMode = True
            .Update
         End With
        
         Call DBRestart
        
         HideMenu (False)
         SetStrProp "CustomRibbonID", "AccessRibbon"
         DoCmd.Quit
      End If
   End If

   Me.Caption = "Menu"
        
   Set Rst = dB.OpenRecordset("LocalVersion", dbOpenForwardOnly)
  
   Me.menuVersion.Caption = "Version " & Rst!Version
  
   Rst.Close
   dB.Close
   Set Rst = Nothing
   Set dB = Nothing
  
   FileCheck
   VersionCheck
  
End Sub


I hope this code is easy to understand because several functions & subs I haven't defined in this post are called in this part of the code and the code won't run properly without them. If anyone wants an explanation about something, please let me know. thumbup.gif
This post has been edited by cpaulk1: Apr 19 2017, 02:30 PM
Go to the top of the page
 
ScottGem
post Apr 19 2017, 02:44 PM
Post#10


UtterAccess VIP / UA Clown
Posts: 32,164
Joined: 21-January 04
From: LI, NY


QUOTE
This is great for front-ends that need to keep users out of Design View without requiring them to log in or enter a password every time.


Simply deploy the app as an accde and you don't have to worry about them getting into Design Mode.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
GroverParkGeorge
post Apr 19 2017, 02:47 PM
Post#11


UA Admin
Posts: 30,734
Joined: 20-June 02
From: Newcastle, WA


If possible, give users only the Access runtime. That, along with an accde, effectively shuts them out of design entirely regardless of what they do or don't do.


--------------------
Go to the top of the page
 
JonSmith
post Apr 19 2017, 03:00 PM
Post#12



Posts: 3,105
Joined: 19-October 10



Was gonna say the same thing, this shift thing is overcomplicated. Just compile your database and no design changes can be made.
Go to the top of the page
 
cpaulk1
post Apr 20 2017, 07:46 AM
Post#13



Posts: 22
Joined: 17-May 16



I've considered the Runtime and ACCDE option. I may add that when development cools down and I can expand the installation process to add the front-end to Access' Trusted Sources through the registry; but I don't think this was a waste of time at all. If we end up expanding the database the way I believe we will, we'll do a lot of new development to the front-end where I can use what I've learned here. This was a great learning experience. thumbup.gif
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 08:04 AM
Post#14


UA Admin
Posts: 30,734
Joined: 20-June 02
From: Newcastle, WA


I always like to have the last word, so here goes.

At one time, when I was in the process of climbing the learning curve as a newer Access developer, I liked to write lots of clever code to do things that seemed cool. Over time, though, I began to realize that there is little valor in writing wads of code that only do things which Access would otherwise allow me to do through one of its native functions. Sure, there are times when it's important to streamline processes by writing UDFs, classes and reusable code, but quite often, the "Access way" is the easiest, quickest and lowest hassle way, even though it might seem kind of simple to some who LOVE, LOVE, LOVE code solutions to every problem.

That's why this discussion leaves me a bit unsettled. There's a really easy to implement, low-code (actually no-code) way to prevent people from tampering with forms, reports and VBA: convert the accdb to an accde. Done.

It may be that I'm just too lazy to figure out a strategy that attempts to achieve the same non-intervention end game using code to check for the use of the Shift-Key and all of the rest. Or maybe I've just come to the place in life where I don't have to impress myself with such code.

I refer to it as "code wadding" ( as in writing wads and wads of complex code to accomplish something relatively simple ) and I try not to go down that path any more than I have to.

Okay, that's my last word, but it doesn't have to be the last word in this discussion.

Continued success with your project.

--------------------
Go to the top of the page
 
ScottGem
post Apr 20 2017, 08:15 AM
Post#15


UtterAccess VIP / UA Clown
Posts: 32,164
Joined: 21-January 04
From: LI, NY


I don't know whether it was a waste of time or not. But I totally agree with George's point here. One of the beauties of Access is that it can do so much natively. And I have to agree that throwing code at a solution unnecessarily is just not a good practice. Compiling to an accde is the first, and often the last, step needed to protect the application design.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
cpaulk1
post Apr 20 2017, 08:16 AM
Post#16



Posts: 22
Joined: 17-May 16



Good word of wisdom - I'll keep it in mind. thanks.gif
Go to the top of the page
 
jwild
post Apr 20 2017, 08:45 AM
Post#17


UtterAccess VIP
Posts: 844
Joined: 28-September 05
From: Ontario, Canada


I must agree. cpaulk1 you mention adding runtime/accde when 'development cools down' and that you're doing a lot of new development to the frontend.

Deploying the runtime and an accde doesn't hamper development at all. You keep the development copy in accdb format; just deploy the accde to the users.

--------------------
Joan Wild
Microsoft Access MVP
Go to the top of the page
 
cpaulk1
post Apr 20 2017, 09:41 AM
Post#18



Posts: 22
Joined: 17-May 16



QUOTE
Deploying the runtime and an accde doesn't hamper development at all. You keep the development copy in accdb format; just deploy the accde to the users.

I don't have experience with ACCDE files and thought they might get in the way; but that sounds pretty good to me. I may begin implementing that starting with the next update.
Go to the top of the page
 
ScottGem
post Apr 20 2017, 10:24 AM
Post#19


UtterAccess VIP / UA Clown
Posts: 32,164
Joined: 21-January 04
From: LI, NY


The accde is for deployment. You maintain a copy (or several) of the accdb as your development copy. Changes are made to the accdb and then compiled into an accde for deployment.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
JonSmith
post Apr 20 2017, 10:50 AM
Post#20



Posts: 3,105
Joined: 19-October 10



Ok, if you dont have accde experience you cant make an informed choice to go with your way tongue.gif

Lots of seasoned people hear seeing issues with your approach so its worth exploring the advice.

Accde is so easy, you must already have a separate Dev copy of your accdb so just save that as a accde when you want to deploy.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2017 - 01:33 AM