pass

[alsa]

Hi,
I would appreciate help in order to find solution as follows:
Namely, by using ADO I connected Excel to Access database. Also, using ADO I made a worksheet activate VBA code that requests entering of Username and Password reffering to data in Users table (my created table containing Usernames and passwords) in MS Access (e.g. depending of entered Username and password combobox is loaded with specific data).
Please find bellow part of VBA code related to Username and password:


username = Application.InputBox("Enter your username: ")
rs.Open ("SELECT password as pass , gf , tc, tc1, tc2 , tc3, tc4, tc5 from users where username = '" & username & "'"), cnt
If (rs.EOF = True Or rs.BOF = True) Then
MsgBox "No User defined", vbOKOnly, "Error"
Sheet6.Activate
Exit Sub
End If
password = Application.InputBox("Enter your password: ")
If password = rs!pass Then
If rs!gf > 55 < 1000 Then
Sheet1.Activate
Call Me.load_combo2(rs!tc, rs!tc1)
End If

Currently, this work very fine. In order to improve the same I need help for the following:
1. I would like to adjust above VBA in order to mask entered password )e.g. Currently if I enter xyz I see xyz.... instead of that i would like to truncked xyz with for instance ***)
2. I would like to provide users with posibility to change password by themself (this mean that somehow I have to upgrade users - column with defined passwords table in MS access)
3. I would like to have posibility to define time frame for password exceed for e.g. 30 days

Thanks in advance for time and effort for helping me.
Regards,
Alsa:yes

[CreganTur]

Welcome to the forum- it's always great to see new members!

In the future, please wrap all VBA code in VBA tags- click on the green VBA button. This will format it according to VBIDE and make it easier to read.


1. I would like to adjust above VBA in order to mask entered password )e.g. Currently if I enter xyz I see xyz.... instead of that i would like to truncked xyz with for instance ***)

Open your UserForm in design view and click on the textbox where your users enter their password. Open the property sheet and look for the PasswordChar property- use this to set the placeholder you want.


2. I would like to provide users with posibility to change password by themself (this mean that somehow I have to upgrade users - column with defined passwords table in MS access)

First I'd suggest having another UserForm to handle this. Give the User a change password button on the login screen that would bring up the change password userform. For validation purposes I would have the user enter their current password, and then have a "Enter new password" and "Re-enter new password" textbox. You would compare the strings from these two textboxes to make sure they are the same, and then run an Update query via ADO against your Users table.


3. I would like to have posibility to define time frame for password exceed for e.g. 30 days
You could add a new field to your Users table that holds the date when the password was created. Then get the difference between that date and the current date. If that difference is greater than a set amount a messagebox would fire, telling the user to change their password. When they change their password, have the date update as well.

HTH:thumb

[alsa]

Hello,
Thank you for your reply. Please could you just confirm that I understood corectly (regarding 1st issue)
I need to create new UserForm and use the same one instead of Inputbox.
If that so, I allready created the one, and assigned * in PasswordChar (properties). Now, I would very appreciate if you could help me to inplement this newly created UserForm1 in previously enclosed VBA code.

Thank you in advance.
Alsa

[CreganTur]

Hello,
Thank you for your reply. Please could you just confirm that I understood corectly (regarding 1st issue)
I need to create new UserForm and use the same one instead of Inputbox.
If that so, I allready created the one, and assigned * in PasswordChar (properties). Now, I would very appreciate if you could help me to inplement this newly created UserForm1 in previously enclosed VBA code.

Thank you in advance.
Alsa

For issue #1- you do not need to make a new userform. Use your existing Login UserForm and change the PasswordChar property. You can use your existing login screen- you said it already worked, you just wanted to use placeholders so the password didn't show.

[alsa]

Hello again,
I have just small problem. Here is an amendment to my original VBA code. Here I tried to introduce value of textbox1 as reference for password checking.
[password = UserForm1.TextBox1.Value].

Unfortunatelly it seems that textbox is recognized as blank field since form is not activated at all. Please If you could Just help me in order to define a line of code which will activate Userform and provide users to enter their password in textbox.

Thank you

[CreganTur]

Unfortunatelly it seems that textbox is recognized as blank field since form is not activated at all.
Your code for this should be behind your login UserForm. Then you can capture the value of the textbox directly using: password = Me.TextBox1 Me refers to the current, active UserForm. Also Me.TextBox1 does the exact same thing as Me.TextBox1.Value.

You should never use the default name of an object- it's very bad practice and can cause you a lot of problems if you come back to this application at a later date. You need to give your objects meaningful names. For example, if TextBox1 is your password textbox give it a descriptive name, like txtPword, or txtPassword, or something like that- so you know what it is and what it's used for. In a month's time TextBox1 will probably be meaningless for you and you'll have to work to remember what the object is and why it's there.

[alsa]

Hello,
What did you mean by
"Your code for this should be behind your login UserForm".
Ok I understand and see your point of using me. Unfortunatelly I still don't follow your idea how to make this working...could you describe me in more details how would you make solution and what amendmends and where should be made in existing VBA code
I really appreciate and thank you once more for your time
Thank you....

[alsa]

Sorry one more time...
The whole confusion to me is made because in order to enter a password I am using Inputbox for which I think is not possible to define PasswordChar at all. That is why I would like to introduce a separate Userform with textbox for entering a password but don't know how to integrate in existing VBA code.
Please If you can consider to help me once more reffering to above mentioned .

Thank you very much...
Regards

[CreganTur]

Here's an idea for you to play around with- see if you can make it work. If you still have problems I'll provide you with the code tomorrow (will be leaving for the day soon).

For your login UserForm you need at least 2 textboxes (one for UserName, one for Password), and a button. Name the button Login or Submit, or something similar. Your user will click this button to try and login with the provided credentials.

You're going to put your code behind the button's OnClick event. Fill some variables with the values of your textboxes, and then use these variables as a part of your recordset's SQL, but I suggest using both the Username and Password as part of the WHERE clause. That way you are testing both against the database at the same time. If there is an error you can display a generic "UserName or Password is incorrect" message.

[alsa]

Hello,
I followd your sugginestions and created an UserForm which is show on Worksheet activated. Unfortunatelly I have a problem with command button (named Submit). I receive error 461 Compile Error - Method or data member not found. This appears in line code when it comes to Call load_combo Sub. Please find bellow adjusted VBA code for clarification:

username = Me.txt_UserName
rs.Open ("SELECT password as pass , gf , tc, tc1, tc2 , tc3, tc4, tc5 from users where username = '" & username & "'"), cnt
If (rs.EOF = True Or rs.BOF = True) Then
MsgBox "No User", vbOKOnly, "Error"
Sheet6.Activate
Exit Sub
End If
password = Me.txt_password
If password = rs!pass Then
If rs!gf > 55 < 1000 Then
Sheet1.Activate
Call Me.napuni_combo2(rs!tc, rs!tc1, rs!tc2, rs!tc3, rs!tc4, rs!tc5)
End If

If rs!gf < 55 Then
Sheet1.Activate
Call Me.napuni_combo(rs!gf)
End If

If rs!gf > 1000 Then
Call Me.napuni_combo3(rs!gf)
End If

Else
MsgBox "Incorrect Password, try again!", , "Login"


Maybe would be great if you could provide me with your code in order to have solution finaly instead of adjust the above one.

Thank you very much in advance.
Regards.

[Bob Phillips]

Here is a quick example that I knocked up