now code is write to query instread of comparing

teiben · Nov 27, 2004

I have 2 forms and 1 query. The sequence of events is frmLogin check password in queryA, if it matches it opens frmDataEntry. Somehow it works! But now I see that the passwords are storing verse comparing.

This is the code I have on the command button, of FormLogin

Dim stDocName As String

stDocName = "tblUsers Query"
'DoCmd.OpenQuery stDocName, acNormal, acReadOnly
DoCmd.OpenForm "frmDataEntry"
DoCmd.OpenQuery "tblUsers Query", acViewNormal, acReadOnly

Pat Hartman · Nov 27, 2004

I don't see any password check in the code you posted so I think the code does not do what you think it is doing. Are you running an update query by any chance? Post the SQL for [tblUsers Query].

FYI - using embedded spaces or special characters in field or object names is poor practice.

teiben · Nov 27, 2004

the sql for the tblUsersQuery is as follows:

SELECT tblUsers.strTPassword, tblUsers.strUsrLogin, tblUsers.lngUsrType, tblUsers.strUsrPsswd, tblUsers.lngUsrId, tblUsers.strUsrTitle, tblUsers.strUsrFrstName, tblUsers.strUsrMdlInit, tblUsers.strUsrLstName, tblUsers.strUsrPos, tblUsers.strUsrEmail, tblUsers.ysnUsrActive, tblUsers.EmployeeID, tblUsers.Department
FROM tblUsers
WHERE (((tblUsers.strTPassword)=[Forms]![frmReLogin]![strTpassword]));

It does seem to be validating the password.

Pat Hartman · Nov 27, 2004

Your code isn't validating the password. It is retrieving a record if the password matches but your code isn't checking to see if a record was found so there is no difference between finding a record and not finding one. The query also doesn't verify that the password is valid for the given userID. Besides, you are opening the second form before you even run the query. Just because something doesn't abend or raise an error, doesn't mean that it works.

The easiest way to validate a userID and password is with a DLookup() -

Code:

If IsNull(DLookup("strTPassword", "tblUsers", "strUsrLogin = '" & Me.strUsrLogin & "' AND strTPassword = '" & Me.strTPassword & "'")) Then
    Msgbox "invalid password or userID", vbOkOnly
Else
    DoCmd.OpenForm "frmDataEntry"
End If

Notice that this code checks BOTH the userID (which should be unique) and the password (which may not be unique) as a SET. Not only does each need to appear in the file, they need to be found IN THE SAME RECORD! The code checks the result of the DLookup() for null. If the result is null, no matching record was found and so the second form should not be opened. If the result is not null, the userID/Password SET are valid.

teiben · Nov 28, 2004

Thanks. I pasted the code in and it doesn't validate the password. **I started clean and removed all other code I had it there. PLEASE review, I obviously did something wrong

Pat Hartman · Nov 28, 2004

In my earlier post I had guessed at the names based on the query but those were not the names of the unbound control fields. So the DLookup() needs to use the correct names or it won't work -

Code:

Private Sub cmdLogOk_Click()
If IsNull(DLookup("strTPassword", "tblUsers", "lngUsrId = " & Me.cboLogin & " AND strTPassword = '" & Me.txtPsswd & "'")) Then
    MsgBox "invalid password or userID", vbOKOnly
Else
    DoCmd.OpenForm "frmDataEntry"
End If
End Sub

Having two versions of the userID and password in the table is simply confusing and the text versions should be removed. Also, I really don't like your naming style. Conventionally, column names are not prefixed with their data type. Only variables are prefixed this way.

You have the two tables joined lngUsrId-EmployeeId. If lngUsrId is the employee ID, CALL IT THAT. Don't make up a new name. You also have employeeID defined in the Users table. It is redundant and also not populated.

Having a combo to select from is ok if you're not really worried about security. If you need better security, make the user type in his ID. The combo just makes it too easy to get a list of valid users. In my apps, I usually set the default userID to the user's network ID. That saves typing when they're using their own PC to log on. There are lots of posts here if you need the code to get the network ID.

teiben · Nov 28, 2004

still not picking the correct user information

Ok, I've eliminiated the strTPassword and the employid, as you were correct they weren't doing anything. Yes Pat, I've concern w/security, but I've been handed a project that I really don't have skills to accomplish, so I'm doing the best I can. Anyway, no matter which user I select from the combo box, the first person get opened. Can you Please, please take a look.

Pat Hartman · Nov 28, 2004

You need to tell the form which record to open to. Change the OpenForm to:

DoCmd.OpenForm "frmDataEntry", , , "lngUsrId = " & Me.cboLogin

We have now run into the problem caused by using captions in your table definitions. Because you have used a caption -[User ID] for lngUsrID, that is the name that shows up when you view the query. However substituting [User ID] for lngUsrID will not work. The only thing that does work, is going back to the table definition and removing the caption. This is probably a bug but it has been present in every version of Access that I have used including 2003.

Professionals rarely run into this problem because they don't use captions. Captions save you a small amount of work when you first build forms/reports and give nicer column headers when you open tables and queries directly, but they cause insurmountable problems with queries and VBA. Your users should NEVER be opening tables or queries directly so you don't need to worrry about the "unfriendly" names good practice dictates. Only you would be affected. The bottom line - don't use captions for anything.

teiben · Nov 28, 2004

3rd time a charm?

Pat, whom would have thought captions are a problem. I went in a delted them all and the query(s) and recreated (notice no spaces). I put the line of code on the frmDataEntry and I get a compile error: method of data member not found. -->on the me.cbologin.

On the dataentry forms, I reconned the query(s), as I deleted them, so nothing should be hanging around.

Any more ideas? would greatly be appreciated.

Pat Hartman · Nov 28, 2004

It worked for me in your db and I copied and pasted it here. Make sure that you haven't changed any of the control names on the form.

teiben · Nov 29, 2004

could you post

Pat, do you still have a copy of it, you can upload, I can't get it to work.
Thank you in advance

now code is write to query instread of comparing

teiben

Registered User.

Pat Hartman

Super Moderator

teiben

Registered User.

Pat Hartman

Super Moderator

teiben

Registered User.

Pat Hartman

Super Moderator

teiben

Registered User.

Pat Hartman

Super Moderator

teiben

Registered User.

Pat Hartman

Super Moderator

teiben

Registered User.

Similar threads

Users who are viewing this thread