Logging on, VBA and modifying queries
Goal - Create a log on form for the user
In the real world databases are not let open for anyone to have a look at.
With the school database it might not respect privacy if that information was available to everyone. So usually we have usernames and passwords and we protect the users data. We are going to protect both databases with a Security log in page |
Creating a user table
For the school database, we want administrator access.
To set this up we want to create a user database with the following fields: FirstName, LastName, UserName, Password, Email, Make sure you set an input mask for passwords so they don't appear on screen. Add the following users Sarah, Martin, missmartin, miss, [email protected], ; Sally,Camel, sallyadmin,password,[email protected],; |
Task: Use the form wizard to make a form to manage the users
Make sure a button exists in the main menu to manage this. |
Create your log on form
Task: Using a blank form create a form like the one on the right.
Ensure you set a password input mask on the password. Ensure you call the labels lblErrorNoUser and lblErrorWrongPass Make the form a pop out menu with no controls and record selectors Task: Create the close database button with a prompt to ask if you are sure 1.) Click on the close database button in design view. 2.) go to the Event Tab and click on the three dots next to On Click 3.) Click code builder. In the code builder it will have setup a method (or function) to occur when you hit the button it will look like this: To make a message box you enter this code:
MsgBox("are you sure you want to quit?", vbYesNo, "Are you Sure?") We can turn it into the if statement adding these in If, = vbYes, then, End If and we can quit the database with this command: DoCmd.quit Save your database before you try it. Task: Check the database for the correct username 1.) Hide the error labels by setting the visibility to false 2.) Go into the code builder for the log in button We now need to check through the database to see if the name entered is the same as one in the database. We code this with Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly) rs.FindFirst "UserName='" & Me.txtUserName & "'" Red text indicates code you have to change in order to match your database. After this we can put in this code to check to see if their is a match: If rs.NoMatch = True Then End If 3.) Using the code provided and the picture on the right make it so the program can detect whether a user exists. 4.) test it on all users. Checking for the correct password: The code rs.findfirst goes through the table and selects the record that matches. Now that the right record is selected we can simply use this code to check if the password matches: If rs!Password <> Me.txtPassword Then <> stands for "does not equal" Finishing Tasks: Turn Modal mode on (prevents users touching any background programs while form is open). Get log off button working from main database |
Task: Make it so that if the user name and password are correct then it opens the main menu.
You will need this:
Docmd.openForm "frmMainMenu" DoCmd.Close acForm, "frmLogin" |
Opening with the login form
To make the Access database open with the log in form only do the following
1.) Click File --> Options 2.) Go to the "Current Database" tab 3.) Change the display form to the login form 4.) Save and close the database 5.) Open it up again to see what happens If you experience any issues and wish to bypass this simply hold shift when opening up the database |
Qry - Delete Records
Warning - Create a backup before continuing with this task
The database owner wants to ensure that you can delete records of students that are not enrolled with the click of a button. This is where we use queries, we can use queries to not only search for records, but we can also use them to delete records. Task - Create a delete query to delete the currently viewed records from the manage students form. 1.) BACK UP YOUR DATABASE (You don't want to have to reinsert your records because you last data) 2.) Copy the qryStudents and rename it qryDeleteStudents 3.) Change the type of qry to "delete" 4.) Add one more field to tell the query which table to delete the records from (See picture below) tblStudents.* tableStudents From |
You can turn off the warning with this command
DoCmd.SetWarnings False But be sure to turn it back on afterwards. Task - Link the query to a button
Create a button that says "Delete currently viewed data" Program it with this: DoCmd.OpenQuery "qryDeleteStudents" To remove the error messages type this in: DoCmd.SetWarnings False DoCmd.SetWarnings True |
Query - Creating a User
If rs.NoMatch = True ThenThe school administrators should be able to create new users.
They also shouldn't have access to other user accounts. So we are going to create a form that simply adds a new user. Task: create the form pictured on the right. Hide the red text. Keep in mind there is an error message for "user name is already in use Task: Create Error Checking Code You will need the following If IsNull(txtFirstName) = True Then ----------------------------------------------------------- Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly) rs.FindFirst "UserName='" & Me.txtUserName & "'" If rs.NoMatch = False Then ---------------------------------------------------------------------------------- If Me.txtPassword <> Me.txtPassword2 Then Extra for Experts: Make it so the error messages will all clear when create user is it, so only one error is showing at a time. |
Query Append
We can use Queries not only to delete and view, but we can use them to modify and update.
Task: Create a query that inserts a record into the student database 1.) First create a query but change it to append 2.) Select the table you want to enter the data into 3.) For each field enter expr1: "Bob" and expr2: "Jones" 4.) Append to the field you want it appended to 5.) Click Run Fixing the "multiple records" Problem
Click on SQL view
Delete the last line that says "From Users" |
Task 2: Link the query to your form
1.) Instead of writing expr1: "bob" you can use the query builder (like for searches to link the query to a form text box. 2.) in your form you can write this code to get it to run the query: DoCmd.OpenQuery "qryInsert" 3.) To get rid of the pop up messages you can write this either side: DoCmd.SetWarnings False DoCmd.SetWarnings True |