login form with password expiration in 30 days

[lacampeona]

Hello

I have a login form and change password form with securtiy questions.

Now I am trying to make a password expiration in 30 days from the last setting the password date.
I find some code on internet but I dont know how to make it all together. I manage to open the change password form but is not working like has to work. It open always no metter if passes 30 days or 1 day.

Can somebody help me how to write some code to set password expired in 30 days? I will attach all code that i have for my login form. Can somebody help we with what field i have to insert in the table...what type and where in the code i have to put the code for expiry date? Thank you

I browse a lot on internet and I know that I have to put some new field in the table let say like LastPasswordDate. But here i dont know how to manage to access write the last date into this field. i try with then input now(), but is not working like I want.

I have a table (tblUser) with 11 fields.
1. UserID
2. UserName
3. UserLogin
4. Password
5. UserSecurity
6. Question1
7. Answer1
8. Question2
9. Answer2
10. Question3
11. Answer3
12. LastPasswrdDate? Date/Time?

 

[isladogs]

See this example app Password login with session login recording

Your table isn't normalised. I would strongly advise you to remove the Question and Answer fields to a separate table with field ItemNo, Question, Answer. Each user would then have 3 records for those items

Also, storing passwords is generally not advisable due to security issues. if you really MUST store the passwords, make sure you use strong encryption such as that in my example app.

 

[lacampeona]

Hello
thank for your advice. i will try that.
about the sample db , yes i found that example yesterday and i get errors. WHen i change the password the password is not reset. it says me that i have to put again the password and in the table password the filed has some strange letters,,,

if you have time can you explain me more about the sample db that you send me....if i want to have text field i have to change cboUser wit text field?

 

[isladogs]

Sorry. I'm busy all day today decorating.... Just about to start
The strange letters are the encryption. Do NOT edit passwords using the table.
You MUST set / change passwords using the form as that handles the encryption/decryption automatically.

You can change the combo to a textbox if you wish. That is discussed somewhere towards the end of the thread from memory.

If errors persist, try and give the exact errors you get.
When they happen and what the error is in each case. Also state which version/bitness of Access you have.

The app has been downloaded by a large number of users and definitely works
Suggest you download a fresh copy and play with it,making sure you understand it before trying to copy the code

 

[lacampeona]

Yes i downloaded the last version from 05 january 2020. i am playing with the code all day yesterday and today ...i get up 7 clock...becouse i was conficed that i will change everything so easy... . this login is perfect..is what i am looking and to make already 1 month...and i know i will need more time to be perfect...
i use ms access 2016.. i get some error when i want to set the new password to the new user

 

[isladogs]

Hi
Having a short coffee break.

I can't read the error message (out of interest, which language is that?) - perhaps you could paste it into Google Translate
However it may be an issue with your local date settings which appear to have a space after each dot separator.
Error 3075 occurs when there is an issue with a query or SQL statement

 

[lacampeona]

Hello is slovenian language, europe Slovenia
it says
a syntax error in the query expression and then the date format is writen bellow 02.16.2020

if is local date settings error how we can fixt that?
thank you

 

[isladogs]

It should cope perfectly with a date format mm.dd.yyyy but not if there are spaces mm. dd. yyyy which appeared to be the case in your screenshot.

As a test, please enter the following in the immediate window and press enter after each
?Date
?#16/02/2020#
?#02.16.2020#
?02. 16. 2020#
Then copy and paste the results here

 

[lacampeona]

Hello

?Date
16. 02. 2020

?#16/02/2020#
16. 02. 2020

?#2.16.2020#
i get error...missing list separator...see attachment


?2. 16. 2020#
2 16 2020

thank you

 

[isladogs]

Well that was unexpected.
The results indicate that your local date format in Access is actually dd/mm/yyyy

You forgot the First # in the final test
Can you repeat the last test as ?#02. 16. 2020#
I expect you will get a list separator error again.

Then check your Windows date settings and Office language settings

If you're still having problems, then I suggest debugging the current.execute line in both cmdLogin_click and txtConPwd_afterUpdate as it is definitely a date issue.. Cant do any more for now as i'm answering on my phone

 

[lacampeona]

Helloo i have good news. Yes the error was the date format. In all the code and in the tbl users i change the date format.
i write the europe format
dd-mm-yyyy

and is workinggggg
thankkkk youuu

I have now one question in testing the expiry date....when adding new user why i cant set 30 days? if i say 30 days i cant create new user?
hmmmm

how do you reccomend to test the expiry date?

have nice day
best regards from slovenia, now i will try to implement other things
Elena

 

[isladogs]

Hooray to solving the date issue.
For info, I'm aware of several people who use this app successfully with a date format mm.dd.yyyy or dd.mm.yyyy.

As regards the new issue, I've retested and it works for me without problems.
For example:

Apologies for misspelling your name....

You can test the expiry date by temporarily changing the system date to 31 days from now.
Don't forget to change it back afterwards

 

[zeroaccess]

I despise the idea of managing passwords. If it is at all possible to include their login names in your users table and validate based off of that, it will save you a lot of headaches supporting passwords. I understand there are likely situations where you can't do that.

 

[lacampeona]

Hello
first thanks for everythink yesterday. i manage to create new user but only if the field expire date is 0 and change form is NO.

I learn that i have to have dd-mm-yyyy
now I get the same error again 3075 in query expresion for date where i want to add new user with set expiry date

here is where vba is saying me that something is wrong
when you click on add new user

can someone look the code and tell where could me the date format? hmmm

thanks

 

[theDBguy]

Hi. I would suggest using a variable for your SQL statements, so you can print its content to the Immediate Window to examine what could be wrong with your SQL string.

 

[isladogs]

Hmm...though that code worked for me yesterday, it should be as follows

      CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, PWD, ChangePWD, ExpireDays, AccessLevel, PWDDate)" & _
            " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & SetDefaultPwd() & "' AS PWD," & _
                " " & blnChangePWD & " AS ChangePWD, " & intPasswordExpireDays & " AS ExpireDays," & _
                " " & intAccessLevel & " AS AccessLevel, #" & Format(dtePwdDate, "mm/dd/yyyy") & "# AS PWDDate;"

Sorry it got overlooked before.
Tested and it still works with the date format added
Can you let me know if that fixes it.
I'll look through all the code again tomorrow for any other date related issues

 

[lacampeona]

Helooooo
yesss is working
thank you very muchhhh

can i ask how many years you guys are working with vba? is very nice to see people that if i woke you in the middle of the night you will know how to fixed very very good.

thanks again

 

[lacampeona]

Hi. I would suggest using a variable for your SQL statements, so you can print its content to the Immediate Window to examine what could be wrong with your SQL string.

thanks for your advice, nice guy Isladogs resolve the problem.

 

[isladogs]

Don't worry, its around 18:15 here - early evening!
Around 20 years in my case though I dabbled with it before then

All that experience and I still overlooked a simple date error!
FWIW I've just checked and unless I missed anything there are no more date issues.

Updated version 5.3 attached
Many thanks for helping me to find that error - I have just updated it in sample databases as well

 

[lacampeona]

Yes you are very good. i also read on the internet that this date format dd-mm-yyyy is like universal and should work in all the countries. i dont know if that is true but in my case that was really the problem.

thanks again i am so happy that we resolved very very nice
all the people here in the access world are very nice and very very good programers.

if we can use all that people with their knowlege of programing this could be very very strong company.

thanks again
i will play now with constructing my database and testing my ideas when sometimes i think i am crazy i am trying to create a perfect database,..when i think i am done other day i have new idea.... I have before some login form but this what i want here is more proteced and more good

thank you
Elena