Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Do...While Loop

Posted on 2011-05-09
5
Medium Priority
?
316 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 500 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 500 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

618 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