finding a record in a table 1

[Travis33]

I have a database where a user inputs a password and I check the table record to see if it is there to grant them access to the forms. This is the code I am using but it only finds the first record in the table. I want it to search the whole column.
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("users", dbOpenTable)

Key = rs.Fields("password&quot.Value
Password = InputBox("Enter your password&quot
If Password = Key Then
MsgBox ("this Works&quot
else
msgbox("Wrong Password&quot

Can someone please help me, Thank You Travis

 

[kathryn]

You need to step through every record. Something like this:

dim blnVerified as Boolean
Dim db As Database, rs As Recordset

'to start with, assume that password is not verified
blnVerified=False


Set db = CurrentDb
Set rs = db.OpenRecordset("users", dbOpenTable)

Password = InputBox("Enter your password&quot

'move to first record
rs.movefirst

'do the following until the End of File (EOF)
do until rs.eof

If Password = rs.Fields("password&quot.Value then
blnVerified=True
'exit do loop, user has been verified
exit do
else
rs.movenext
end if
loop

'here use an If statement with blnVerified to do whatever
'you want, depending on if they are verified or not.

There is another way to do this, by running a query with the password used as a parameter to the query. If the query returns a record, then the password exists.

Hope that helps.

Kathryn

 

[THoey]

Kathryn,

Maybe I am reading too much into this, but will they have logins and passwords? The second way you list requires all passwords to be distinct. If the users create their own, although probably rare, a record could come back for someone else's login.

I like your first example better...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[kathryn]

Terry,

You are correct. Since Travis only mentioned password, I went with that. If he wants distinct logins and passwords, the code will have to check both, or he can use the CurrentUser function if he has security in his database to get the current users name. If he has no security, he can get the Windows login using an API.

So Travis, do you really just want to check a password? If you have assigned a password to an object and only users know it, then maybe you really do.



Kathryn

 

[Travis33]

Thank You all for your Help,

At this time I am only checking for passwords. I will be checking usernames too but for now I am seeing if it will work. I have a Table called "Users" In that Table I have
recordset for password,username,forms,reports,admin. The last three are marked as yes or no. This is the full code that I have written when a user puts it a password and clicks the enter button. I can now check all the passwords in my table and it will let me in but whatever value is marked in the first record for Forms,reports,admin is what ever user has the rights too( hOw do I fix this problem)Private Sub Command10_Click()
Dim db As Database, rs As Recordset, found As Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("users", dbOpenTable)
found = False
Password = InputBox("Enter your password&quot
var1 = rs.Fields("forms&quot
var2 = rs.Fields("reports&quot
var3 = rs.Fields("admin&quot
var4 = rs.Fields("form1&quot

Do While Not rs.EOF
Key = rs!Password
If Password = Key Then
found = True
Exit Do
End If
rs.MoveNext
Loop



If found = True Then
MsgBox ("this Works&quot
If var1 = True Then
Me.cmdforms.Enabled = True
Else
Me.cmdforms.Enabled = False
End If
If var2 = True Then
Me.cmdreports.Enabled = True
Else
Me.cmdreports.Enabled = False
End If
If var3 = True Then
Me.cmdadmin.Enabled = True
Else
Me.cmdadmin.Enabled = False
End If
Else
MsgBox ("Wrong Password&quot
Me.cmdreports.Enabled = False
Me.cmdforms.Enabled = False
Me.cmdadmin.Enabled = False
End If


End Sub


Thanks again

 

[kathryn]

I'm sorry, but you are still doing the same thing, checking only the first record. Let me explain:

Set rs = db.OpenRecordset("users", dbOpenTable)
found = False
Password = InputBox("Enter your password&quot
var1 = rs.Fields("forms&quot
var2 = rs.Fields("reports&quot
var3 = rs.Fields("admin&quot
var4 = rs.Fields("form1&quot

You are setting the "var" variables to the values in the FIRST record. You have just opened the recordset, so rs.fields("forms&quot is equal to the value of the forms field of the first record. By the way, you can use rs!forms instead of rs.Fields("forms&quot.

Instead, try this.

Private Sub Command10_Click()
Dim db As Database, rs As Recordset, found As Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("users", dbOpenTable)
found = False
Password = InputBox("Enter your password&quot
Do While Not rs.EOF
Key = rs!Password
If Password = Key Then
var1 = rs.Fields("forms&quot
var2 = rs.Fields("reports&quot
var3 = rs.Fields("admin&quot
var4 = rs.Fields("form1&quot
found = True
Exit Do
End If
rs.MoveNext
Loop

Now you are setting the "var" variables at the record which has the correct password.

Does that make sense?







Kathryn