Solved

Do...While Loop

Posted on 2011-05-09
5
295 Views
Last Modified: 2012-05-11
Hello Experts,

I'm using the following code found at http://support.microsoft.com/kb/209871 to password protect a form.

Private Sub Form_Open(Cancel As Integer)

   Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   On Error GoTo Error_Handler

       ' Prompt the user for the Password.
       Hold = InputBoxDK("Please Enter Your Password", "Enter Password")
       ' Open the table that contains the password.
       Set db = CurrentDb
       Set rs = db.OpenRecordset("tblKeyCode", dbOpenTable)
       rs.Index = "PrimaryKey"
       rs.Seek "=", Me.Name
       If rs.NoMatch Then
          MsgBox "Sorry cannot find password information. Try Again"
          Cancel = -1
       Else
          ' Test to see if the key generated matches the key in
          ' the table; if there is not a match, stop the form
          ' from opening.
          If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
             MsgBox "Sorry you entered the wrong password." & _
                "Try again.", vbOKOnly, "Incorrect Password"
             Cancel = -1
          End If
       End If
       rs.Close
       db.Close
       Exit Sub
   
Error_Handler:
   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
   Exit Sub
End Sub

Open in new window


The code works but after one failed attempt the form opens.
How can I add a loop to prompt for the password until the right password is entered?  
0
Comment
Question by:hello_everybody
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 35726416
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 125 total points
ID: 35726427
Private Sub Form_Open(Cancel As Integer)

   Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   On Error GoTo Error_Handler

       ' Prompt the user for the Password.
Do
       Hold = InputBoxDK("Please Enter Your Password", "Enter Password")
       ' Open the table that contains the password.
       Set db = CurrentDb
       Set rs = db.OpenRecordset("tblKeyCode", dbOpenTable)
       rs.Index = "PrimaryKey"
       rs.Seek "=", Me.Name
       If rs.NoMatch Then
          MsgBox "Sorry cannot find password information. Try Again"
          Cancel = -1
       Else
          ' Test to see if the key generated matches the key in
          ' the table; if there is not a match, stop the form
          ' from opening.
          If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
             MsgBox "Sorry you entered the wrong password." & _
                "Try again.", vbOKOnly, "Incorrect Password"
             Cancel = -1
      else
Cancel = 1
          End If
       End If
       rs.Close
       db.Close
Loop While Cancel < 0

       Exit Sub
   
Error_Handler:
   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
   Exit Sub
End Sub
0
 
LVL 8

Author Comment

by:hello_everybody
ID: 35727569
pratima_mcs:

It worked like a charm. But if I click cancel it continues to loop through the code, what can I do so clicking Cancel will close the database?
0
 
LVL 1

Assisted Solution

by:Chad-M
Chad-M earned 125 total points
ID: 35737902
Put Exit Sub after Cancel = 1 shown below
Else
          ' Test to see if the key generated matches the key in
          ' the table; if there is not a match, stop the form
          ' from opening.
          If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
             MsgBox "Sorry you entered the wrong password." & _
                "Try again.", vbOKOnly, "Incorrect Password"
             Cancel = -1
      else 
Cancel = 1
Exit Sub
          End If

Open in new window

0
 
LVL 8

Author Comment

by:hello_everybody
ID: 35743987
Chad-M:

Thanks for your suggestion, but it doesn't do a thing, what I did get to work is adding the following line on the last line of the loop

 
If hold = "" Then DoCmd.Quit

Open in new window


this works since the variable "hold" is an empty length string if cancel is clicked.


The only remaining issue is that the msgBox is displayed before the mdb is closed.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question