Creating user login with access

[Scythed]

Hi, I am quite new to access and I am trying to create a user login. What I currently have is a table with two fields, username and password. I want to be able to type in a username and password onto a form, then click a button and have it check whether the login details i entered match any login details in the database. If anyone could help me I would really appreciate it. Thank you.

 

[statsman]

In order to do this, you will be required to enter a Twilight Zone known as Access Security.
Almost everything about Access can be learned by trial and error EXCEPT SECURITY. You really do require a good guide to get you through this black hole.

For an excellent overview of Access security and how to do it advice, go to:
http://www.geocities.com/jacksonmacd
download the first file – Security Paper by Jack Macdonald. It’s a little long but it tells you everything you need to know.

 

[Scythed]

In regards to my security, it is only for a school assignment so it does not have to be hack proof etc. Is there any simple way you know of that will allow me to do that without getting into too much intricate detail?

 

[statsman]

I believe the only way to require a UserID and Password to gain access to the database is by going through Access Security.
Note: I may be wrong about this, if so perhaps some other poster will correct me.

 

[GaryPanic]

no you wrong..

it is possible to have a secuirty system
you need to have a form that has
user id and password
so it MarkZ (user id)
and enters in Password and the after update of the password field in a form = whats on your table then it allows you to go on ..


Private Sub CheckLogin(Pass As Boolean)
Dim ValidUser

If Pass Then Try = Try + 1
If Try > 3 Then
MsgBox "Too many attempts, please try later", vbCritical
DoCmd.Quit
End If

If IsNull(UserName) Or IsNull(UserPasswd) Then Exit Sub

ValidUser = DLookup("UserName", "UserPassword", "UserName ='" & Me!UserName & "' and Password ='" & Me!UserPasswd & "'")

If IsNull(ValidUser) Then
If Pass Then UserName = ""
If Pass Then UserPasswd = ""
Exit Sub
End If

DoCmd.OpenForm "FrontScreen", acNormal


not my code
Frontscreen is the second form to open

 

[statsman]

Thank you Gary. I stand corrected.

 

[GaryPanic]

oops that did come across abit abrupt - - bad manners of me .. aplogies -

back to secuirty - i was told my the guy who showed me stuff - stay away from Access secuirty stuff , cos its a bitch ..

 

[Scythed]

Hi, thank you for your replies. The only problem is I don't fully understand how to link the form to the specific table. Do I have to bind the text box? If so, do you know how to do that? Because I cannot find it in the properties list of the text box. Thank you.

 

[boblarson]

Don't know if this will help, but here's a mini-sample for a simple security setup:
http://downloads.btabdevelopment.com/samples/misc/LoginSample.zip

 

[Scythed]

Using that sample that you gave me I tried to snip off some bits and eventually ended up with this. Note: My username in the table is CustomerID. Password is LastName. My username input on the form is CustomerID as well and the Password input is also LastName.

Private Sub Command5_Click()
Dim strUser As String
Dim strPWD As String
Dim intUSL As Integer

strUser = Me.CustomerID
If DCount("[LastName]", "[Customer Details Table]", "[CustomerID]='" & Me.CustomerID & "'") > 0 Then
strPWD = DLookup("[LastName]", "[Customer Details Table]", "[CustomerID]='" & Me.CustomerID & "'")
If strPWD = Me.LastName Then
DoCmd.OpenForm "[Browse Menu], acNormal"
End If
End If

End Sub

For some reason though I am getting a data type mismatch on this line.
If DCount("[LastName]", "[Customer Details Table]", "[CustomerID]='" & Me.CustomerID & "'") > 0 Then

 

[boblarson]

Take the single quotes out of the "[CustomerID]='" & Me.CustomerID & "'") part. If CustomerID is a number then you would just use

"[CustomerID]=" & Me.CustomerID)

 

[Scythed]

Edit: Nevermind I just removed the square brackets and the acNormal part. Thank you.


I tried that and it seems to have fixed that problem, but now I have another one.
When I type the username and password in it tells me that the form name '[Browse Menu], acNormal' is misspelled or refers to a form that doesn't exist. I have double checked spelling and they are both the same, I tried removing the " from in front of [Browse Menu] and after acNormal but that does not seem to fix it either. Any help would be greatly appreciated. Thank you for helping me out a lot so far.
DoCmd.OpenForm "[Browse Menu], acNormal"

 

[boblarson]

It would be:

DoCmd.OpenForm "Browse Menu", acNormal

 

[Scythed]

Thank you very much

 

[RodgerDJr]

Don't know if this will help, but here's a mini-sample for a simple security setup:
http://downloads.btabdevelopment.com/samples/misc/LoginSample.zip

I created a very similar DB as Bob did in the above sample. I like the idea of having a hidden form with the variables I am using Global Variables but when the code breaks I lose them so Bob's way is much better and I will have to change my design to include that.

I am having one isssue with this method. When I have a user that is read-only (And open the form as read-only) the user is unable to use the search drop down I have at the top of my form. Kinda makes the form unusable. As the only way to go from record to record is to use the controls at the bottom. Is there a way I could open the form read only but allow the user to search the form?

I was thinking about having a function that would look at all the controls on the form and subforms and update thier property to Enabled=No and Locked =Yes making them "read-only" but not set the drop down for the search.

This seems like it is going to be a lot of work and was wondering if there might be a better way.

TIA,
Rodger

 

[boblarson]

Just add something like lock to the tag property of any of the controls you want locked. Then you can just use this code in the form's Current event:

Dim ctl As Control

For Each ctl in Me.Controls
   ctl.Locked = (ctl.Tag = "lock")
Next ctl

 

[RodgerDJr]

Just add something like lock to the tag property of any of the controls you want locked. Then you can just use this code in the form's Current event:

Dim ctl As Control
 
For Each ctl in Me.Controls
   ctl.Locked = (ctl.Tag = "lock")
Next ctl

Bob,

So if I place "lock" in the tag property of all the fileds I want to lock and then I add your code it will turn the property for each field to Locked=Yes?

Wouldn't I need to have an If Statement? To check if the controls tag property has "lock" and then set the Locked Property to Yes.

Thanks,
Rodger

 

[boblarson]

Bob,

So if I place "lock" in the tag property of all the fileds I want to lock and then I add your code it will turn the property for each field to Locked=Yes?

Wouldn't I need to have an If Statement? To check if the controls tag property has "lock" and then set the Locked Property to Yes.

Thanks,
Rodger

Actually this part:

ctl.Locked = (ctl.Tag = "lock")

is just like an If - as it will set the locked property to whatever the other equals statement is - so if

ctl.Tag = "lock" then it would return True

if

ctl.Tag <> "lock" then it would return False


see how that works? It is really compact code and I have only recently learned how to use that to shorten things up.

 

[RodgerDJr]

Actually this part:

ctl.Locked = (ctl.Tag = "lock")

is just like an If - as it will set the locked property to whatever the other equals statement is - so if

ctl.Tag = "lock" then it would return True

if

ctl.Tag <> "lock" then it would return False


see how that works? It is really compact code and I have only recently learned how to use that to shorten things up.

I did try that, but it did not seem to work. Let metry it again. I am now working on updating the form to know what group the user is in and then i will check if the tag is read only depending on what group they are in.

I will let you know how it works. Thanks again for you help!!! The tag seems to work just great!!!

 

[boblarson]

Actually, I just realized that for this occasion it won't work because there are different types of controls and some of them don't have a "Locked" property.

So modify to:

Dim ctl As Control
 
For Each ctl in Me.Controls
   If ctl.Tag = "lock" Then ctl.Locked = True
Next ctl