Login and User Access

[gemma-the-husky]

You will need to add code to each form to test the user login, and set the form to allow edits or not. You may actually need to lock individual controls on firms, as you might find that simply disallowing edits is too severe - as it will stop you changing non-critical drop downs, and so on.

 

[Emma35]

Yes you have if you have just typed those passwords in tblUsers.
Remember that the passwords are meant to be stored in an encrypted form.
Look back at the screenshots in the original link.
You might be better going back to the original version

Ok sorry that's exactly what i did. I see there's a 'Add New User' form...so i delete the records in tblUsers and then add my own users with that form ?

 

[isladogs]

Yes.
If you get stuck copy the original table tblUsers into your modified version then edit it as appropriate
The idea is that nobody needs to know the actual password except the person entering it as only the encrypted value is stored

Dave made a good point regarding combos on read only forms.
If necessary, you can use code like
Me.Combo1.Locked = False
Me.txt2.Locked = True
or you can use the Tag property to modify the locked state of a group of controls at once.
But we'll come back to that later if necessary

 

[Emma35]

Another small problem...when i try to add a new user using the form provided it says i can't have a space between first and last names and is limited to 15 characters ?. Can this be changed ?

 

[isladogs]

I strongly recommend you study the code behind each form and the table structure. If you are going to use something like this, you need to understand how it works

The table tblUsers has a field size=15 for user name. You can increase that if you wish though that should normally be sufficient.

If you want to change either the max length or allow spaces, you need to modify the following procedure in frmNewUser which checks for valid user names:

Private Function CheckValidUserName() As Boolean

    CheckValidUserName = True
    
    If Nz(Me.txtUserName, "") = "" Then
            FormattedMsgBox "User name NOT entered" & _
                "@Please try again     @", vbCritical, "You MUST enter a user name!"
            CheckValidUserName = False
    ElseIf Len(txtUserName) > 15 Or InStr(txtUserName, " ") > 0 Then
           FormattedMsgBox "The user name must have a maximum of 15 characters with no spaces" & _
               "@Please try again     @", vbCritical, "User name error"
           CheckValidUserName = False
    End If
    
    If CheckValidUserName = False Then
        cmdAdd.Enabled = False
        Me.txtUserName = ""
        Me.txtExpireDays = 0
        Me.cboChangePWD = "No"
        Me.cboLevel = 1
        Me.txtUserName.SetFocus
    End If
    
End Function

Whilst you could allow spaces in user names, I wouldn't recommend it.
Access will not be able to distinguish between Emma, Emma 35, Emma 59
whereas Emma, Emma35 & Emma59 would all work fine

 

[Emma35]

Ok this is definitely more complicated than anything i've tried before but if i can get it working it would be a great asset to the database. I've changed the password length to 20 characters (we have two Polish users with very long names).
I added myself as a user and then tried to set up a password when i logged in for the first time but got the error attached when i clicked LogIn ?

 

[isladogs]

Even your Polish friends might be pleased if their user name was shortened.
Those from Sri Lanka often have names even longer than 20 characters.

I'm pleased to say, the corrupt query error is nothing to do with my app. Its caused by a flawed MS Office update from 12 Nov and you are very lucky its not affected you till now.

Luckily there is now a fix for all affected versions of Access. See this sticky thread https://www.access-programmers.co.uk/forums/showthread.php?t=307918. There is a link to the fixes in post #60 of that thread

 

[Deleted member 147267]

Is it too late to ask if a password function and all it's 'hassle' is really needed versus just getting the Windows login name and looking up their profile/permissions in tblUsers? I scanned the thread and didn't see where anyone asked.
If my Windows login name is Micron and I'm in the table, I get into the db. If not, I don't. Once in, the app knows if I'm just a looker or an editor. Obviously the standard stuff about not allowing me behind the scenes applies.

If there was a response that said this app was on a shared Windows login, then I missed it and will apologize for the intrusion.

 

[Emma35]

I can't download the fix without consulting our IT dept. :banghead:
This is a pain in the a** !

I'll come back as soon as we've completed 17 safety meeting and 11 seminars

 

[isladogs]

Your IT department ought to be aware of both the issue and the fix and should have a plan in place for implementing it...after all the Nov 12 update was applied due to policies they already have in place.

However in the meantime you can apply temporary fixes several of which are described in that lengthy thread

 

[The_Doc_Man]

I'll come back as soon as we've completed 17 safety meeting and 11 seminars

What branch of the government did you say you worked for? Sounds like a typical week for me as a Navy contractor.

 

[Emma35]

The IT fella has told me that he can either install the patch or just upgrade me to Access 2016. If i get the 2016 version installed will that solve this problem or do i need the patch also ?

Thanks

 

[Emma35]

What branch of the government did you say you worked for? Sounds like a typical week for me as a Navy contractor.

Doc Man i don't work for the government it's actually worse than that. The IT guy told me it would be the new year before this got fixed, so i told him i'd knock him out. He's fixing it today....sometimes all you men need is a little friendly encouragement

 

[isladogs]

The IT fella has told me that he can either install the patch or just upgrade me to Access 2016. If i get the 2016 version installed will that solve this problem or do i need the patch also ?

Thanks

The bug affected all versions of Access from A2010 onwards including A365.
Separate fixes are now available for all versions

 

[The_Doc_Man]

so i told him i'd knock him out...

sometimes all you men need is a little friendly encouragement

Actually, I'm of two minds on this. Sometimes you DO need a touch of emphasis to show the fervor you have for your request. If it gets the message across, it isn't entirely bad.

However, my other thought is that one day I was visiting at a friends house and he had a "Gentleman's magazine" on the coffee table. Curious person that I am, I started reading. This ad popped up out of my memory when I read your post: "Aggressive gals who want to meet YOU." Not that I accuse you of liking leather and whips, mind you, but my crazy associative memory reacted to the way you said that.

 

[Emma35]

My threat was strictly tongue in cheek Doc man...although i swear the next time he tells me to "turn it off and back on again" i WILL deck him

Colin....i downloaded the patch and everything is working fine. It looks great thanks. I have two more questions (i promise).

The access level restrictions are working when i enter the code in the OnLoad event of the forms, however i have another form which is used to create a new record and the read only users can still create a record...how can i prevent that ?

Also, if someone forgets their password do i need to go into tblUsers and delete them before i create a new account for that user ?

Thanks

 

[isladogs]

Modify the code I gave in post #18 to cover adding/deleting records as well as editing them

Private Sub Form_Load()

Select Case GetAccessLevel

Case 1 'full access
Me.AllowEdits=True
Me.AllowAddtions=True
Me.AllowDeletions=True

Case 2 'read only
Me.AllowEdits = False
Me.AllowAdditions=False
Me.AllowDeletions=False

End Select

End Sub

Once again the code in Case 1 isn't really needed as that's the form default settings

There are several ways to handle forgotten passwords:
You could just tell the user the existing password which can be viewed in qryUsers. However that query shouldn't really exist as anyone who sees the query will know all passwords.
If you do that it might be worth forcing a password change by ticking the ChangePWD field, setting ExpireDays to any value >0 and the PWDDate to yesterday.
Another method would be for you to force a password reset by changing the PWD field value to the encrypted version of 'Not set' by copying the value from Jill's record.
Or as you say you could delete the user and start again but I would avoid that if possible

 

[Emma35]

That works perfectly. Thanks for everything. I may not understand the whole code 100% but i know just enough to make it work. Thanks again for your time and patience

Em x

 

[isladogs]

Glad I could help. Good luck

 

[Emma35]

Hi Colin.....not sure you got my message but i can't seem to get the LogOut code from the Log me Out button on frmSessions to work with my Quit Button on my switchboard. I added the code and then added a DoCmd.Quit directly after it but it gives me a Data Member Not Found error. I tried adding a reference to the form frmSessions to the code but it didn't work ?

I changed Me.1stSessions.Requery to

Forms![frmSessions]!1stSessions.Requery