Login Security w/ Macro Builder and Table

[benthamq]

Hello all,


Many thanks in advance for any help offered.

I am new to Access 2010 and I have really very little skill and no experience with it yet.

I am trying to construct a log-in for my database that will permit employees at our company to select their names from a drop down and enter a password.
I made a table with three fields - an autonumber primary key, a field with user IDs (the records will contain each employee name in this field) and finally a field with passwords (the records will contain each employee's password in this field).
I built a "login" form with a drop list so the employee can select his/her name and I used a query to the User ID field in the aforementioned table as a data source. Then I put a text box and a button beneath it. My intent is that the employee will select his/her name from the list box, then enter his/her password, then click the button, and finally either enter the database if his/her username and password are correct or get an error message.

I am trying to use the macro builder to create an on click event which will check that both the employee name selected in the drop list and the password typed correspond to the record in the table. It seems easy to use the OpenForm function to make the button just open the form but I get nothing but error messages when I try to build something that can test if the user name and password are correct.
How can this be accomplished in the macro builder? Is VBA necessary? At least at this point, VBA seems really too difficult to me so I am hopeful that it can be accomplished using the somewhat less daunting Macro builder.

On a related note, if I use this technique in conjunction with a password for the whole application and I hide all the controls and make it impossible for anyone except myself to edit anything, is this a realistic way to try to secure the database? Or is this laughably simplistic and even somebody with basic access skills will be able to get in and damage the database?
I realize a truly skillful malicious user will break anything a noob like myself can create so I have no illusions about creating something invincible - I just want to have a reasonable level of security for the database that will foil someone who does not have extreme computer skills.

I'm very regretful if this is a tedious or difficult question to answer; please note as I mentioned I am new to Access and there really seems to be a massive amount to learn. I search the forums but suspect I will continue to have many questions.

Again, many thanks for any help. I am very grateful.

[June7]

Most anyone with basic Access knowledge is aware that holding the shift key while opening a simple mdb/accdb file will override project settings and bypass almost any automation. The project will open fully exposed. Requires some sophisticated coding and/or publishing as an mde/accde file to prevent this. There is also something about workgroups. I tried helping someone with a project set up for workgroup security - it was very frustrating. I have not yet done any of this with any project.

I do have one project of split design, multiple users, running on a network. I take the view that if user logged in through the network I don't need to annoy them again with another username/password login. I grab their username from the network and compare to a table, if found the project proceeds to open. If not found then it is their first time in the db and they must provide initials (initials are used elsewhere in the project) and record is added to table. Grab username from network with Environ("UserName") - exactly like that, do not replace UserName with anything, this is a parameter of the Environ function.

I don't use macros (okay, I have two exceptions), only VBA.

[benthamq]

Thank you for your reply.

[dreday]

I have a database that does something similar. I use a data macro on the user table to "Look up a record" in the table and then return TRUE if the username/password combo are correct or FALSE if incorrect.

From your command button you can add an OnClick macro and tell it to run the data macro with the login credentials. Then program other events depending on the result. This won't solve the shift bypass issue, but it will ID the user for you. My database is published through web services so the shift deal doesn't affect me.

[benthamq]

Hello dreday,

Thanks. How do you do that?

I am trying to create the data macro from the table by going into the Macro builder by clicking the After Update macro button in the ribbon but I can't save it and run it from the command button on the form.
Should this macro contain just one action, "Look Up a Record" with just one argument, Look Up Record In (the table), or is the where condition argument also necessary and, if so, what should it be?

What is the conditional statement to determine if the user/password combo is true? I don't know how to write it.
Also, do you have to put that if statement in the data macro or does it go in the on click even of the command button on the form?

Thanks very much for your help.

[dreday]

Hello dreday,

Thanks. How do you do that?

I am trying to create the data macro from the table by going into the Macro builder by clicking the After Update macro button in the ribbon but I can't save it and run it from the command button on the form.
Should this macro contain just one action, "Look Up a Record" with just one argument, Look Up Record In (the table), or is the where condition argument also necessary and, if so, what should it be?

What is the conditional statement to determine if the user/password combo is true? I don't know how to write it.
Also, do you have to put that if statement in the data macro or does it go in the on click even of the command button on the form?

Thanks very much for your help.

No problem:

First, the event I use is in the "Named Macro" section, not the AfterUpdate one. You should be able to use the Lookup A Record command in there.

Second, you want to add 2 parameters, one for the username and one for the password.

Third, use an if statement to see if the password matches the one that was entered.

Last, set a return var to tell the calling code what happened.

I attached my code to the post. From the login screen, I have a button that calls the procedure (RunDataMacro) and sends the txtUser and txtPassword variables. It gets back either True or False depending on whether or not the password matches. Hope this helps!

[benthamq]

Thanks for your reply.

I think I am very close but I cannot get it to work. When I click the login button I put on the form I get the error, "Object or Class Does Not Support the Set of Events" Error 459.
I created the data macro exactly as you indicated minus the last two return variables since I don't think I need them right now - I only want to make sure that the username/password is correct.

For the On Click event of the command button on the login form I made, I put "Run Data Macro" and selected the data macro for the table and entered the two parameters. I set them to the values of the two text boxes which I put on the form for entry of username and password. After that I put an if statement to check the value of the data macro and, if true, it should open the navigation form I made.

I think the problem is I don't have the correct if statement after the data macro it to determine whether the value returned by the datamacro is true or false - my table is also called, tblUsers and I called the data macro mcrLogin so what I have immediately after calling the data macro is this:

If [tblUsers].[mcrLogin]="True" Then
Open Form etc.

I take it that is probably what is wrong? I tried just mcrLogin=True also but that does not work either.

Sorry for my tedious questions and thanks very much for your help.

[dreday]

Thanks for your reply.

I think I am very close but I cannot get it to work. When I click the login button I put on the form I get the error, "Object or Class Does Not Support the Set of Events" Error 459.
I created the data macro exactly as you indicated minus the last two return variables since I don't think I need them right now - I only want to make sure that the username/password is correct.

For the On Click event of the command button on the login form I made, I put "Run Data Macro" and selected the data macro for the table and entered the two parameters. I set them to the values of the two text boxes which I put on the form for entry of username and password. After that I put an if statement to check the value of the data macro and, if true, it should open the navigation form I made.

I think the problem is I don't have the correct if statement after the data macro it to determine whether the value returned by the datamacro is true or false - my table is also called, tblUsers and I called the data macro mcrLogin so what I have immediately after calling the data macro is this:

If [tblUsers].[mcrLogin]="True" Then
Open Form etc.

I take it that is probably what is wrong? I tried just mcrLogin=True also but that does not work either.

Sorry for my tedious questions and thanks very much for your help.

No worries, you need to use return vars since the macro itself doesn't pass any values back automatically like it would in vba.

Try this:
If [ReturnVars]![ValidUsers] = True Then .........

You can reference the return var by its name that is set in the datamacro

[benthamq]

Thanks very much.

I tried this and it worked - but just once!

I am at a total loss - I literally changed nothing and when I tried a second test I received the error message, "Object or Class Does not Support The Set of Events" (Error 459).

I rechecked both the data macro and the On Click macro for the command button on the login form and nothing changed, yet it refuses to work.

Do you have any idea what could cause this? How could running the Data macro and on click macro once mess the whole thing up?

For what it is worth, I noticed that before my first (and only successful test) the borders of the table tblUsers were a light blue - afterword they turned yellow. I don't know if this is reflective of the problem.

Thanks again.

[dreday]

Very strange, can you post your code? Here is mine from the calling button and the datamacro

[benthamq]

Something is very strange.
Both the Data Macro on tblUsers and the On Click event macro for the command button (cmdEnter) on the login form (frmLogin) are correct.

I had set up the conditional code in the cmdEnter macro to either open a navigation form (frmNav) If[ReturnVars]![ValidUser]=True (if the correct username and password were entered) or Else send up an error message if false. (The error message, "Please enter correct username and password.")

It was working correctly if the correct username/password were entered, but when I deliberately entered a wrong username/password to test the error message, it gave me error message 459, "Object or Class Does Not Support the Set of Events."

Apparently, the error message was "hanging" one of the macros. (?)

So I entered an ElseIf statement in the DataMacro to StopAllMacros if the username/password did not match.

And now it works great - the navigation form opens fine if the correct username/password are entered, and the error message pops up correctly if the wrong username/password are entered...unless I close and reopen the database, or if I do a compact and repair! The first time I try to use the login form again after the database is reopened, I get the error 459 again.

To get the form working again, all I have to do is rename any one of the objects on the login form. For example, I called the text box for username entry, "txtUsername" so if I rename it "txtUsernam", then change it back txtUsername (in order for the macro to work, since txtUsername is it's reference in the macro) the form then works fine. But, if I close it again, it fails yet again.

Attached is the code for each macro.

Do you have any ideas? It seems like I may have to abandon this login form idea.

In any case, thanks so much for your help. I would have made zero progress without it.
I think this may just be some unsolvable bug.

[dreday]

Yeah, all your code looks good to me. The only thing I can think of is the

"stop all macros"
"clear macro errors"
"On error goto next"

group might be interfering since there's technically no Next to go to. Anyway, let me know if you make any more progress or how it turns out.

[eki einstein]

I read the conversation you two because it has been a long time I wanted to learn for, to mr. Dreeday when I use the "rundatamacro" function, parameter option does not appear, why in your example, the parameters appear?, It says Update prameter but when clicked on would not expand. I create a login form with two textbox "user name" and "password" I put the macro on login button that will process user verification. thank you and sorry to bother sorry if i had bad english google help me translate this

[dreday]

I read the conversation you two because it has been a long time I wanted to learn for, to mr. Dreeday when I use the "rundatamacro" function, parameter option does not appear, why in your example, the parameters appear?, It says Update prameter but when clicked on would not expand. I create a login form with two textbox "user name" and "password" I put the macro on login button that will process user verification. thank you and sorry to bother sorry if i had bad english google help me translate this

The macro you are running must have Parameters defined. Then when you do the RunDataMacro command there should be a spot for them.

[eki einstein]

"the macro you are running must have parameters defined Then when you do the RunDataMacro command there should be a spot for them."

first :
Thank u for your reply i realy appriciate it ....

I'm new in macro ! ussualy i use VB!, but i won to learn macro !, so are u mean is "rundatamacro" function is to call other "macro" ?, because what i do in yesterday i just create new macro that run when clikced , and try to do like your pics

are your tutorial mean "create a macro that have a parameter defined" then call it with "RunDataMaro" ?
if yes... can you litle bit explain how to make macro whit a parameters defined..

Thanks