Help in looping through recordset. 1

[pdldavis]

Hi, I would like to check to see if when a user changes his password, that the password does not currently exist in the password table. This is what I have so far:

Dim strPassword As String
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("SELECT tblSecurity.PassWd FROM tblSecurity", dbOpenDynaset)
Set fld = rst("[PassWd]&quot
rst.MoveFirst

If Me.txtNewPass = DLookup("[passwd]", "tblSecurity", "[passwd]&quot Then
MsgBox "This Password is Unavailable. Please Select Another Password"
End If

Do Until rst.EOF
strPassword = fld
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

I don't get any errors or Message Boxes either so something is wrong. I was thinking that this would lookup the first record and compare it to Me.txtNewPass. If it did not equal Me.txtNewPass then it would go on to the next record and so on.

I'm not sure this is the correct way to do this. Any help would be appreciated.

Thanks

 

[scriverb]

Everything looks okay as far as the looping goes but I don't see anywhere where you are trying to make a comparison of the password in the table/recordset to your strPassword. You have Dimensioned it and are making an assignment of the tables field once for each record but you are not doing anything to compare for it.

strPassword = fld

Maybe should be:
[COLOR=blue
If strPssword = fld then
'do something like a message
'end loop
else
'continue loop
end if[COLOR=black]

Hope this helps trigger the logic process that you intended.
Bob Scriver

 

[beetee]

I think there may be something wrong with your DLookup. I've never used it, but a quick check of the help indicates you may want something like this:

if DLookup(&quot;[passwd]&quot;, &quot;tblSecurity&quot;, &quot;[passwd] = '<whatever password the user entered>'&quot <> null then

' it's not a unique password

Somebody please correct me if something is wrong with this.

In any case, as far as I can tell there's no need for the loop or any of the variables/objects associated with the loop.

 

[pdldavis]

ScriverB,

Thanks, that was the problem. I also took out the dlookup and included a With statement. Works fine. Is the With statement redundant for this?

Thanks

Dim rst As DAO.Recordset 'Checks to see if Password is Available for Use - Not in Table
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset(&quot;SELECT tblSecurity.PassWd FROM tblSecurity&quot;, dbOpenDynaset)
Set fld = rst(&quot;[PassWd]&quot
rst.MoveFirst
Do Until rst.EOF

With rst

If Me.txtNewPass = fld Then
MsgBox &quot;This Password is Unavailable. Please Select Another Password&quot;
End If
End With

rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Sub

 

[scriverb]

First of all the With rst just allows you to not include the rst on the front end of any of the recordset commands:
.movefirst
.movenext
.eof
It is your choice. It is a time saving command when entering a lot of code that will refer to rst.

Now I believe that a much shorter version of this checking is possibile for you. Your code above is how to do it with a recordset looping comparison. Below is how to do it with the DLookup function. This code could be put in the OnClick of a command button:

If Not IsNull(DLookup(&quot;[passwd]&quot;, &quot;tblSecurity&quot;, &quot;[Passwd] = '&quot; & me![txtNewPass] & &quot;'&quot) then
MsgBox &quot;This password is already in use. Please Select Another Password&quot;
Me![txtNewPass].setfocus
else
'Password not used so allow to be updated.
end if

I am assuming that you have a way to update the tblSecurity. Let me know if this helps you. Bob Scriver

 

[pdldavis]

The Dlookup was what I originally intended to do but I could not get the syntax correct. Thanks for help in correcting the statement. I'll use that instead.

Thanks,

Dan