User login screen

[Ryker7]

I am trying to develope a database that requires the user to login. I have a login form where the user will enter his id. I have a user table that will have user id's stored. When I click the enter button, I would like access to take the data entered on the form and compare it to the user table to see if there is a user match. If there isn't a match, I would like a message to be displayed saying the user does not exist. I am trying to use the dlookup function to accomplish this, but cannot get it to work right. Any suggestions?

 

[randy700]

Try this....

Private Sub Enter_Click()
If Not IsNull(DLookup("Name", "tblUsers", "Name = '" & me.txtUser & "'") Then
user found.. code to continue goes here
Else
MsgBox "User not found.", vbOkOnly, "Access Denied"
End If
End Sub

Randy

 

[Ryker7]

Thanks. If the user is found, I would like to open another form. What is the code to do this?

 

[randy700]

I'd use something like this....

DoCmd.OpenForm "FormName"
DoCmd.Close acForm, "LoginFormName"


Randy

 

[psemianonymous]

If you use Access/JET security, the login screen is provided for you.

Note that if you use your own authentication system, any of your users can still edit the tables without any problems, including their user privileges. But if you're fine with that, that's okay too.


I wrote a (long) FAQ about this entire situation:

Gauging your security needs; alternatives to Access/JET security faq181-3893

 

[Ryker7]

What is wrong with this code? I cannot get the Dlookup statement to compile.

Field Names

**********************************
User = Name of field on table
tblUser = Name of table the lookup is occuring
Name = Name of field on logon form
***************************************



Private Sub Enter_Click()
If Not IsNull(Dlookup("User","tblUser","User='"&me.Name&"'")) Then


DoCmd.OpenForm "frmPassword"
DoCmd.Close acForm, "frmLogon"

Else
MsgBox "User not found.", vbOKOnly, "Access Denied"

End If

End Sub

 

[randy700]

Try replacing this...
If Not IsNull(Dlookup("User","tblUser","User='"&me.Name&"'")) Then

with this...
If Not IsNull(Dlookup("User","tblUser","User='" & me.Name & "'")) Then

Sometimes Access gets picky about the spaces around the ampersands. If it doesn't make the correction for you, it probably won't work.



Randy

 

[Ryker7]

Ok, I have the syntax working now. However, when I enter a user id, the msgbox appears whether the id is valid or not. It is as if the Dlookup statement is being ignored and the Else condition is executed not matter what. Any suggestions?

 

[randy700]

Here's another method I've used successfully.


Private Sub Enter_Click()
If Me.Name = DLookup("User", "tblUser") Then
DoCmd.OpenForm "frmPassword"
DoCmd.Close acForm, "frmLogon"
Else
MsgBox "User not found.", vbOKOnly & vbCritical, "Access Denied"
End If
End Sub


Randy

 

[OhioSteve]

If you want to channel well-intentioned but ignorant users into the right interface for them, then your approach may work. However, foolio12 is 100 percent right when he says that you are not building a real security system. For example, lets say that the bad guy creates a blank .mdb. He could simply link to data in your table and then nuke you. In addition, he could suppress your startup form with the shift key. Then he might be able to display the database window and nuke you.