Hi there - im building a contact management system and require some help with coding. I have a table (tblusers) for users that contains their information e.g. user name , password and tick boxes that will hopefully allow them to edit certain levels within the hierarchy of the system e.g 'allowcompanyedit' tick box will allow users with that box ticked to create and edit company information, 'allowcontactedit' tickbox will allow users with that bx ticked to create and edit contact information etc etc. I have created a user login screen and added code to a command button that checks their credentials against what is held in the users table to see if they should be allowed to log in or not. I would like to add additional code that will also check the tick boxes and give or deny them the ability to create or edit information on the company and contact level forms (users should be allowed to view these screens regardless of permission level).
here is the code on the command button at the log in screen:
Option Compare Database
Private Sub login_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(user) Or Me.user = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.user.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.password) Or Me.password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.password.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.password.Value = DLookup("password", "tblusers", "[ID]=" & Me.user.Value) Then
ID = Me.user.Value
'Close logon form and open splash screen
DoCmd.close acForm, "frmuserlogin", acSaveNo
DoCmd.OpenForm "frmcompany"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.password.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact Rhys or Richard.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub
would anyone be able to help me
Thanks,
Rhys.
MS Access User Defined Permissions
Bryan Kelly
Thanks Derek - susbstituting ID at the end for the actual ID number of a user does work - unticking the box will not allow the company button to be edited so as you say it does suggest that the ID isnt getting stored correctly in the variable - any ideas
Thanks again,
Rhys.
Mable
Pavan Apuroop
Also thanks for explaining the code to me - i can get my head around it now
with regards to the ID not storing correctly in the variable - one thing i forogot to mention was that the module doesnt accept the syntax
public dim ID as string
it only accepts
public ID as string
or dim ID as string
does not referencing memory make any difference
im asssuming the public declaration has to be there to allow the ID variable to be moved around as you said.
Thanks Derek,
Rhys.
sally_de
Derek - have solved the problem - i recreated the module with ID as the global variable and it now works as it should so thanks very much for your help, it has been invaluable and a real help in my understanding of how VBA works and how powerful it can be.
Rhys.
wunda
No worries Rhys,
In your user table you'll have fields that will be named things like ID, Password, Name, Staff Number etc. The ID field in this table is the most important because it lets you find an individual record (or it should if it's the primary key, which it should be).
You find a record in a table using an SQL statement which looks like this.
SELECT * FROM [tblUsers] WHERE [ID] = 4 (* stands for all fields in the table).
The code....
DLookup("password", "tblusers", "[ID]=" & Me.user.Value)
... translates to SELECT [password] FROM [tblusers] WHERE ID = (whatever me.user.value equals)
Which will only return one value, the users password, because the ID is unique for each record
Since you need the users ID to find information about that user, and that ID is entered in the login screen, then the code stores the entered ID (me.user.value) in a variable so that you can use it later.
ID = me.user.value
You can then use the variable ID to find out information about the logged in user, like if they have permission
DLookup("allowcompanyedit", "tblusers", "[ID]=" & ID)) becomes
SELECT [allowcompanyedit] FROM [tblUsers] WHERE [ID] = (whatever ID equals)
Your error message suggests that the ID isn't getting stored correctly in this variable, set a breakpoint on the line of code 'DLookup("allowcompanyedit", "tblusers", "[ID]=" & ID)) ' and make sure ID has a value.
Your right though there is a difference in querying for a string and a number...
how the code knows if your using a string or a number is like this...
"[ID]=" & Me.user.Value becomes [ID] = 4, so it's looking for a number
"[ID]='" & Me.user.Value & "'" becomes [ID] = '4', so it's looking for a string
SQL knows if your using a string because of the quotes. In VBA it doesn't matter if your storing it as a number or a string, as long as the value is numeric in nature.
Aleniko29139
No worries Rhys,
Ok, looks like I gave you the ideally wonderful fantastically groovy solution when it might be you don't really need to go that far. So lets forget my suggestion and keep it really straightforward as all your really looking to do is disable or enable a couple of buttons depending on the values of some fields in a table.
Your using a call to DLookup to find the users password and then your storing the ID of the user. This is good.
What you want to do then is use this function to look up the true/false values for both your buttons....
the true or false values in the table will cause the buttons to be enabled or disabled like this.
Me.ButtonForCompaniesScreen.Enabled = CBool(DLookup("allowcompanyedit", "tblusers", "[ID]=" & ID))
You'd more than likely put this in the switchboards OnLoad event, the ID variable must be globally (Public Dim ID as String) defined in a module.
How's that
Mitch Walker - MSFT
That was a silly syntax error on my part...
Public myGlobalVariable as String
... is the correct sytax
Code and variables in modules have what's knows as global scope, they are available throughout the whole database/spreadsheet/document/application. But they have to be marked as public so that other objects (forms etc) can access them. Conceptually it's like a bit of memory thats shared throughout the application. As long as your application has one user per application instance then everything is ok.
With Access you can have many people using the database but the forms and modules in the database are all loaded into and ran from the Access instance stored in the memory of the users machine, it's only the database data thats shared.
You shouldn't really use Global variables because they are updatable anywhere in your application so it can lead to really hard to find bugs, but sometimes you just have to and what your doing is one of those times.
No worries, happy to help.
MickJ27
Thanks Derek for all of your help - the issue was the syntax for the lookup "ID=" & ID)) - i added parentheses to ID to make it "ID=" & "ID")) and that has resolved the issue. I really appreciate your help becasue this was an important requirement in the system so thanks again
Rhys.
hacker_eg
Hi Derek, many many thanks for your reply. However i am only just starting out on the road to VBA nirvana and as such i know little about in-depth programming. All i want is to either allow or deny user access to create or edit information on either the company level form or the cotnact level form. The 'users' table holds the user name and password and authentication works fine and they then arrive at the switchboard screen, where they can click to view or create companies or contacts. The 'allowcompanyedit' tick box will control access ot teh company level screen and 'allowcontactedit' will control access to the contact level screen.
Your solution seems perfect for what i want to achieve but i have no idea how to do it (apologies)!!
do i just enter
Public Sub AccessFile(currentUser as User)
If currentUser.HasFilePermission = True then
End if
End Sub
into a class moduel and save and then enter
Dim currentUser as new User
currentUser.LoadCredentials(userID as integer)
AccessFile(currentUser)
as code into the relevant form's onload event - (im probably getting this totally wrong!!)
Also, which parts do i need to substitute for my own field names
Thanks very much, and apologies for making this harder than im sure it should be.
Rhys.
Eric Robert
Hi Rhys,
What I would do is create a class module called User in which there are variables and properties which return Boolean values indicating the true/false permissions of the User. When your user logs in create an instance on this class and set all the variables of the class to the values stored in the table for the user.
So for example Mr X logs in, once authenticated then create a new customer class and load all the yes/no values from the User table for Mr X.
You can pass this class around your application and use if statements to see if the user has permission...
for example:
Public Sub AccessFile(currentUser as User)
If currentUser.HasFilePermission = True then
End if
End Sub
You could call this method like this...
Dim currentUser as new User
currentUser.LoadCredentials(userID as integer)
AccessFile(currentUser)
I used this approach once and it worked really well.
pbjorge12
Derek - thought id solved it but i spoke too soon! it works for the first user that i added - but whatever setting i apply to him - i.e. allowcompanyedit box ticked or unticked will also apply to all other users, regardless of what their setting is - im confused!
Rhys.
fiNAL.Y
Hi Derek - i copied the code into the switchboard forms on load event, added the global variable in a module and changed the
me.buttonforcompaniesscreen.enabled to the name of the company button ie. me.comp.enabled but i get the message:
run time error 3075
syntax error (missing operator) in query expression 'ID='
Also as i modifed the existing login code for my purposes can you also tell me the logic of what the following lines do exactly so i have a better understanding:
If Me.password.Value = DLookup("password", "tblusers", "[ID]=" & Me.user.Value) Then
ID = Me.user.Value
i understand that it is checking the value entered into the password text box with a value in the password column in the tblusers table but what exactly does "[ID]=" & Me.user.value then
ID = me.user.value mean
- im assuming that having found the password in the table that it then tries to match up that password's associated user name with their unique ID number but im not sure how it matches a number with a string
Any ideas and thank you for your patience and understanding, i know it must be frustrating.
Rhys.