Authenticating users via a form

[Lorimare]

Greetings everyone, I'm wanting to authenticate users via a form. I will have a form with a username and password fields for the user to enter their info. Then a command button to send the info.

Anyone have experience doing this? I would think my first step would be to set up my 'users' table. For example the table would have: user_id(primary key), username and password.

From here I need to create a form that when the data is entered will compare the data the user entered to the data in the table for validation. If the user is not found in the users table he/she will be exited out of the application.

Thanks if advance for any help.

 

[LarryDeLaruelle]

Have you considered using the built in security?



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[formerTexan]

Jonair,

Definitely have a look at Access's innate security features. However I have heard comments to the effect that the maintenance of the innate security is rather an administrative hassle.

As far as setting up your own login form:

Note that you can set up a password field mask at both the form and table level.

The easiest way to validate the user's entries is the DLookUp function.


If DLookUp ("[UserName]", "tblLogin", "[UserName] = txtUserName) Then
Whatever
End IF

Check my DLookUp syntax as my memory may be fuzzy. You'll need to do the same for the password either seperately or simultaneously. And then decide on how you want to handle the various permutations (true/false/partially true, etc.)

Additionally you can use the login form to handle all sorts of other events: command bars, other forms, etc.

Cheers,
Bill

 

[scottian]

I wouldnt even bother with the user id and password.
just simply grab the users existing PC login username, then in either the forms open event, or the button or event which opens the form have some code check that user is allowed in this area of the db, if not create an error message or a nice friendly form to inform the user that access is denied

"My God! It's full of stars...

 

[randy700]

The problem with using the pc login username is that your users would need to be on their own pc to obtain access. I prefer the login form. The table structure you described is correct. On the form, you will need two text boxes... one for the login id (txtLoginID) and one for the password (txtPassword). Then place the following code in the click event of a command button...

Private Sub cmdEnter_OnClick()
If Not IsNull(DLookup("UserID","tblSecurity","UserID='" & me.txtLoginID & "'")) Then
If Not IsNull(DLookup("Password","tblSecurity","UserID='" & me.txtLoginID & "'")) Then
ID and password are valid.
Enter any code necessary
Else
ID was valid, password was wrong
MsgBox "Incorrect Password",vbOkOnly + vbCritical,"Access Denied"
End If
Else
ID was invalid
MsgBox "Incorrect login id",vbOkOnly + vbCritical, "Access Denied"
End If
End Sub


Randy

 

[scottian]

quote
"The problem with using the pc login username is that your users would need to be on their own pc to obtain access."

if a user is using a pc with someone elses ID then this flies in the face of a user login system. users login for security and auditing purposes. so why not use that which has already been built to your advantage. this also dispenses with the need to have users enter data such as id and password which is not securely stored anyway.


"My God! It's full of stars...

 

[Stolman]

I have a similar situation and need help.
MGID is a Long Integer in MG Names table.
The code in my validation form is:

Dim intMGID As Variant
' Validate the MGID, if any
If DLookup("MGID", "MG Names", "[MGID] = Forms!intMGID") Then

Else
' Not good - tell them
MsgBox "The MG ID you entered is incorrect.", vbCritical, gstrAppTitle
End If
' Good to go ... open the Activity form
DoCmd.OpenForm "frmActivityEdit"

The problem is that I always get the error message, even when the correct MGID is entered. Can someone please point out what I am doing wrong?
THanks!

 

[PHV]

In the VBA help file have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fneily]

DLookup("[MGID]", "MG Names", "[MGID] = " Forms![intMGID])

As an aside, you may also put "Usys" as a prefix to your table name. eg. UsysMG_Names. This will hide your UserID and Password table from casual Access users.

 

[PHV]

Try something like this:
If IsNull(DLookup("MGID", "[MG Names]", "MGID=" & Me![name of MGID control])) Then
' Not good - tell them
MsgBox "The MG ID you entered is incorrect.", vbCritical, gstrAppTitle
Else
' Good to go ... open the Activity form
DoCmd.OpenForm "frmActivityEdit"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stolman]

fneily/PHV - Thanks for the prompt assistance!

Alas neither works. fneily, yours will not compile and PHV's yields: syntax error (missing operator) in query expression 'MGID='.

Sorry to be such a dough head but can you please take another look and suggest what is needed to make it work?
Thanks VERY much,
-bill

 

[fneily]

DLookup("[MGID]", "MG Names", "[MGID] = " Forms![intMGID])

Wasn't paying attention when I typed in the above. Should be

DLookup("[MGID]", "[MG Names]", "[MGID] = " & Me![intMGID])

 

[Stolman]

All,
Finally got it. The only syntax that works is:

If (DLookup("MGID", "MG Names", "MGID=" & "[intMGID]")) Then

Seems so simple once I got it right. Thanks for all your attention and assistance.

-bill