35. VBA - Creating A Login Screen (Programming In Microsoft Access 2013) 🎓

[@ProgrammingMadeEZ]

Looking for DAILY news and commentary? 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: kzbin.info/door/eXAUvo5xxDY_b-lSknPC1A

[@kensimba7868]

Only geniuses can understand this. And Mr Steve Bishop Ur our leader. Ur really helping me in my developer career. God bless you mightily bro. Cheers.

[@stewartwilkinsonsnr]

Steve some told me the other day, "Think you are a little to old (68) to learn Access", my reply was, "You are never to old to learn, no matter the subject". With your videos that sure makes it easier, so thank you. I made myself a promise when I retire to find a subject to have as my goal, well it's Access and boy does it keep those little grey cells working. I wanted to start with a Login and so glad I found your channel. Will make the Login form, then start from your first video to learn Access by someone who knows his subject and translates the instructions so you understand. Kind Regards and Thank You. 💻⌨️

[@ProgrammingMadeEZ]

I am so glad to hear it!

[@sgit1]

These are terrific tutorials - you are very gifted in knowledge and presentation.

[@PatrickWolff-wh9oc]

Thank you very much for your excellent instructions, I am learning a lot of helpful things and I hope there will be many more videos from you

[@peterm5132]

I really appreciate you taking the time to make these awesome tutorials. I am doing some volunteer work for a non-profit organization and am building a client database and an attendance tracking system from scratch. These tutorials give me everything to get started (and fondly takes me back to the days when I coded in Fortran - haha). I've designed and built my database tables and now onto form design/build. Thank you so much! In return, is there anything I can do to help you out?

[@MrVennyG]

Hi Steve, Pretty good videos. One thing with combo boxes - always a good idea when setting the row source to grab data from a table, rather than setting up the Select statement in the combo box, create a query. This means the query is compiled, runs quickly and more responsive for the user.

[@seungminlee4971]

Thanks Steve, I got a new co-op on this field. and your videos are saving me so much

[@no_short_circuit]

Great stuff. Working my way through these videos and picking up a lot. Thanks...keep them coming

[@fahdbadr4715]

so simple and easy, thank you for this video, you are the best!!!

[@LucianoIanni90]

Thank you mate! but I realise that it´s not case sensitive. If you put the same password with or without a capital letter you can login in both cases. How can I fixed that?

[@jennpowell2346]

Awesome video, have been fighting with Dlookup all morning!! Thanks again!

[@ProgrammingMadeEZ]

You are welcome. Happy to hear it has helped.

[@x21tri5]

+Programming Very much

[@israelgodinho6027]

Hi, Steve, and thank you! But how can we make this login more secure? I mean, the user can go to the design mode and add a non-zero width to the password so they can see it. Or they can even go to the password table and see all the passwords lol

[@greencoder7419]

Please explain the difference between control source and row source

[@damasevich]

Hello, Mr. Bishop! Thank you for your videos! Could you explain, why should we use variables in this case? I think we could write down "If Me.cboUsername.Column(1) = Me.txtPassword then..." Will it run more slowly or there is some other reasons for using variables?

[@ProgrammingMadeEZ]

You can certainly look at the values directly and don't need a variable. Often times we use variables just to help make things easier to use with less typing. I believe in this case I just wanted to use variables to assist with demonstrating how they can be used.

[@aaakimov]

I had exactly the same question, thanks for rising this. And Mr. Bishop, thanks for your comment too!

[@peymanghaffari2744]

First of all, thank u so much for your tutorials Unfotunatelly, on this lesson, i've got this error: "Function call on left-hand side of assignment must return variant or object" What should i do?

[@lianapadron2239]

Excellent video! Thank you much.

[@firstlady5393]

Hi Steve- thank you so much for these great videos and lessons. I have learned a lot. Wondering if you can give insight on the following: Is it possible to create a daily auto link between a local MS database and web app? Then, Is it also possible to create a daily auto sync (or link) between web app and SharePoint? Any suggestions on how I would accomplish these automated links and syncs would be greatly appreciated?

[@asniceful]

Hi Steve, That's great job for sharing your knowledge. You said this logon screen is common. I was wondering if that doesn't reduce security since one can see all available usernames. Also will you recommend this for a large employee table considering big data and loading issues? Thanks once again.

[@ProgrammingMadeEZ]

+T. Mensah (Tomasm) It's not the most secure systems, but honestly Access isn't the most secure of platforms under most circumstances. You have to tweak it a lot to make it secure. I show you how to do this throughout this course and the advanced course, but 99 out of 100 databases don't take the steps necessary to properly secure them. Most developers of Access don't even disable the bypass key.

[@asniceful]

Hi Steve, Great to hear from you and thanks for your deep insights. I do access developments basically at my leisure times. I like the VBA coding because I'm not a hard core programmer. I develop Access for my church and also for a non-profit associations (Volunteering) and I am looking forward to advance my skills. I watched your videos on My SQL Server and I don't seem to get it very well. First, for the free SQL server how many users can use? 2. After sharing can I continue to develop it? 3. I am still a "'DAO' VBA guy" since it's easy to learn that one. I still use "set Db=CurrentDb" Will that work if I put my design codes on the Server? I would really appreciate hearing from you. Thanks PS: Kudos for your great work.

[@ProgrammingMadeEZ]

Hmmm.. I don't have any videos on MySQL, but I do have a few videos in my Advanced Access series on MS SQL Server. I recommend you watch that series because I do talk about how to connect to SQL Server using DAO and VBA.

[@asniceful]

Actually I meant MS SQL Server. Thanks a lot.

[@niyiomojoh6622]

Steve, this is great one.

[@PatrickWolff-wh9oc]

Found an interesting bug, there is no username validity check, so if you enter a different username in the combobox than is available in the list, vba crashes with the error: "illegal use of null". you would have to block at least the processing of the list entries in the combo box in order not to get into the debugger and thus avoid the query. this is a note and not a criticism. I followed the instructions and entered "jdoe" and "jadoe" as users in the table and hidden the table and the form so that the information cannot be easily read. In the options of the database I have now selected my login form under open form so that it appears immediately as soon as you open the db. here comes the gap, open the db, now you simply enter an invalid name in the combo box on the form, e.g. "haxor" and press enter and the macro crashes. I was able to fix this as follows. in the properties of the combo box under the Data tab: only list entries: yes Allow value list editing: no will inherit: yes show only record seed values: yes it is no longer possible to enter an invalid user name Best wishes

[@johnpence8105]

Not sure if you're still helping on this however, I follow your tutorial exactly. I get an error message "User-defined type not defined" when clicking submit form. I also don't get the suggestions box while typing [rs.FindFirst "UserName='" & Me.txtUserName & "'" ]

[@seaham69]

Great video! Helped me quite a bit, however, how do you apply case sensitivity when comparing the passwords?

[@ProgrammingMadeEZ]

+seaham69 Go to the properties of the password textbox, click on the "Other" tab and change "Allow Autocorrect" to no.

[@rohitharidas91]

+Programming I did this but the password isn't case sensitive. :(

[@ProgrammingMadeEZ]

I'm not sure what is wrong then.

[@58oldguy]

How does a user enter a password for the first time and how does a user change the password later on?

[@NotesAndSoft]

could you help me create history of books with append query, I have library database(which i am creating) , i want it to save history of the books i check in or checkout. And i saw your blog , i am a good blog designer , if you allow me i can help you in making it look better

[@ProgrammingMadeEZ]

Sure I can help you out NotesAndSoft. First, did you see my video on how to insert data into a table? kzbin.info/www/bejne/mIDRd4drnbWVjpo

[@IoDavide1]

How you deal with the security confirmation? If access ask for this, the ok button is behind the login form and you cannot click on it

[@changqunzhang1277]

Howdy, Steve, thanks for your great illustration. I mimic the log in form as you did on MA Access 2010. Here are two issues I countered. 1. even I set the property of both loginfrm and table stores username and pins to snapshot of recordset type. The username and pin seems being changed after login few times. Have this problem happened to you? 2. I noticed that you did not apply the query to the whole login from, but select the from row source. The row source in MS Access seems behavior a bite different. can I apply the data to the whole form? It will work, but I have the issue as stated in the 1

[@weibinren92]

Hi, what would be the difference between Me.txtPassword and Me.txtPassword.Value?

[@marcinofsky]

Verry usefull!

[@jooryalhamed938]

thank you a lot you are so smart

[@weilunn1252]

Hi Steve, thanks for the video. It's great. May I ask How do we determine the value for that Column Count?

[@weilunn1252]

Thank you Steve

[@ProgrammingMadeEZ]

It's 0 based. 0 is the first column, 1 is the second.. etc.

[@cybertjc]

Steve, working from lesson 35 (combo box login) how do you have it open forms for the individual users?

[@stephenguan9443]

nice skill

[@MrSpark05]

Hello again, could you pls tell me how to always open a form in center of screen. Form-Autocenter property is set to Yes, but doesn't work. I use docmd.maximize command. thank you.

[@ProgrammingMadeEZ]

MrSpark05 I would start here: access.mvps.org/access/forms/frm0042.htm

[@MrSpark05]

Programming thank you

[@tabunsandar2894]

How to change icon

[@luisalorimer9250]

Hi Steve, how do I manage that the logged in user is automatically shown in my form in a text box? (Hope this chat is still alive) Cheers L.

[@laithhameedalazawy]

this video is great Can I ask how to calculate the working Days and holidays and business days and calender days Regards

[@ProgrammingMadeEZ]

That isn't something that can really be explained in one video or in a comment. I suggest you go here: access.mvps.org/access/datetime/date0006.htm You'll need to make a table to store all of your holidays and use the VBA code mentioned there to do calculations. That should get you started.

[@beneaton8375]

Hi. I have created my login screen however I was wondering what are the next steps so if the login is successful to direct the user to another form?

[@ProgrammingMadeEZ]

+Ben Eaton DoCmd.Open "formname"

[@jamesoooojames2575]

+Programming where do i add this command ?

[@jamesoooojames2575]

+Programming Never mind figured it out ,thanks

[@jamesoooojames2575]

+Ben Eaton Add : DoCmd.OpenForm "name of the form that you want to open" after MsgBox "Login Successful!" and before Else

[@danielbrekkelarsen8682]

Hi guys got one problem if i dont fill out the text field and try to hit enter i get invalid use of null as msg, is it anyway to make that message go away?

[@mmgbtv]

Thanks a lot for the videos. So what if the user hits enter with a null Value? I get a debugging error because of null values.

[@elvinrashidzade8626]

Dear Steve, I want to use ə (schwa) character while writing msgbox message on Code Builder. Another problem is while I change fonts on code Builder from tools>options>editor format. It change on Code builder but stays same on form. Like ş ü characters. If you help me to handle this problem I will be really glad. Thanks a lot

[@TheChrillexx]

Hey, thank you for this Video but I have a Question. First: I am working on a membership administration of Sports Club. I dont have a table "Employees" yet. Would you recommend to build one, or could I just create a new table "Users" that doesnt relate to my main table "Member"?

[@ProgrammingMadeEZ]

+The Chrillexx You can use whatever table you've got that stores the user account information with their password. If you call it Users, then that's the table you would want to use.

[@artsafari8157]

Thank you! This is great!

[@BrianGivensYtube]

How do I make it so the login screen forces itself in before any other access is allowed to the software?

[@vilakshangupta3200]

SUUUUIIIIIII

[@MrSpark05]

Nice, easily understandable. Thank you. But how to give logic to the button? Thank you in advance!

[@ProgrammingMadeEZ]

Not sure what you mean? What kind of logic?

[@MrSpark05]

Programming Sorry for not being clear... I meant how to make login button open up a new window after login, but now I am watching another video of yours (Classic login form) and it seems that it will give an answer for my question.

[@ProgrammingMadeEZ]

MrSpark05 No problem. Yeah I do go over the docmd.openform function in the classic login video. You can do the same thing with this login button.

[@MrSpark05]

Programming Thank you.

[@SpecOpsFerret]

I am getting "Run-time error '94' Invalid use of Null" when a password or name is left blank. Any suggestions? I had it working before but now it does not, which is right after adding another column to a combo box, but unless I am missing something, that should not have affected anything because I changed my column count and widths and the added column is at the end

[@ProgrammingMadeEZ]

Check your "Bound Column" number for the combo box. Then look at your query used as the "Row Source". Whichever column number you have in "Bound Column" will be the column from your Row Source that will be returned when you make a selection. Make sure that column is the one you want and that it is of the right value type.

[@SpecOpsFerret]

It was my bound column. I had it set to 1 when it should have been set to 0. Thank you.

[@ramirocarvalho69]

Bound column set to 0, still returns the same error...

[@ProgrammingMadeEZ]

Ramiro Carvalho A Bound Column of 0 won't work because it is not a zero base property. It starts at column 1.

[@vicenteferrara7626]

Hey Steve! Do your videos series cover something about maskedtextbox? I´ve looked in the Web to find something related with Access but there is none. Do you know how to use it?

[@ProgrammingMadeEZ]

Vicente Ferrara I don't dig into masks much. I just use the defaults.

[@mugijamankey8780]

Can u tell me how to creat relation between tables?

[@enseig_rech2780]

Hi. Thank you for all your videos. In my application i use a login form then i close it and use other forms and requests. How can i use a parameter, how and where to declare it (this parameter is for example the user ID) if i want to filter data depending on who is a user. Thanks

[@ProgrammingMadeEZ]

+MK F You'll want to use VBA code to do this by setting the recordsource to a SQL query with some parameters. I discuss this in the Advanced series a bit more.

[@enseig_rech2780]

+Programming Thank you for your answer. My problem is precisely : before i quit a login form i read a User_ID and want to save it as a parameter. After this i open a navigation form witch include some buttons. The navigation form open automatiquely the first form corresponding to a first control button (automatiquely without clicking any button) and use a request. I dont know how i can use my parameter to filter this request for this automatiquely opened form. If i must to use a query builder, i don't know how to insert my parameter by using builder. This builder use values of controls but not a general variables or parameters.Thanks

[@nyxviliana]

not sure if you are still monitoring this, I have completed a slight variation of your other login tutorial. my problem is I have 3 levels of account access. One is required activation (default value on new accounts), people can make their own accounts but if they try to log in before its activated they would get a message saying "requires activation" One is Active (no message displays they have full access. and the last is suspended, and again if they try and log in they would get a msgbox saying error account suspended. I have done this before, but I have forgotten the code for the back end and cant find my old database I had used, was wondering if you could point me in the right direction

[@ProgrammingMadeEZ]

Samuel the Terrible Certainly. I actually did a video on how to manage user access that might help. kzbin.info/www/bejne/eqm1oaJqbpuqlcU

[@ApolloDSouza]

Hi Mr. Bishop. I am absolutely new to coding. I copied your code for creating a traditional login. It works. However I have users that are "admin" and users that are "users". I need to direct users to a form that is a data entry form. Please help me. Thanks in advance

[@ProgrammingMadeEZ]

I suggest you continue watching the series so you can learn more about how to code this yourself, but there is a how to video on user access if you go to my main channel page.

[@eyribalt99]

Great stuff. Got a donate link?

[@eyribalt99]

Nevermind, I'm blind lol. Coffee link see it now.

[@paolorecasas]

How do you pass values of the logged in or current user to other forms? For example, after the login screen, there will be a navigation form with different tabs, on the first tab of the navigation form, or the default tab to be displayed first, I want to display, "Current User" is logged on. I have been trying to do it, it always display the first record or user on the table. Thank you for the videos, very useful for starters like me.

[@ProgrammingMadeEZ]

There are several different ways you could do this, but probably the most straightforward is to use TempVars. It's a unique collection in Access they specifically created to store data like this. To use it just, here is how you add a new value: TempVars.add "CurrentName", "Name Goes Here" Then to retrieve the value just use TempVars("CurrentName") or TempVars![CurrentName] msdn.microsoft.com/en-us/library/office/ff821458(v=office.15).aspx

[@SpecOpsFerret]

Programming Thank you. In VBA or macro creation, how would you reference a navigation control button with the purposes of locking/unlocking it based on this temp var's value? In other words, what would be the control name I would use in a SetProperty macro action if my form is Main, the navigation control is navctrl and the navigation button is navAdvanced?

[@ProgrammingMadeEZ]

Jack Outside the Box If tempvars("UserLevel") > 2 Then me.control.enabled = true

[@SpecOpsFerret]

Programming I was making this way too complicated it appears. Thank you very much.

[@ProgrammingMadeEZ]

Jack Outside the Box This is not an uncommon problem among smart people. :D

[@sandeshghimire3028]

Hello sir, how can we go to the main page/next page automatically after login is successful _ ??????

[@denissmajlovic4644]

Very good video but I suggest one change...Nz(Me.txtPassword) - for case if somebody try log in without password...

[@ProgrammingMadeEZ]

Yup. I did make that mistake.

[@GlentonJelbert]

Normally, passwords are put through a hash before they are stored, and compared, so that the password is never stored in plaintext! So you compare the hash of the password with the stored hash, rather than the password directly...

[@ProgrammingMadeEZ]

That is correct which is why in video 60. Hashing Passwords (Programming In Access 2013) I explain that process. Good call out.

[@JavoNielsen]

I have a question if you can help me that is great, I followed your instructions, but what if the user want a blank password or hit enter with a blank password? since it is a string, and strings cannot be null I am getting the debugger pop up. thanks

[@ProgrammingMadeEZ]

I fixed this in the code for video 56.

[@JavoNielsen]

Good, I saw you had that video, but I didn't saw it all. thanks for your help

[@dylanfan1941]

Hi. I successfully got my combo box to go to my form and pull the user names and passwords. My problem is, in the combo box display when you use the pull down arrow, it lists the user names, but they are preceded by USERNAME. How can I get it to leave off the extra stuff? Thanks!

[@ProgrammingMadeEZ]

That must mean that data is part of the data in your table. You should fix what's going into your table rather than to write extra code that takes that part out.

[@dylanfan1941]

Programming I re-did the columns on my UserT. I changed the username and password columns to datatype "short text" and that seems to have solved the problem. Thanks!

[@ProgrammingMadeEZ]

dylanfan1941 Glad to hear it!

[@philipmthompson]

I want to incorporate different groups (Admin, user, manager), and based on their assigned group, open up a different form from the login form. Any ideas on the best approach?

[@ProgrammingMadeEZ]

Philip Thompson Take a look at my how to manage user access video I just posted (kzbin.info/www/bejne/eqm1oaJqbpuqlcU). It doesn't answer your question directly but it should give you the right idea. After someone clicks on the login button you can do a case statement to determine based on their EmployeeType ID which form to load.

[@geraldjansen7086]

Great technique! less code than other examples.

[@CheylaMoranchel]

I was able to follow this very smoothly but at the end after putting in the code, no message came up after pushing enter or "login"

[@ProgrammingMadeEZ]

You need to use DoCmd.OpenForm ""

[@moniquewynter1613]

Hello when I follow the code I get a error message Compile error: invalid outside procedure

[@fjfrancus8396]

Hello Steve, how can I display the login name in any of the form?

[@ProgrammingMadeEZ]

After a successful login store the username to the TempVars collection. Then you can refer to it from anywhere in the application.

[@samadCoorg]

Hello Sir... i have created VBA but password is appearing in username column..any help

[@ProgrammingMadeEZ]

Samad Ummer Can you be more specific?

[@muneeralhassan4724]

if user clicks on login without entering password and user name error will emerge (error 94)

[@unown_]

I keep getting Compile error: Argument not optional on the msgbox

[@unown_]

Kevin Vugts that is probably because you used spoof hacks once. Spoofing makes your pc do weird things so probably that’s why acces won’t work properly.

[@unown_]

Kevin Vugts English please

[@moniquewynter1613]

Can someone please help me

[@choosetolivefree]

FYI, displaying everybody's usernames to everyone is a terrible security practice.

[@sixana7056]

It's giving me an error 😭😭😭😭

[@pursephone1]

how the hell does this walk me through making it you already have the freaking table set up this is total crap

[@MuhammadAdnan-ws7yy]

thank you sooo much sir

[@ProgrammingMadeEZ]

You're welcome!