Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: ruclips.net/channel/UCeXAUvo5xxDY_b-lSknPC1A
This video is really help and I am still enjoying them. God bless your effort for helping us with your knowledge. If I may ask, can you teach us or any ideal on "how to generate or create a licenses key/product key/serial number with an expiry date like (Non-Expiry, 6 months, 1 Year....etc) for your database. Maybe Offline or Online. I hope to hear from you. Thank you once again for your time. Love you.
Steve, this video tells it all. I have been all over the internet trying to find someone to follow that can direct me the way that Relational Database Management Systems truly manage data. I have still some issues with Normalization and Relationships holding me back, but when i go back to videos that you produce like this one as an example, I am truly leaning this way again. This video seems to reduce the opportunity of outsider hacking substantially. I am pretty darn sure that there is not to too much more that you can do, to keep your users on a solid path in teaching them, that at that time to follow path that has been set out for them, and over time with some simple proof of service, they also can find themselves moving up the ladder and taking on more responsible roles over time. But the framework is in this video to keep your users within the scope of the roles set out and over time permissions or roles do change based on their history. So well done Steve, you are truly a master of disaster, prevention that is . . .
Thank you Steve. I've worked in Access several years, but this is the first time I've had to use VBA for my databases. Your videos have been very helpful.
thank you Steve for your tutoring videos. I really enjoy learning and putting your teachings to practice.. very, very helpful.. thanks again and keep up..
It has been a great experience watching your videos. Am glad that I met someone of such wisdom that I am hungry for now. Thank you so much for been an agent of change in my life and future career. CONGRATS Steve
Such an amazing presentation, not to mention how helpful this is for the project I am working on, I had to sign up for a membership. Thank you so much.
Excellent Video, followed step by step and all worked as should, however I did find that if the form I have put the ‘On Load’ code on, is a subform. I first get the “You do not have assess” message box as I should but when I click OK, it opens anyway. Any suggestions on how to prevent this?
Hi Steve, your video is very helpful. I do have a question. While creating your tbl9EmployeeAccess, do you follow the basic database normalisation? I am asking because I would like to apply this procedure to many of my form and for each of them I will have to repeat 3 times the record and change only the HasAccess. Can we do it the same and follow the database normalisation? Thank you in advance!
Thank you very much for your tutorial Steve, I'm currently attempting to build a training database for my department and your videos are really helpful.
Hello, sorry for asking this dumb question here, may I know how to open the work file given? I keep getting [Run-time error 3044: 'c\user inger\desktop\database\service inc_be.accdb' is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides] when trying to login from the fe file. Is there anything that I missed?
Question: It seems that TempVars will only be recognized until you close the access database. once you reopen it, the script will not work. what would I be able to use to have it there permanently?
I am looking for the coding needed to disable the Close Access button on my Access 2013 database. I have watched several of your tutorials and I love how simple you make learning the VBA coding. Could you possibly help me figure this out? I would really appreciate it.
Hello, we are 5 mutli-users on Access working on a front end database that connects to a back in database. Our company pushed out an auto update of MS Office 2013, and all of a sudden only 1 user can get in at a time; normally all users can be in working at the same time with no "Locking" in records. The error we get is that the '...backend accdb file is already in use'. Again, the program works fine for 1 user but nobody else can connect to the backend simultaneously. How do we fix this? Thank you.
Hello Steve, i downloaded the project files from GitHub, when i try to open the FE file it gives me the following error, Run-time error '3244: 'C: the path is not valid not a valid path. Make sure that the path name is spelled correctly and hat you are connected to the server on which the file resides. would you please help
Hi Steve. Can all of this be done with for a command button that is used to fill a form text box? Example: I've created a UserAccess table with columns for; SecurityNo, ButtonName and HasAccess. I want to use the information on that table with the user login, to allow or deny the user to click the button to fill in the text box above with the Windows UserName. If not...message box "You do not have access!" will appear. Simple... For you... Access 2019, btw. HELP ME, PLEASE!!!
I tried this but for some reason it is not blocking access to restricted forms. In this section of code: DLookup("HasAccess", "tbl9EmployeeAccess", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'") If the "EmployeeType_ID" referencing the tbl9EmployeeAccess table or tbl2EmployeeTypes table? Thanks.
I have found your videos to be exactly what I need at the time I need them. Thank you so much for what you do. I had lots of fun restricting users from forms, but would like some help (or point me to the video) to restrict form fields for those that should have read only access. I created that as an option in my UserType table (you might have called it employee type). When the form loads, would love for only those with read/write access (or higher) to be able to modify fields (change table values). THANKS AGAIN
There are several ways to address this. I've seen people use a field with a numeric value to hold the access level. 0 is no access, 1 is read access, and 2 is read/write access. Then just check the value for the user and the section in a case statement to determine what the control appears like. Hidden for 0, visible but locked for 1, and visible unlocked for 2.
Hi, I wonder if anyone can help me. I think I missed a step. at 17.48, where You are talking about "TempVars" and using "EmployeeType_ID" I didn't see any of this (And much more of the code on this screen) in your "Live coding build a login screen". Can anybody help?
very need! can we so something the same with tables, queries and macros? i really admire the efforts to make those clear session on the topic. i pickup many new technics. keep going. cheers.
Thanks a lot Steve, big fan of ur videos Q. Can we link another database with the main database and when the form is submitted save database in another database ( not updating the linked access)
Fantastic series, Steve. Much appreciated. Could this approach be used to Enable and /or disable multiple controls on a form? I currently have a form that different usertypes can access, but I only want 3 user types to be able to make changes to 3 of the controls on the form.
okay, one other questions. Due to having a lot of different forms that different groups will need to have access to, i'm trying to see if there's a way to create a short cut, through creating two separate "launch menus" that load after the user logs in, utilizing the employeetype_id thought. Admins with employee type 1 would go to "frm_launch" and users with limited access (employee Id =2) to pull reports and view read only tables would go to "frm_smllaunch". The code i was trying to use would go at the end of the vba for the login form, after adding and hiding employee type onto the form. I've run it, and regardless of what person i put in, all are going to frm_launch, even if their employee type is 2. Here's what i've done- is this anywhere along the route to success? Thanks! If rs!Password = Nz(Me.txtpassword, "") Then txtempl = employeetype_ID End If If employeetype_ID = 1 Then DoCmd.OpenForm "frm_launch" DoCmd.Close acForm, Me.Name ElseIf employeetype_ID = 2 Then DoCmd.OpenForm "frm_smlaunch" DoCmd.Close acForm, Me.Name End If
Good Day Steve. I recently came across your tutorials, which I have found very useful, and although I have a fair knowledge of Access i have not done a lot on Modules. I have just finished using your Login tutorial and everything works fine. I am now moving on to your How To Manage User Access tutorial. Checking what I need in the Db I am using when I open the Modules in the Navigation pane I cannot see the module for the Login. If however I look at the Login Form and open the properties for the UserName and Password there they are. Is this correct or have I done something wrong when creating the Module for the Login. Looking forward to your help ( with maybe many more questions to follow) I appreciate your time. Regards Mike
how does this work when you have multiple people logging into the same database? wouldn't that effect Tempvalues? i have one file location for a department or should i make links to new data bases that these members download to their desktop.
Very useful. However, the confusing part is that it seems you are bypassing Access work group security. Shouldn't we set permissions using the work group (group and user level security)? For example, when you use Current user() it returns Admin not id used to log in. Am I missing something? Thanks
Thank you for video tutorials you posted. I found they are very informative and helped me to write vba code. If I want to restrict user to only access subset of all records( e.g a user has access to one or multiple departments), can I replace object form as field name using this approach ? Thank you
Hey Steve, the video is a few years old but i hope you will answer my question nevertheless. If I split my database into front- and backend, the users see all connected tables in their frontend as well. Theoretically they can see so any informations from the tables, also the passwords of the other users. How can I prevent this? The other point I want to know is, how can I prevent that users simply write informations directly into the tables even if the equal formular is locked for them? How can I prevent, that the users can see the content of the connected tables? Thanks a lot! :)
Great video Steve! I am having trouble with my administration login section. When I log in as admin it doesn't really do anything different than the other profiles that I created. I might not fully understand it but I thought by turning the Bypass Key on that it is basically like opening the database while holding shift. Is this accurate? What exactly is turning the Bypass Key on do?
I wrote code to integrate my user table to an LDAP query function against our AD. This checks who they are logged in as and then only gets them what they have rights to see. No additional login prompt or password storage in an unsecure database. Great video though.
Ok you have used the codes to code the login form how about if someone who is familiar with those codes can they use the macros to do those codes made for the access to on the longing form instead of him/her typing in codes, please your advice is much needed.
Hi thanks for the video very helpful. You have another video explaining how to control what data (not form) can a person see and modify. For example sales representative can only see employees from sales department
hi steve thanks for the great video. I have tried your tutorial regarding creating a login screen and it did go well however when I tried to add this new code for managing user in my login form starting from tempvar it seems that other user except for the one i declared in "If rs!EmployeeType_ID = 1 Then" is not going through my log in page and also there is no error appearing on it. hope for your advice. thank you.
Dear Steve , do you have tutorial for user session management? example there are 10 user with different privileges , there are logout button to destroy the session (or there are other efficient technique to attempt this). Each user can not view other user input list (each user can only view their own created records). All in ms access matter.
Hello Steve, Thank you so much, you are wonderful and helped so much. I do have one issue, on the Onload event, I have another piece of code that is filtering the form by Division … how do I add this other piece of code (user access) so they both worked. I did try Else but it did not work. I appreciate any help. Lisa
No. You should be making tables and design views inaccessible by disabling those features. If you watch my Programming In Access 2013 series, you will see how to do that in the later videos.
Hello Steve, I have follow your steps and a Compile error: Argument Not Optional appears highlighting the Logging Global after I login again. Will you know why?
Hi , Thank you for your videos, it has proved to be very, helpful, I have a question when i use this code UserAccess = Nz(DLookup("Has_access", "tbl1_Employee_Access", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'"), False) I get a runtime error 2471, Any suggestion what I may be doing wrong?
Hi! I am really new to access, but I have made a database which is going to be used by ten people. So I would like to manage their access not only to the forms, but also to the tables. I have seen your video about splitting the database into back end and front end, but my database is not that big and I don't want to split it. So I would like to ask you is there another way to limit the access of the regular users, so they wouldn't be able to see some of the tables. Thank you. :)
Hi Maria I would strongly suggest that you split the database. If you are trying to control access to portions of the database it really is the way to go. Also with that many users you will shortly have a situation where two users want access to the database at the same time. With a split database arrangement across a network this is easily accomplished.
This was a very helpful lesson. I'm wondering if the same user access functionality can be applied to a tabbed form, where all users could see the first tab (page), but only certain users can see the other tabs (pages). Thanks!
Yes. Create a function that returns a boolean. Specify the employee type that should have access in an array. Loop over the arrays of employee type, then return true or false for each page per employee type, A Select Case per employee type would be needed, then you specify the return value for each page. You would need global variables to hold the page boolean properties returned by the function (you can use the tempvars ("page Name") to hold the function return value. On the OnLoad event of the Main Form: Page.visible = tempvars("pagename") and you should be good.
You can hide navigation menu and prevent ordinary users to change information in the table. However, there are still chances that an advanced user may be able to change this information and get access to your database. That's why MS Access is not considered secure when it comes to controlling user access. Still you may find smart ways to secure your database. Google until you get a solution for you.
Hi Steve, This video only restricted one particular form, what if I have additional buttons and forms that I would like to restrict. How can I accomplish that? Thank you.
Hello Steve I wanted to know how to creat a bulk or group login For example we have several sales groups or different companies are using the same app with multiple users at different security levels but all of them as a group can only see records associated with their company or group I hope I was clear enough ! And thank you again I learned a lot from your videos
Thats great lesson , but i have two questions 1- when i have more than one form to each user 2- i want to have more than user access to the same form but with different accessibility
Is it easy to convert an access 2003 database to 2010 or later ? What do you do about security. If converting from 2003 to 2010 do you have to write a new front end for 2010 ? What about the back end ?
Hello, again, Steve. Just wondering, If I have more lines of code to execute following the jump to and back from the globals.function "has access or not". Shouldn't there be an "exit sub" if the user is not permitted into the specific form? Seems like trying to open the off-limits form generates an error messages when those lines of code can't execute successfully after a 'no permission granted' log in. (the extra lines of code define the db/rs parameters for opening that form
I'll try to explain better. On form load, the VBA code, per your video, calls a globals function that looks at a user-has-access-to-this-form verification table. If user does not have access/permission, then the on_load routine should stop running right away, right? So, an "exit sub" command should be invoked, immediately, right? Before any form controls' data is loaded.
The routine, without any user-requested 'corrections' being asked, ought to end. My forms load with the declarations of what info the form should show, by defining what rs is: what table, etc. those come after the function returns it's result, outside the "if/then/else block.
Howard Ammerman I would say yes in the scenario you are suggesting. You would want to exit the sub if the user should not have authentication to open the form. An Exit Sub would jump out of the subroutine at that moment, and any code written after the Exit Sub would be skipped.
I think the code you demo should really be called by the Form_Open event as it has a Cancel parameter. If the user doesn't have permissions Set this to true and when Form_Load finished processing the form will not open and it wil pass control pack to the code that opened the form. eg Private Sub Form_Open(Cancel As Integer) if not PermissionToOpen then msgbox "You are not worthy to enter this form" cancel = true ... End Sub
Hi. I downloaded the example database you provided and I tried to login but I get an error message 3044. I'm using access 2013. Any idea how i can fix this?
The error I get is that I am unable to open the FE database since it is referencing a file location that is not on my machine. The 3044 tells me the file path it is trying to reference to and it appears to be from the author's machine.
Hi Steve thanks a lot, but how can i open the Front end database ? database ask me to log in, but i don't have any login id and password, please help me
Hello, downloaded your database for testing, getting error after wrong login: it gave me a path that not from me :) C:\Users\Ringer\Desktop\Database\Database\Service Inc_Be.acccdb. Im sure I dont start the accessfile on that path !! made folder and try to login in.. i get "Incorrect Password" And NO the "Password1"doesn`t work !!! please help with this
Hi Steve, I'm learning mostly from your tutorials and I comparing to others in RUclips and you are the best! I want to be a member in your channel. Right now I'm working on this log Screen for my project Auto-email reminder in our office, kindly consider me because along the way, I do have some questions/issues which you might help me. Thank you so much. By the way where I can download this code?
Hi I think i missed a bit about TempVars("employeeType'). When I recreate the code you have in this video. The code on the Employee form gives me a compile error "TempVars sub or function not defined." There is no compile error on the login code. I feel that the tempvars code is not being stored any where and I need to declare it as a variable someplace outside the login code?
TempVars is a collection that is a part of the Access codebase. it isn't something you have to declare because it already exists. I'm not sure why you're getting this error.
is the "employeeType" in the tempvars a reference to something or is the name just a place holder? When i run through the code. And hover over the TempVars it says the value = NUll
Hi, Love your tutorials! I am having an issue with the Employee Form coding. If DLookup("HasAccess", "EmployeeAccessT", "EmployeeTypeID=" & TempVars("EmployeeType") & " AND FormName='" & Me.Name & "'") = False Then This is resulting with an error: Run-Time error'3075': Syntax error (missing opererator) in query expression 'EmployeeTypeID= AND FormName='EmployeeF" Any suggestions?
I realized that I had commented before. Since this comment I have went through the tutorial again and I got the login screen to work perfectly as you stated in the tutorial! Thank you!
Hello, I know that you added this comment forever ago, but can you explain what was wrong with your code? Im having the same issue, but am typing this exactly. I cannot figure out what is wrong with the code.
Great lesson! Question concerning the TempVars, I was able to reference a record set in another form but is there a way to store more than 1 record set? I'm trying to reference a few record sets within the same username. Thanks again!
Thanks for the reply! Would you be able to provide an example code. I tried creating another TempVars recordset and it stopped my login code username and password. Thanks!
You should not be assigning tempvars recordsets. There isn't any need to anyway. You can assign a specific value to a TempVars item, but you won't want to do the entire recordset.
Hi Steve Nice, What if I wanted to restrict only a few controls on a form. For example some user may have view access, while others may have add, delete and ofcourse view access. How can I achieve this? Thank you in advance
Since this is not setting permissions at the table level, presumably users will have free reign to do as they see fit once they do open a form meaning edit, delete, add unless of course the form definitions prevent such. But what if we want some users to have the ability to edit and others just read? Wondered if you have addressed that anywhere. I am thinking expanding on the HasAccess to include other options such as AllowEdits, Allow Deletions, AllowAdditions. Obviously that complicates things especially if the user should have permission to do multiple options. I think I know how I would address it but seems ugly and perhaps you have a good idea that you have already covered.
+ShoppersCritiqueIntl You can make allow edits, and allow additions properties false for the entire form based on the value stored in the table for that type of user.
Hi Steve, i watch this Video several times and try to use it in my DB, also im not using (If rs!Password Encrypt(Me.txtPassword) Then) im just using basic password (If rs!Password Me.txtPassword Then) I have two problem 1-dose not make any different if i type password or not system accept and let me go to my DB, but if i type wrong password (Me.lblWrongPass.Visible = True) coming up. how about if password is just the numbers? 2-for using your codes in VBA application which References i need and in which order (which on first .....) 3-when i used your exercise DB, if you not type anything in password box, Microsoft V.B. widow comes and saying (Run-time error '94':)(Invalid use of Null) ** this is error high lighted in code "If rs!Password Encrypt(Me.txtPassword) Then" any help? thank you
+Majid Akbary You need to wrap the me.txtPassword in an Nz Function.... so Nz(me.txtPassword). I mentioned this in the annotation of the video as well as the description since it was a mistake in the original video. As for the references, it entirely depends on what 3rd party libraries you are using. There is no way I can answer that for you. Lastly, the invalid use of null I'm not sure of. You will have to try to debug the code and figure out what it's trying to do.
Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: ruclips.net/channel/UCeXAUvo5xxDY_b-lSknPC1A
THIS GUY IS THE MOST POWERFUL TEACHER OF PROGRAMMING, I HAVE NEVER FAILED TO UNDERSTAND HIM.
This video is really help and I am still enjoying them. God bless your effort for helping us with your knowledge. If I may ask, can you teach us or any ideal on "how to generate or create a licenses key/product key/serial number with an expiry date like (Non-Expiry, 6 months, 1 Year....etc) for your database. Maybe Offline or Online. I hope to hear from you.
Thank you once again for your time. Love you.
Steve, this video tells it all. I have been all over the internet trying to find someone to follow that can direct me the way that Relational Database Management Systems truly manage data. I have still some issues with Normalization and Relationships holding me back, but when i go back to videos that you produce like this one as an example, I am truly leaning this way again. This video seems to reduce the opportunity of outsider hacking substantially. I am pretty darn sure that there is not to too much more that you can do, to keep your users on a solid path in teaching them, that at that time to follow path that has been set out for them, and over time with some simple proof of service, they also can find themselves moving up the ladder and taking on more responsible roles over time. But the framework is in this video to keep your users within the scope of the roles set out and over time permissions or roles do change based on their history. So well done Steve, you are truly a master of disaster, prevention that is . . .
Thank you Steve. I've worked in Access several years, but this is the first time I've had to use VBA for my databases. Your videos have been very helpful.
Great job. Three ways to check password authentication very intuitive and advanced.
Thanks.
Very helpful Steve, I've learned a lot from you and i'm still learning you are my favorite VBA tutor ever. please keep it up and thanks a lot :)
+MRGhass Thank you so much for the kind words. I am glad you are enjoying the videos.
It would have taken me three lifetimes to do it. THANKYOU! GOD BLESS!
I saw just now your tutorial and I would thank you for your clarity, skill and competence in explain all the topics.
YOU ARE THE BEST... very helpful Steve
Good job Steve. I appreciate your willingness to share your talent. God bless!
thank you Steve for your tutoring videos. I really enjoy learning and putting your teachings to practice.. very, very helpful..
thanks again and keep up..
Nice to have you back! I look forward to your insightful videos.
atlantakick Thank you atlantakick. I'm glad I could get something up for you guys. It has been a while.
Thank you Steve, your tutoring is absolutely the best
It has been a great experience watching your videos. Am glad that I met someone of such wisdom that I am hungry for now. Thank you so much for been an agent of change in my life and future career. CONGRATS Steve
Such an amazing presentation, not to mention how helpful this is for the project I am working on, I had to sign up for a membership. Thank you so much.
Excellent Video, followed step by step and all worked as should, however I did find that if the form I have put the ‘On Load’ code on, is a subform. I first get the “You do not have assess” message box as I should but when I click OK, it opens anyway. Any suggestions on how to prevent this?
Thanks.. Steve. I always think you are the best for the Access programming teaching!!!!!
Big fan of your series and instructional style. Thank you for posting.
Hi Steve, your video is very helpful. I do have a question. While creating your tbl9EmployeeAccess, do you follow the basic database normalisation? I am asking because I would like to apply this procedure to many of my form and for each of them I will have to repeat 3 times the record and change only the HasAccess. Can we do it the same and follow the database normalisation? Thank you in advance!
Thank you very much for your tutorial Steve, I'm currently attempting to build a training database for my department and your videos are really helpful.
Hello, sorry for asking this dumb question here, may I know how to open the work file given? I keep getting [Run-time error 3044:
'c\user
inger\desktop\database\service inc_be.accdb' is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides] when trying to login from the fe file. Is there anything that I missed?
Thanks a lot Steve, you are great at sharing knowledge!
Question: It seems that TempVars will only be recognized until you close the access database. once you reopen it, the script will not work. what would I be able to use to have it there permanently?
You would need to store the value in a table. - Doesn't make too much sense for something related to the *current* user of your application.
Awesome help! Im only running into one problem if the form is set to MAX on open. The form opens, then the popup tells them they dont have access
I am looking for the coding needed to disable the Close Access button on my Access 2013 database. I have watched several of your tutorials and I love how simple you make learning the VBA coding. Could you possibly help me figure this out? I would really appreciate it.
Hello, we are 5 mutli-users on Access working on a front end database that connects to a back in database. Our company pushed out an auto update of MS Office 2013, and all of a sudden only 1 user can get in at a time; normally all users can be in working at the same time with no "Locking" in records. The error we get is that the '...backend accdb file is already in use'. Again, the program works fine for 1 user but nobody else can connect to the backend simultaneously. How do we fix this? Thank you.
Great video. Very useful and easy to follow. Great job!! Salute!
Thank you, Steve _ Great video. Very useful and easy to follow.
Hello Steve,
i downloaded the project files from GitHub, when i try to open the FE file it gives me the following error,
Run-time error '3244:
'C: the path is not valid
not a valid path. Make sure that the path name is spelled correctly and
hat you are connected to the server on which the file resides.
would you please help
Excellent content. Beautifully explained.
Hi Steve. Can all of this be done with for a command button that is used to fill a form text box? Example: I've created a UserAccess table with columns for; SecurityNo, ButtonName and HasAccess. I want to use the information on that table with the user login, to allow or deny the user to click the button to fill in the text box above with the Windows UserName. If not...message box "You do not have access!" will appear. Simple... For you... Access 2019, btw.
HELP ME, PLEASE!!!
+Carol Taylor The compare binary will work fine. Unfortunately there's not much you can do about the warning message.
I tried this but for some reason it is not blocking access to restricted forms.
In this section of code:
DLookup("HasAccess", "tbl9EmployeeAccess", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'")
If the "EmployeeType_ID" referencing the tbl9EmployeeAccess table or tbl2EmployeeTypes table?
Thanks.
Thanks. I did not know about the TempVars collection. I can see myself using it in the future.
Steve, you are a great teacher.
You are awesome dude ...
I watched your all videos earlier ..
And this one is very helpful..
Thank You Steve ..
I have found your videos to be exactly what I need at the time I need them. Thank you so much for what you do. I had lots of fun restricting users from forms, but would like some help (or point me to the video) to restrict form fields for those that should have read only access. I created that as an option in my UserType table (you might have called it employee type). When the form loads, would love for only those with read/write access (or higher) to be able to modify fields (change table values). THANKS AGAIN
There are several ways to address this. I've seen people use a field with a numeric value to hold the access level. 0 is no access, 1 is read access, and 2 is read/write access. Then just check the value for the user and the section in a case statement to determine what the control appears like. Hidden for 0, visible but locked for 1, and visible unlocked for 2.
Thank you Steve, it is very helpful and I have learned a lot for you
Good! I am so glad to hear it.
Hi, I wonder if anyone can help me. I think I missed a step. at 17.48, where You are talking about "TempVars" and using "EmployeeType_ID" I didn't see any of this (And much more of the code on this screen) in your "Live coding build a login screen".
Can anybody help?
very need! can we so something the same with tables, queries and macros? i really admire the efforts to make those clear session on the topic. i pickup many new technics. keep going. cheers.
It's very good
Please tell me it will be work with multiple user at same time?
Yes, it will. - Recommendation: Every user should have his own copy of the frontend file on his local computer.
Thank you, learned a lot. Like your mode and pace.
Thanks a lot Steve, big fan of ur videos
Q. Can we link another database with the main database and when the form is submitted save database in another database ( not updating the linked access)
Fantastic series, Steve. Much appreciated. Could this approach be used to Enable and /or disable multiple controls on a form? I currently have a form that different usertypes can access, but I only want 3 user types to be able to make changes to 3 of the controls on the form.
Yes. You can just set the controls enable property equal to the result of the check of the user access.
Will tempvars work like sessions variable in web environments?? So that moultiple users login at the same time with out mess up the values stored ??
What is the user name and password for the front end database? I want to access your code for the login form.
okay, one other questions. Due to having a lot of different forms that different groups will need to have access to, i'm trying to see if there's a way to create a short cut, through creating two separate "launch menus" that load after the user logs in, utilizing the employeetype_id thought. Admins with employee type 1 would go to "frm_launch" and users with limited access (employee Id =2) to pull reports and view read only tables would go to "frm_smllaunch". The code i was trying to use would go at the end of the vba for the login form, after adding and hiding employee type onto the form. I've run it, and regardless of what person i put in, all are going to frm_launch, even if their employee type is 2. Here's what i've done- is this anywhere along the route to success?
Thanks!
If rs!Password = Nz(Me.txtpassword, "") Then
txtempl = employeetype_ID
End If
If employeetype_ID = 1 Then
DoCmd.OpenForm "frm_launch"
DoCmd.Close acForm, Me.Name
ElseIf employeetype_ID = 2 Then
DoCmd.OpenForm "frm_smlaunch"
DoCmd.Close acForm, Me.Name
End If
Good Day Steve.
I recently came across your tutorials, which I have found very useful, and although I have a fair knowledge of Access i have not done a lot on Modules. I have just finished using your Login tutorial and everything works fine. I am now moving on to your How To Manage User Access tutorial.
Checking what I need in the Db I am using when I open the Modules in the Navigation pane I cannot see the module for the Login. If however I look at the Login Form and open the properties for the UserName and Password there they are.
Is this correct or have I done something wrong when creating the Module for the Login. Looking forward to your help ( with maybe many more questions to follow) I appreciate your time.
Regards
Mike
how does this work when you have multiple people logging into the same database? wouldn't that effect Tempvalues? i have one file location for a department or should i make links to new data bases that these members download to their desktop.
Very useful. However, the confusing part is that it seems you are bypassing Access work group security. Shouldn't we set permissions using the work group (group and user level security)? For example, when you use Current user() it returns Admin not id used to log in. Am I missing something?
Thanks
hi i need help. i dont have globals under the module. what can i do?
VErry good job! THX its helpfully!
Thank you Steve. Great Video again.
Thank you for video tutorials you posted. I found they are very informative and helped me to write vba code.
If I want to restrict user to only access subset of all records( e.g a user has access to one or multiple departments), can I replace object form as field name using this approach ? Thank you
You need a query that filters the records based on the department matching the Tempvar EmployeeType. (Assuming dept. and EmployeeType are the same)
Hey Steve, the video is a few years old but i hope you will answer my question nevertheless. If I split my database into front- and backend, the users see all connected tables in their frontend as well. Theoretically they can see so any informations from the tables, also the passwords of the other users. How can I prevent this? The other point I want to know is, how can I prevent that users simply write informations directly into the tables even if the equal formular is locked for them? How can I prevent, that the users can see the content of the connected tables? Thanks a lot! :)
Great video Steve! I am having trouble with my administration login section. When I log in as admin it doesn't really do anything different than the other profiles that I created. I might not fully understand it but I thought by turning the Bypass Key on that it is basically like opening the database while holding shift. Is this accurate? What exactly is turning the Bypass Key on do?
I wrote code to integrate my user table to an LDAP query function against our AD. This checks who they are logged in as and then only gets them what they have rights to see. No additional login prompt or password storage in an unsecure database. Great video though.
Ok you have used the codes to code the login form how about if someone who is familiar with those codes can they use the macros to do those codes made for the access to on the longing form instead of him/her typing in codes, please your advice is much needed.
Hi thanks for the video very helpful. You have another video explaining how to control what data (not form) can a person see and modify. For example sales representative can only see employees from sales department
hi steve thanks for the great video. I have tried your tutorial regarding creating a login screen and it did go well however when I tried to add this new code for managing user in my login form starting from tempvar it seems that other user except for the one i declared in "If rs!EmployeeType_ID = 1 Then" is not going through my log in page and also there is no error appearing on it. hope for your advice. thank you.
Awesome, Thanks very much man.
HI, is it possible to give them access, but so they can only view their own data and not other users.
Dear Steve , do you have tutorial for user session management? example there are 10 user with different privileges , there are logout button to destroy the session (or there are other efficient technique to attempt this). Each user can not view other user input list (each user can only view their own created records). All in ms access matter.
Hello Steve, Thank you so much, you are wonderful and helped so much. I do have one issue, on the Onload event, I have another piece of code that is filtering the form by Division … how do I add this other piece of code (user access) so they both worked. I did try Else but it did not work. I appreciate any help. Lisa
Fantastic information and thank you for sharing.
You are very welcome!
Hi Steve, i tried ur video step by step but i can't pass the login screen its say password wrong . what did i do wrong here need ur help? Thanks
Very informative and helpful. How can I go to the link to get your source code. Thanks
Thank you - your videos are very helpful.
Is there a way you can also control user access to tables and design view using this approach?
No. You should be making tables and design views inaccessible by disabling those features. If you watch my Programming In Access 2013 series, you will see how to do that in the later videos.
Hello Steve, I have follow your steps and a Compile error: Argument Not Optional appears highlighting the Logging Global after I login again. Will you know why?
No idea. Try comparing your code to the work files.
Hi , Thank you for your videos, it has proved to be very, helpful, I have a question
when i use this code
UserAccess = Nz(DLookup("Has_access", "tbl1_Employee_Access", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'"), False)
I get a runtime error 2471, Any suggestion what I may be doing wrong?
Great video, thank you!
Hi! I am really new to access, but I have made a database which is going to be used by ten people. So I would like to manage their access not only to the forms, but also to the tables. I have seen your video about splitting the database into back end and front end, but my database is not that big and I don't want to split it. So I would like to ask you is there another way to limit the access of the regular users, so they wouldn't be able to see some of the tables. Thank you. :)
Hi Maria
I would strongly suggest that you split the database. If you are trying to control access to portions of the database it really is the way to go. Also with that many users you will shortly have a situation where two users want access to the database at the same time. With a split database arrangement across a network this is easily accomplished.
This was a very helpful lesson. I'm wondering if the same user access functionality can be applied to a tabbed form, where all users could see the first tab (page), but only certain users can see the other tabs (pages). Thanks!
Yes. Create a function that returns a boolean. Specify the employee type that should have access in an array. Loop over the arrays of employee type, then return true or false for each page per employee type, A Select Case per employee type would be needed, then you specify the return value for each page. You would need global variables to hold the page boolean properties returned by the function (you can use the tempvars ("page Name") to hold the function return value. On the OnLoad event of the Main Form:
Page.visible = tempvars("pagename") and you should be good.
What would prevent users from changing "tbl9EmployeeAccess" and give themselves access?
You can hide navigation menu and prevent ordinary users to change information in the table. However, there are still chances that an advanced user may be able to change this information and get access to your database. That's why MS Access is not considered secure when it comes to controlling user access. Still you may find smart ways to secure your database. Google until you get a solution for you.
Great video, very helpful, thank you Steve
Best regards
janez novak Glad you enjoyed it janez!
Hi Steve, This video only restricted one particular form, what if I have additional buttons and forms that I would like to restrict. How can I accomplish that? Thank you.
Add it to the EmployeesAccess table.
Hello Steve
I wanted to know how to creat a bulk or group login
For example we have several sales groups or different companies are using the same app with multiple users at different security levels but all of them as a group can only see records associated with their company or group
I hope I was clear enough !
And thank you again I learned a lot from your videos
Guys , I am struggling to identify the symbol which looks like an arabic comma. My keyboard does not allow me to use such a symbol. Any suggestions
Thats great lesson , but i have two questions 1- when i have more than one form to each user 2- i want to have more than user access to the same form but with different accessibility
Is it easy to convert an access 2003 database to 2010 or later ? What do you do about security. If converting from 2003 to 2010 do you have to write a new front end for 2010 ? What about the back end ?
You can convert it but you will need to find and fix errors. You will not need to rewrite both fe and be files.
Instead of not opening a form, can I restrict access to a button?
@Tyrielle Cole, that code has to do with the login process, not the logging of activity.
Programming
I do not understand, what prevent user to change access restriction directly on table?
why findfirst and nomatch not work in my database??
any solution ..pls
Hello, again, Steve. Just wondering, If I have more lines of code to execute following the jump to and back from the globals.function "has access or not". Shouldn't there be an "exit sub" if the user is not permitted into the specific form? Seems like trying to open the off-limits form generates an error messages when those lines of code can't execute successfully after a 'no permission granted' log in. (the extra lines of code define the db/rs parameters for opening that form
Howard Ammerman I would need to know the specifics.
I'll try to explain better. On form load, the VBA code, per your video, calls a globals function that looks at a user-has-access-to-this-form verification table. If user does not have access/permission, then the on_load routine should stop running right away, right? So, an "exit sub" command should be invoked, immediately, right? Before any form controls' data is loaded.
The routine, without any user-requested 'corrections' being asked, ought to end.
My forms load with the declarations of what info the form should show, by defining what rs is: what table, etc. those come after the function returns it's result, outside the "if/then/else block.
Howard Ammerman I would say yes in the scenario you are suggesting. You would want to exit the sub if the user should not have authentication to open the form. An Exit Sub would jump out of the subroutine at that moment, and any code written after the Exit Sub would be skipped.
I think the code you demo should really be called by the Form_Open event as it has a Cancel parameter. If the user doesn't have permissions Set this to true and when Form_Load finished processing the form will not open and it wil pass control pack to the code that opened the form.
eg
Private Sub Form_Open(Cancel As Integer)
if not PermissionToOpen then
msgbox "You are not worthy to enter this form"
cancel = true
...
End Sub
Hi. I downloaded the example database you provided and I tried to login but I get an error message 3044. I'm using access 2013. Any idea how i can fix this?
The error I get is that I am unable to open the FE database since it is referencing a file location that is not on my machine. The 3044 tells me the file path it is trying to reference to and it appears to be from the author's machine.
Hi Steve thanks a lot, but how can i open the Front end database ? database ask me to log in, but i don't have any login id and password, please help me
Hello, downloaded your database for testing, getting error after wrong login: it gave me a path that not from me :) C:\Users\Ringer\Desktop\Database\Database\Service Inc_Be.acccdb. Im sure I dont start the accessfile on that path !! made folder and try to login in.. i get "Incorrect Password" And NO the "Password1"doesn`t work !!! please help with this
Never mind the password at the frontend = "password" !!!
Hi Steve, I'm learning mostly from your tutorials and I comparing to others in RUclips and you are the best! I want to be a member in your channel. Right now I'm working on this log Screen for my project Auto-email reminder in our office, kindly consider me because along the way, I do have some questions/issues which you might help me. Thank you so much. By the way where I can download this code?
Hi I think i missed a bit about TempVars("employeeType'). When I recreate the code you have in this video. The code on the Employee form gives me a compile error "TempVars sub or function not defined." There is no compile error on the login code. I feel that the tempvars code is not being stored any where and I need to declare it as a variable someplace outside the login code?
TempVars is a collection that is a part of the Access codebase. it isn't something you have to declare because it already exists. I'm not sure why you're getting this error.
is the "employeeType" in the tempvars a reference to something or is the name just a place holder? When i run through the code. And hover over the TempVars it says the value = NUll
I hate when i do this = I just decided to completely rewrite the code and I noticed i had an extra L in the dlookup and that was causing the error.
steve thanks a lot. loads of love to you
Hi sir , im form bangladesh , thanks for excellent video.
But i want user read,right,view, delete,add permission how to use please help me.
Hi, Love your tutorials! I am having an issue with the Employee Form coding.
If DLookup("HasAccess", "EmployeeAccessT", "EmployeeTypeID=" & TempVars("EmployeeType") & " AND FormName='" & Me.Name & "'") = False Then
This is resulting with an error:
Run-Time error'3075':
Syntax error (missing opererator) in query expression
'EmployeeTypeID= AND FormName='EmployeeF"
Any suggestions?
I realized that I had commented before. Since this comment I have went through the tutorial again and I got the login screen to work perfectly as you stated in the tutorial! Thank you!
Hello, I know that you added this comment forever ago, but can you explain what was wrong with your code? Im having the same issue, but am typing this exactly. I cannot figure out what is wrong with the code.
Great lesson! Question concerning the TempVars, I was able to reference a record set in another form but is there a way to store more than 1 record set? I'm trying to reference a few record sets within the same username. Thanks again!
+Tim Chavez Sure, just create another recordset variable and assign it the value of the other data you want stored.
Thanks for the reply! Would you be able to provide an example code. I tried creating another TempVars recordset and it stopped my login code username and password. Thanks!
You should not be assigning tempvars recordsets. There isn't any need to anyway. You can assign a specific value to a TempVars item, but you won't want to do the entire recordset.
very nice tricks. very useful video.
good one Steve it will helpfull
Hi Steve Nice, What if I wanted to restrict only a few controls on a form. For example some user may have view access, while others may have add, delete and ofcourse view access. How can I achieve this? Thank you in advance
Instead of the visible property, you can use the enabled property to disable a control, but still have it's contents visible.
Since this is not setting permissions at the table level, presumably users will have free reign to do as they see fit once they do open a form meaning edit, delete, add unless of course the form definitions prevent such. But what if we want some users to have the ability to edit and others just read? Wondered if you have addressed that anywhere. I am thinking expanding on the HasAccess to include other options such as AllowEdits, Allow Deletions, AllowAdditions. Obviously that complicates things especially if the user should have permission to do multiple options. I think I know how I would address it but seems ugly and perhaps you have a good idea that you have already covered.
+ShoppersCritiqueIntl You can make allow edits, and allow additions properties false for the entire form based on the value stored in the table for that type of user.
Hi Steve,
i watch this Video several times and try to use it in my DB, also im not using (If rs!Password Encrypt(Me.txtPassword) Then) im just using basic password (If rs!Password Me.txtPassword Then)
I have two problem
1-dose not make any different if i type password or not system accept and let me go to my DB, but if i type wrong password (Me.lblWrongPass.Visible = True) coming up.
how about if password is just the numbers?
2-for using your codes in VBA application which References i need and in which order (which on first .....)
3-when i used your exercise DB, if you not type anything in password box, Microsoft V.B. widow comes and saying (Run-time error '94':)(Invalid use of Null) ** this is error high lighted in code "If rs!Password Encrypt(Me.txtPassword) Then"
any help?
thank you
+Majid Akbary You need to wrap the me.txtPassword in an Nz Function.... so Nz(me.txtPassword). I mentioned this in the annotation of the video as well as the description since it was a mistake in the original video. As for the references, it entirely depends on what 3rd party libraries you are using. There is no way I can answer that for you. Lastly, the invalid use of null I'm not sure of. You will have to try to debug the code and figure out what it's trying to do.