Input Box Vs. Different Form for passwords

[pbrown2]

When initally setup the databases' forms used input boxes for users to enter passwords to navigate from form to form. However, now a separate table has been set up to house the passwords.
Here is the problem and oh boy, do I hope someone can help!
10 different areas. Each area has an individual password.
I.E.
Area Password
Area 1 = TN
Area 2 = GA
and so on....

However, in an attempt to make it easier for the execs to navigate and not need to remember all the different passwords, another Password field has been made with its password. I.E.
Area Password Exec Password
Area 1 TN USA
Area 2 GA USA

Now, currently the code reads (this is for the "Area 1" button):
Private Sub Area1_Edit_Click()
Dim isgm As String
isgm = InputBox("Enter Area1 Password&quot
If (Len(isgm) > 0) Then
If UCase(isgm) = "TN" Or UCase(isgm) = "USA" Then
DoCmd.OpenForm "Area1", , , , , , ""
ElseIf UCase(isgm) <> &quot;TN&quot; Or UCase(isgm) <> &quot;USA&quot; Then
MsgBox (&quot;Incorrect Password for Tennessee&quot
DoCmd.OpenForm &quot;Switchboard&quot;, , , , , , &quot;&quot;
End If
End Sub

By using the inputbox I am able to specifically tell the code what I am looking for. However, is there away to reference the password table instead of
If UCase(isgm) = &quot;TN&quot; or UCase......
so that if the password changes the code would not need to be modified. The item stumping me is how to tell the code or form that for this button I am looking for the password(s) related to Area1 only.

I hope I am not over complicating an issue......



Thank you for any and all help,

PBrown

 

[hymn]

I have a table called Pass a field in that table called pass with this code below

'password..
MaintPass = Pass
MasterPass = &quot;USA&quot;
Static Tries As Integer

'check password to allow entry to system
Attempt = [Password]

If (Attempt) = MaintPass Or Attempt = MasterPass Then
Tries = 0 're-set number of tries to 0 for next time user uses the form
DoCmd.OpenForm &quot;logo&quot;
DoCmd.Close acForm, &quot;frmPass&quot;
Exit Sub
Else
Tries = Tries + 1 'count number of tries

If Tries = 3 Then 'if 3 failed attempts then disable the button
MsgBox &quot;You have had the maximum 3 tries and failed to enter the correct password. You will not be granted access to the Maintenance screens&quot;, 48, &quot;Entry denied&quot;
DoCmd.Close 'This throws you out of the database and the following 2 lines are not required

Else
MsgBox &quot;Incorrect password. You have &quot; & (3 - Tries) & &quot; more tries&quot;, 1, &quot;Incorrect Password&quot;
End If
End If

Hope this helps
Hymn

 

[GTLoco99]

Sure there is. Try this

Private Sub Area1_Edit_Click()
Dim isgm As String

Dim strSQL as String
Dim db as Database
Dim rs as Recordset

strSQL = &quot;SELECT Password.* FROM Password WHERE Password.Area Like &quot;Area1&quot;;&quot;

Set db = CurrentDb()

Set rs = db.OpenRecordSetstrSQL, dbOpenSnapshot

If rs.EOF Then
MsgBox &quot;There are no password for this Form, contact Administrator&quot;
GoTo Exit_Sub_Link
End If

isgm = InputBox(&quot;Enter Area1 Password&quot
If (Len(isgm) > 0) Then

If UCase(isgm)= rs.Fields(&quot;PasswordA&quot Or UCase(isgm) = rs.Fields(&quot;PasswordB&quot Then
DoCmd.OpenForm &quot;Area1&quot;, , , , , , &quot;&quot;
Else
MsgBox (&quot;Incorrect Password for Tennessee&quot
DoCmd.OpenForm &quot;Switchboard&quot;, , , , , , &quot;&quot;
End If
End If

Exit_Sub_Link:

End Sub
___________________________

Hope this helps

GTLoco

 

[pbrown2]

In the process of attempting GTLoco's idea, but recieved &quot;Syntax Error&quot; at:
Set rs = db.OpenRecordSetstrSQL, dbOpenSnapshot

Currently using Access 97, if it helps.

Thank you for any and all help,

PBrown

 

[GTLoco99]

Make sure under references you have Microsofts DAO Library checked

Thanks
GTLoco

 

[pbrown2]

It is checked.... Microsoft DA0 3.51 Object Library

It says &quot;Expected End of statement&quot; and goes to the &quot;,&quot; at Set rs = db.OpenRecordSetstrSQL, dbOpenSnapshot



Thank you for any and all help,

PBrown

 

[pbrown2]

There is only one thing wrong with the below code....

If the user uses the password in [ExecPassword] the form opens, however, if they use the password in [Password] the it gives the message as being an incorrect password

Private Sub Aera1_Edit_Click()
Dim isgm As String
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
strSQL = &quot;SELECT Password.* FROM Plant WHERE Password.Plant Like &quot;&quot;Area1 CST&quot;&quot;;&quot;
Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Plant&quot;, dbOpenDynaset)
If rs.EOF Then
MsgBox &quot;There are no password for this Form, contact Administrator&quot;
GoTo Exit_Sub_Link
End If
isgm = InputBox(&quot;Enter Area1 Password&quot
If (Len(isgm) > 0) Then
If UCase(isgm) = rs.Fields(&quot;Password&quot Or UCase(isgm) = rs.Fields(&quot;ExecPassword&quot Then
DoCmd.OpenForm &quot;Area1&quot;, , , , , , &quot;&quot;
Else
MsgBox (&quot;Incorrect Password for Tennessee&quot
DoCmd.OpenForm &quot;Switchboard&quot;, , , , , , &quot;&quot;
End If
End If
Exit_Sub_Link:


Any suggestions?

Thank you for any and all help,

PBrown

 

[GTLoco99]

Ok for the problem with this statement db.OpenRecordSetstrSQL, dbOpenSnapshot you need a space added. It should look like this, I may have typo'd it. db.OpenRecordSet strSQL, dbOpenSnapshot.

As for your problem, you may try getting rid of your &quot;Or&quot; and create two If statements.

Code:
____________________

If (Len(isgm) > 0) Then
If UCase(isgm) = rs.Fields(&quot;Password Then
DoCmd.OpenForm &quot;Area1&quot;, , , , , , &quot;&quot;
End If

If &quot Or UCase(isgm) = rs.Fields(&quot;ExecPassword&quot Then
DoCmd.OpenForm &quot;Area1&quot;, , , , , , &quot;&quot;
End If
Else
MsgBox (&quot;Incorrect Password for Tennessee&quot
DoCmd.OpenForm &quot;Switchboard&quot;, , , , , , &quot;&quot;

End If
______________________

That should do it. If the first statement is false, it will move onto the next one.

Good Luck
GTLoco

 

[pbrown2]

This solved the problem:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

All is working as needed! Thank you! This will save sooo much time in the future!


Thank you,

PBrown