VBA login code for access - Allow user change 1

[aarondewberry]

RE: thread707-1201105

This thread is very helpful as it allows you to create a form in Access for users to login using a password.

Moving on from that, is there any additional code that I can use for another button, to allow users to change their password?

Thanks

 

[JerryKlmns]

Create also on the form, a textbox for the new password.
Create a new button with the following code behind it:

Private Sub cmdChangePassword_Click()

CurrentProject.Connection.Execute "Alter User " & Me.cboUserName.Value & " Password " & Me.txtPassword.Value & "  " & Me.txtNewPassword.Value
CurrentProjectConnection.Execute "UPDATE tblUsers Set [Password]='" & Me.txtNewPassword.Value & "' WHERE UserName='" & Me.cboUserName.Value & "'",,129
End Sub

 

[JerryKlmns]

Pls delete this line

CurrentProject.Connection.Execute "Alter User " & Me.cboUserName.Value & " Password " & Me.txtPassword.Value & "  " & Me.txtNewPassword.Value

 

[aarondewberry]

I tried running this code but it comes up with a run time error '424'

Object Required

 

[ItIsHardToProgram]

As low of programming that I know, when vba comes with an object required it is simply that you have not specified the right object name... When he asked you to copy paste his code, he did not mean for you to copy pastes the object names since they may not be the same.... replace cbousername with the name of ur own selectable username list, same goes for txtpassword and txtnewpassword, sheesh...

 

[ItIsHardToProgram]

Actually, come to think about it, I have a question about the password issue. I was wondering with what code you open the main form, and how do you lock the mainform so that no one sees it. I have also another problem... how do I compile my database into a database where the users can only see querrys and forms, and depending on the user, admins see everything and regulars see only those that are assigned to them.

Is that hard coding... ?

 

[JerryKlmns]

aarondewberry ,

I based my answer on Ed's post on the thread707-1201105 you mentioned above.

ItIsHardToProgram,

You can hide the tables, but anyone could unhide them. (Options, View tab, Show hidden objects). You can of course use a startup form to handle the log in and if succesful then show a switchboard (use the wizzard or build one of your own) for accessing other forms & queries. To make it harder you could desable the AllowByPassKey property so that even if a user presses the Shift key at start up, the database container wont show. You 'll also have to include code to allow that, for you to do your work. But if you are to have one or more users that should see everything give them the stripped version and let the others through the switchboard only. The next step is to set a database password and the ultimate step is user-level security. User level security handles the rights for groups/users to handle data (Read, Update, Insert, Delete) and database structure (Create, Modify, Delete, etc of objects).
BUT, keep in mind that all the above is ... crackable!

 

[aarondewberry]

Hi. My object names are called exactly the same as on Ed's post. It still keeps throwing up this object required error everytime. In the code don't you have to specify that the txtpassword has to be correct, it does include WHERE cboUserName is selected but nothing about the txtpassword.
Excuse my ignorance

Cheers

 

[JerryKlmns]

Access Version = 2000 or above
Table Name = tblUsers
Field for User Name = UserName
Data Type for User Name = Text
Field for User Password = Password
Data Type for User Password = Text
Form Control Name for User's Name = cboUserName
Type of Control for User's Name = ComboBox
Form Control Name for User's Password = txtPassword
Type of Control for User's Password = TextBox
Form Control Name for User's New Password = txtNewPassword
Type of Control for User's New Password = TextBox

CurrentProject.Connection.Execute "UPDATE tblUsers Set [Password]='" & Me.txtNewPassword.Value & "' WHERE ((UserName='" & Me.cboUserName.Value & "') And (Password='" & Me.txtPassword.Value & "'))", , 129

 

[aarondewberry]

Marvellous. Thanks very much Jerry. Works a treat

 

[ItIsHardToProgram]

Jerry, I think I get what you mean, but some questions remain... I am just starting to re-learn coding, its been a while, when it comes to showing or hiding tables I don't quite get which code to use, or is it a function in access.... usualy for object it would be .visible obviously. If you could enlighten me on that...

Also when you mention a form on startup, so far I don't know how to autoexecute a form from startup of the database, its probably very simple but I don't know the code.

I think from there I could probably figure out something... hopefully, for all the crackable mather, it is not a problem, I mentioned security but its really just for ergonomic usage.

One last question. Is it possible to only show Groups That have the database you want linked in it. ???? that would be the best thing for me really, and for the every one seeing the objects come to think about it it dosnt mather......... So What could be good is just hiding every object, and having 2 workgroup, 1 for employee, 1 for admins, and the workgroup for employees would be only accessible with a employee password, and the admins with a admin password, the twist would be to have data by employee, changing depending on the password... but ill get to that later... Thanks for your first answer though, gives me a good hint.

 

[ItIsHardToProgram]

Nevermind about autoexecuting a Form, I can load one on startup that would allow me to enter a username password, following the code mentioned earlier, all though, I don't know how to show or hide objects, nor how to use the username/password entered to do so.... Those are the questions remaining, thx.

 

[JerryKlmns]

No VBA to hide objects. Right click -> Properties check the Hidden!

 

[ItIsHardToProgram]

If you unable the right click though, theres a minimum security, which is just what I need...... right click and opitons disabled, unless some one builds in a macro to replace one already there I don't think they can see the hidden objects... am I right?

 

[02306080]

Hi all,

Ive used the change password code above and it works fine.
What does this part of the code actually do : CurrentProject.Connection.Execute

Also this code is very straight forward, how can I add validation into this? and possible a confirm new password text before submitting the change into the table.

This would be a massive help.

Thanks,
H

 

[JerryKlmns]

02306080,

When you connect to a database you use a connection string to open a connection object.
CurrentProject.Connection for Access 2000 or above uses the connection of the opened datbase you have.
The .Execute method executes an sql statement, a query or stored procedure.
CurrentProject.Connection.Execute strSQL, , 129

129 = 1 + 128 = adCmdText + adExecuteNoRecords
Option 129 evaluates the strSQL as: Text of an SQL statement + the action query to not return any records.

You could use the .Execute method to create and open a recordset like

Dim rst As ADODB.Reordset
Dim strSQL As String
strSQL = "SELECT DISTINCT Products FROM Orders GROUP BY Products"
Set rst = CurrentProject.Connection.Execute (strSQL), , 1

If you need validation:
First, check that the pair of UserID & Password provided is accepted
Second, create an extra textbox for rekeying the new password
Third, compare the two textboxes for the new password and if they match update the table with the new password!

Sorry for the late posting...

 

[02306080]

Jerry,

Thanks for the help, clears things up.

Do you have the code for the steps of validation you mentioned?
Will it be a serious of IF statements?

Any help would be appreciated.