[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Randanmization of tables if password is lost

Posted on 2006-05-31
73
Medium Priority
?
389 Views
Last Modified: 2011-09-20
I currently have a function that sends an e-mail to users in my db with randomize mixed 8 character numeric / text password replacement if users pasword is lost or forgotten.

I will like to experiment a new method when a button is clicked to generate and send a new randomized decrypted password to the current user and also updates tblSecurity.Password; tblPasswords_old.Passwords_old with the encrypted new password in addition to calling SavePassword(strUser, PerformEncryption(Me.Password, True), dtmModified).

I dont know if this is achievable - this is only an experimentation and would have nothing to lose if it doesnt work.
0
Comment
Question by:billcute
  • 40
  • 33
73 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797136
Bill, I occasionally check EE at work, but dont have access to my emails so I dont get notifications. So dont think Im ignoring you if I dont reply


here is some code that encrypts/decrypts


Public Function EncryptDecrypt(ByVal sWord As String, ByVal bDecode As Boolean) As String

    Const cintStringLen As Integer = 16
   
    Dim i            As Integer
    Dim bChr          As Byte
    Dim sOutput       As String * cintStringLen
   
    sOutput = Trim(sWord)
   
    For i = 1 To cintStringLen
        bChr = (Asc(Mid(sOutput, i, 1)) + ((0 - bDecode) * &H100) + ((1 + (2 * bDecode)) * Int((&HFF - &H20 + 1) * Rnd(-i - cintStringLen) + 0))) Mod &H100
        Mid(sOutput, i, 1) = Chr(bChr)
    Next i
   
    EncryptDecrypt = sOutput

End Function



Place this code in a module

Public Sub TestIt()
    Dim sPass As String
   
    sPass = "fred1234"

    'this is password decrypted    
    Debug.Print EncryptDecrypt(sPass, True)
   
    'this is encrypted
    Debug.Print EncryptDecrypt("No—«£¹ÏÐåúDYn†", False)
End Sub


Play about first, see if your happy with the encryption

basically call the method

EncryptDecrypt with a word, pass in TRUE to encrypt, FALSE to decrypt

note it returns a value so u need to capture that
e,,g

dim x

x = EncryptDecrypt("someword",true)


note u already have a random password generator, u can use that


If your happy with this, then we can proceed
0
 
LVL 4

Author Comment

by:billcute
ID: 16797251
rockiroads,
No worries..I dont think that way.....
Ok,
I placed the first function behind a form and created a module for the other. How will I test this in the immediate window?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797352
ok, encrypt/decrypt is in a module

create a test form, on it have one textbox, call it txtWord

now create a button call it cmdEncrypt

private sub cmdEncrypt()
   dim sEncrypted

   sEncrypted = EncryptDecrypt(txtWord.Value,True)

    msgbox sEncrypted
end sub


now run it, enter a word, then click on the button
it comes back with a encrypted word


That gives u an idea of what happens


0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 4

Author Comment

by:billcute
ID: 16797415
Ok, that was done, and I got result as expected.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797443
ok, so u happy with that

u have a password generator already done

u now want to encrypt and email that, yes?

if your happy with it, at what point do u generate and send?

what is your logic in this respect. Do you control this?

0
 
LVL 4

Author Comment

by:billcute
ID: 16797535
Yes, I am happy with it so far.

Here are what  currently have in my db
a.
Public Function fRandomPword(intStrLen As Integer, booUpperCase As Boolean)
Dim sngStopNum As Single
Dim sngStartNum As Single
Dim intLooper As Integer
Dim strNewPword As String
Dim intRndAscii As Integer

If Nz(intStrLen, 0) = 0 Then GoTo JumpOut:
'97 - 122 = lower case alphabet
'64 - 90 = upper case alphabet, change as needed
'determine if use upper or lower case
If booUpperCase = True Then
sngStopNum = 90
sngStartNum = 64
Else
sngStopNum = 122
sngStartNum = 97
End If

For intLooper = 1 To intStrLen

TryAgain:
Randomize
intRndAscii = (sngStopNum * Rnd) + 1
If (intRndAscii < (sngStartNum + 1)) Or (intRndAscii > sngStopNum) Then
GoTo TryAgain
Else
strNewPword = strNewPword & Chr(intRndAscii)
End If

Next intLooper

fRandomPword = strNewPword

JumpOut:
End Function

--------------------------------
b.
Private Sub cmdPasswordReset_Click()

Dim strTo As String
Dim strSubject As String
Dim strMessage As String

'This opens a form to enter the users E-Mail address if it is blank
If IsNull(txtemail) Then

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmail"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

'This adds the word "New" to the field txtChange which is looked at when the user logs on to see if
'they must be forced to change their password
Me.txtChange = "New"

'This grabs a random 8 character string and replaces the old password with it
Me.txtPassword = fRandomPword(8, True)

'This line send the e-mail
strTo = txtemail
strSubject = "Your Password Has Been Reset"
strMessage = "Your password has been reset to: " & [txtPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"

MsgBox "The users password has been changed, and an E-Mail was sent to thier account"
   
    DoCmd.Close
    DoCmd.OpenForm "fmnuAdministrator"

End If

End Sub
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797543
ok, re-reading your question

u already got the code

so the logic is

1. generate new password using your generatepassword
2. encrypt that


But with regards to saving it, if we save it, fine, but then when the users log on and enter their password

u have to decrypt whats stored in the DB, then compare

u okay with that?

can u post the function you want to modify - the one that generates and sends

also, have u made changes to frmLogon ok procedure? the one that checks the password? that needs changing to decrypt


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797546
ok, got it :)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16797597
do u want the users to be sent the encrypted password?
or do u want it saved as encrypted in the DB

if u send it encrypted, and since the text is not plain english, it might be difficult for them to enter that in

0
 
LVL 4

Author Comment

by:billcute
ID: 16797791
First, user logs on and wants a new randomly generated password by clciking a buiton in frmEdituser, then
a. the randomly generated 'encrypted' password are first updated into the password fields of tblSecurity and tblPasswords_old including a call to save password in the savepassword function.

b. the same randomly generated password will be descrypted before being e-mailed to the current user.

c. Next time user logs back on , he types in the new decrypted randomized password, then the cmdok code will then compare what user types in with the encrypted format in tblSecurity.Password,  tblPasswords_old.Password and the 'GetPassword' function based on my other post at thi link;
http://www.experts-exchange.com/Databases/MS_Access/Q_21863458.html

d. if the three level password are compared and correct then log user into the db
0
 
LVL 4

Author Comment

by:billcute
ID: 16805963
rockiroads,
What is the next step here with the info provided above?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16806260
ok, on password reset, we need to store encrypted passwords


Private Sub cmdPasswordReset_Click()

Dim strTo As String
Dim strSubject As String
Dim strMessage As String

'This opens a form to enter the users E-Mail address if it is blank
If IsNull(txtemail) Then

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmail"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

'This adds the word "New" to the field txtChange which is looked at when the user logs on to see if
'they must be forced to change their password
Me.txtChange = "New"

'This grabs a random 8 character string and replaces the old password with it
Me.txtPassword = fRandomPword(8, True)

'WHAT WE DO HERE IS CALL A MODULE TO SORT OUT THE PASSWORDS
UpdatePasswords


'This line send the e-mail
strTo = txtemail
strSubject = "Your Password Has Been Reset"
strMessage = "Your password has been reset to: " & [txtPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"

MsgBox "The users password has been changed, and an E-Mail was sent to thier account"
   
    DoCmd.Close
    DoCmd.OpenForm "fmnuAdministrator"

End If




'here we put the logic in
'Correct me if I am wrong
private sub UpdatePasswords()

    dim sEncrypted
    dim sSql as String

    'We first encrypt password
    sEncrypted = EncryptDecrypt(Me.txtPassword)

     'Now update tables
     'We know the userid, we take the one from the form

      'Update tblSecurity
      sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & Me.UserID & "'"
      DoCmd.RunSQL sSQL

      'How does tblPasswords_Old work, do we just insert a value?
      sSql = "INSERT INTO tblPasswords_Old (UserID, Password, ChangeDate) VALUES ('" & Me.UserID & "','" & sEncrypted & "','" & Now() & "')"
      DoCmd.RunSQL sSQL

end sub

Do we do anything else with regards to reset?


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16806288
am I on the right track so far

How does

PerformEncryption (your existing function)

fit into the frame of things?

0
 
LVL 4

Author Comment

by:billcute
ID: 16806395
rockiroads,
I think you missed the sequence and logic.... please find below the sequence and logic.

1. First check if CURRENTLY LOGGED ON user has intra office mail address in tblUser.IntEmail field
   a.  if found, then send e-mail,
   b.  if no e-mail found then msgbox 'No valid e-mail address found - new password sent

2.  If item 1 a was successful and If new password was sent then, RESET password in
    a.  tblsecurity.password,
     b. tblpassword-olds.password and RESET tblpasswords-old.changedate fields then
     c.  call Savepassword function to reset the password in the module .......--- I have this function

3. If item 1 b, then "no password sent and do not reset the tables and module listed in item 2 a. b and c
   ....also give user a msgbox that ....Passsword not reset, please contact your Administrator

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16806415
so how do u define a valud intra office email address

is it simply one filled in or not?

0
 
LVL 4

Author Comment

by:billcute
ID: 16806512
When user logs into the db, window's logon name iss aotomatically grabbed into the username field - this can be used to grab the tblUsers.IntraEmailAddress field from each user...such as:
 johnsonb@state.pa.gov     '<<<<---- if johnsonb is the currentuser.

..the user of "Environ" might be a good way to grab the e-mail -- what do you think?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16806532
little confused, u are doing this from frmEditUser (reset passwords)

therefore admin may pick someone else, so u cant use logged on user

do u store the email address in the table? I dont recall u storing the email


Plus assuming userid@state.pa.gov

is that the right thing to do?

or are all your usernames the prefix to an email address

so a person with a userid of johnsobn, must have an email address johnsobn@state.pa.gov
0
 
LVL 4

Author Comment

by:billcute
ID: 16806540
rockiroads.
Currently, my frmE-mail form has a rowsource of:
SELECT tblUsers.NameID, tblUsers.e_mail, [LName] & ", " & [FName] & "  " & [MI] AS UserName FROM tblUsers WHERE (((tblUsers.NameID)=Forms!frmUserDetails!NameID)) ORDER BY tblUsers.LName;

..if you choose to use global for example, we can add NameID to the global and substitute..the line code below:

(((tblUsers.NameID)=Forms!frmUserDetails!NameID))

Note:
Private Sub cmdPasswordReset_Click() is a button in frmUserDetails

...if you choose to use this option, then I will appreciate help with the row source above.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16806577
....Your question:
so a person with a userid of johnsobn, must have an email address johnsobn@state.pa.gov

Answer:
No, userid johnsonb may have a different e-mail address.

Although, I was formally thinking of having "Private Sub cmdPasswordReset_Click()" in frmEditUser - I have decided that the best place for it is a button in frmUserDetails

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16806595
..what I was trying to say ...is that when a user logs on....any valid e-mail address should be grabbed only in the tblUsers.IntraEmailaddress field.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16806907
ok, if it was done from frmUserDetails
lets take existing logic in frmEditUser click - i.e. if u have permissions to go into frmEditUser, u have permissions to reset password, is that okay

a  button on frmUserDetails
do we show the email address anywhere here?

pls follow this logic, I think its more what u want now


Private Sub cmdPasswordReset_Click()

    Dim strTo As String
    Dim strSubject As String
    Dim strMessage As String
   
   
    If List0.ListCount = 0 Then
        MsgBox "There are no users in the list", vbInformation + vbOKOnly, "Error"
    Else
        MsgBox "You have selected " & List0.Column(1) & " - " & List0.Column(5)

        'Logic - if your accessid <= 3 and the selected user from list is higher or equal to yours then allow edit
        '          if your accessid > 3 then u can only edit your own profile
        'SAME AS EDITUSER
        If (g_lAccessID <= 3 And List0.Column(5) >= g_lAccessID) Or (g_lAccessID > 3 And List0.Column(1) = g_sUserID) Then
            UpdateUserPassword
        Else
            MsgBox "Sorry operation not allowed"
        End If
    End If
End Sub


'here we put the logic in
'Correct me if I am wrong
Private Sub UpdateUserPassword()

    Dim sPassword As String
    Dim sEncrypted
    Dim sSql As String
   
   
    'This grabs a random 8 character string and replaces the old password with it
    sPassword = fRandomPword(8, True)
   
    'Next we encrypt password
    sEncrypted = EncryptDecrypt(sPassword)
   
    'Now we update the tables, store encrypted password
   
    'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
    sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & Me.txtUser & "'"
    DoCmd.RunSQL sSql

    'How does tblPasswords_Old work, do we just insert a value?
    sSql = "INSERT INTO tblPasswords_Old (UserID, Password, ChangeDate) VALUES ('" & Me.txtUser & "','" & sEncrypted & "','" & Now() & "')"
    DoCmd.RunSQL sSql
   
    'Now we send the email
    'WE GET THE EMAIL FROM THE FORM - AS WE WANT TO SELECTED USERS EMAIL
    'BILL IS THERE A CONTROL ANYWHERE HERE?
    strTo = Me.Email
   
    strSubject = "Your Password Has Been Reset"
    strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
    DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
   
    MsgBox "The users password has been changed, and an E-Mail was sent to their account"
   
End Sub




0
 
LVL 4

Author Comment

by:billcute
ID: 16813177
rockiroads,
Thank you for your suggested code...what "control" are yu asking for in your above code...? please assist in placing the appropriate control.

You may have forgotten that I do have password encryption module in my logi form to sychronize this with all other code in my db, dont you think it will be a good idea to user my current module in the DB.
' ************
Public Function PerformEncryption(ByRef strPassword As String, Optional ByVal boo As Boolean = True) As String
' boo = True then Encrypt, boo = False then Decrypt
Dim strCode As String, intCounter As Integer

    For intCounter = 1 To Len(strPassword)
        If intCounter Mod 2 = 0 Then
            If boo = True Then
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) + 1)
            Else
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) - 1)
            End If
        Else
            If boo = True Then
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) + 2)
            Else
                strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) - 2)
            End If
        End If
    Next intCounter

    PerformEncryption = strCode

End Function
' *********

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16813272
rockiroads.
One more thing, each user exist in tblassword-old  from the time the user was created.  believe that since user, password and changedate fields already exist in the table, you only need to update just two fields, password and changedate fields.

THEREFORE YOUR CODE TO INSERT MAY NEED TO BE CHANGED TO UPDATE... using WHERE CLAUSE JUST AS YOU DID FOR tblSecurity.Paswword sql update above....

sSql = "INSERT INTO tblPasswords_Old (UserID, Password, ChangeDate) VALUES ('" & Me.txtUser & "','" & sEncrypted & "','" & Now() & "')"

2. i noticed you were using Me.txtUser what happened to the global you declared in my previous posts
    g_sUserId

Would it not be better to use this global instead of Me.txtUser?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16815407
rockiroads,
What do you think of my suggestion above?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16815938

    Rockiroad,
                                              PONDERING QUESTIONS
  1.  How do I force users to change their password next time user log in with the new randonmized password.
  2.  I will appreciate your help in putting finishing touches to the control to send email to users.

   Regards,
   Bill.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16818105
ok, let me get this right


cmdPasswordReset is like u said, required for
frmUserDetails

Now I used the userid on the form because
If admin user logged in and wants to reset a password
he/she can pick a user and do it
If u use the global, u end up only resetting yourself
But u can change to that if u want
You need to think about how admin can reset passwords


Ok, u have password encryption, but Im confused now
Its probably me, I thought u wanted a encryption
But what we do is make use of your encryption right

Easily done



--

tblpassword-old, ok when user created they have an entry here

what is the logic in resetting passwords?
we update current password in tblSecurity
we insert a record into tblPasswords_old
do u want to keep a copy of all old passwords
or just keep copy of last one?
if we keep copy of all passwords then we insert
if we keep just last one, then we update
pls advise what u want to do


--

To force users to change their password,
we need to introduce a flag
when reset the password, or a new user is created
this flag is set.
On logon, if this flag is set, then u force them to change password
on password change, this flag is reset

0
 
LVL 4

Author Comment

by:billcute
ID: 16820817
rockiroads,
You wrote..
1.
cmdPasswordReset is like u said, required for
frmUserDetails

If admin user logged in and wants to reset a password he/she can pick a user and do it

Answer..This part is very good...I' ll keep this part.

2. ..... But what we do is make use of your encryption right
   Answer..
    I'll appreciate the conversion to my encryption.

3. tblPasswords_old......./if we keep just last one, then we update the table...
    Answer.. I want to keep the last one....so "Updating tblPasswords_old fields is what I want.

(4). To force users to change their password, we need to introduce a flag when reset the password, or a new user
      is created this flag is set. On logon, if this flag is set, then u force them to change password on password
      change, this flag is reset
     
      I will appreciate help with this part .

(5). Controls for sending the decrypted password to user.
     I have two text fields that are bounded to tblUsers namely:
   
a.       txtIntEmailAddress bounded to tblUsers.IntEmailAddress field
b.       txtNameID bounded to tblUsers.NameID field ..This field is (PK) and related to tblSecurity.NameID field (FK)
c.       txtChange (described in the code I pasted on: 05/31/2006 07:23AM EDT)
         This txtChange form field in frmUserDetails is bounded to tblSecurity.PWChange
        ....which can be used as a "Flag" for enforcing password change for new users and randomly assigned
        password.

These three text fields described above are visible text form fields in frmUserDetails

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16821626
rockiroads,
I have decided to open a new thread to deal with the question of forcing user to change password based on flagging - this is because, I thought that question warrants a new thread. I will appreciate your help at the link below. In addition I will appreciate your help in including the flag design for E-MAIL design here such that when users password are reset, the flag is alsoi set.

And as soon as user logs in with the new password and the validity of the password is checked against tblSecurity.Password, tblPasswords-old, module password..and tblPasswords-old.ChangeDate, then force user to change password if and ONLY if the flag has been set ..if not set...give masgbox..'Please contact your dataabase Administrator.' The link below has the details.

http://www.experts-exchange.com/Databases/MS_Access/Q_21873240.html

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16822746
right bill, that new function in frmUserDetails called UpdateUserPassword

ive modified as to what u want


Private Sub UpdateUserPassword()

    Dim sPassword As String
    Dim sEncrypted
    Dim sSql As String
   
   
    'This grabs a random 8 character string and replaces the old password with it
    sPassword = fRandomPword(8, True)
   
    'Next we encrypt password
    sEncrypted = PerformEncryption(sPassword, True)
   
    'Now we update the tables, store encrypted password
   
    'NOTE tblSecurity PWChange, is this set to TRUE for forcing password change
   
    'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
    sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "', PWChange=True WHERE UserID = '" & Me.txtuser & "'"
    DoCmd.RunSQL sSql

    'How does tblPasswords_Old work, do we just insert a value?
    Updateusertable Me.txtuser, sPassword   'note we pass in unencrypted pswd as that func does it
   
    'Now we send the email
    'WE GET THE EMAIL FROM THE FORM - AS WE WANT TO SELECTED USERS EMAIL
    'BILL IS THERE A CONTROL ANYWHERE HERE?
    strTo = Me.txtIntEmailAddress
   
    strSubject = "Your Password Has Been Reset"
    strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
    DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
   
    MsgBox "The users password has been changed, and an E-Mail was sent to their account"
   
End Sub



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16822751
your logon code should now work with regards to encryption because u already decrypt it

0
 
LVL 4

Author Comment

by:billcute
ID: 16825477
rockiroads,
Here is the code that works with the controls in place...but there are some glitches to fix.

I could not get frmEMail to display the correct user name when that form opens (Please refer to the recent sample db on the subject).

Here are visible fields that are embeded in frmUserDetails that relates to the E-Mail function

(i). txtPWChange - bounded to PWChange field in tblUsers
(ii). NameID form field bounded to tblUsers.NameID
(ii). txtIntMainAddess bounded to tblUsers.IntMailAddress

Private Sub UpdateUserPassword()

        Dim sPassword As String
        Dim sEncrypted As String
        Dim sSql As String
       
        Dim strTo As String
        Dim strSubject As String
        Dim strMessage As String
    ' *************
    'This opens a form to enter the users E-Mail address if it is blank
If IsNull(txtIntMailAddress) Then

        Dim stDocName As String
        Dim stLinkCriteria As String

        stDocName = "frmEmail"
        DoCmd.OpenForm stDocName, , , stLinkCriteria

    Else

         ' ***************************

        'This grabs a random 8 character string and replaces the old password with it
          'Next we encrypt password
    sEncrypted = PerformEncryption(sPassword, True)
   
    'Now we update the tables, store encrypted password
   
    'NOTE tblSecurity PWChange, is this set to TRUE for forcing password change
   
    'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
        sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "', PWChange=True WHERE UserID = '" & Me.txtUser & "'"
        DoCmd.RunSQL sSql

    'How does tblPasswords_Old work, do we just insert a value?
        Updateusertable Me.txtUser, sPassword   'note we pass in unencrypted pswd as that func does it
   
    'Now we send the email
    'WE GET THE EMAIL FROM THE FORM - AS WE WANT TO SELECTED USERS EMAIL
    'BILL IS THERE A CONTROL ANYWHERE HERE?
        strTo = Me.txtIntMailAddress   ' <<--- bounded to tblUsers.IntMailAddress
   
        strSubject = "Your Password Has Been Reset"
        strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
        DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
   
        MsgBox "The users password has been changed, and an E-Mail was sent to their account"
    End If
End Sub
' ************
In frmEMail:
First, I included g_lNameID in the global then use that in frmEMail

Also in frmEMail there is only one text field on the form
 txtIntMailAddress bounded to IntMailAddress

Here is the Form's On Load code:

Private Sub Form_Load()
Me.RecordSource = "SELECT tblUsers.NameID, tblUsers.IntMailAddress, " & _
                    "[LName] & ', ' & [FName] & '  ' & [MI] " & _
                    "AS UserName FROM tblUsers " & _
                    "WHERE (((tblUsers.NameID)=" & g_lNameID & ")) ORDER BY tblUsers.LName "
End Sub

' *************** End of Addition ************

So far there are no errors but if for example user Admin attaempts to send random password via e-mail to user.Bill and the E-Mail button is clicked from frmUserDetails, frmEMail opens but the user loaded in the form is user developer
not user Bill.

Since User.Bill was highlighted in List0 of frmUserDetails, then when frmEMail opens user.Bill's profile should be loaded.

Is there any way of rectifying this?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16826697
rockiroads,
...The line code is part of my code but was omited in my last pasted code above...

'This grabs a random 8 character string and replaces the old password with it
    sPassword = fRandomPword(8, True)

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16826722
rockiroads,
I finally figured out a way to get frmE-Mail display the appropraite form but there is a problem with the code for sending e-mail.

Upon testing, I received two errors:
1. There was an error with Ms. Outlook
    "no profile has been created. To create a profile, user the Mail Icon in the Control panel"
   Here is my problem...obviously not all users will have Ms. Outlook setup on their PC. So what happens if User
   has no outlook setup then there might be ...ccompile error????

   I have once come across one of your suggestions on EE in another where you posted a code for sending e-mail
   that bypasses Ms.Outlook. Can you suggest something like than for my purpose ?

2. Upon clicking the "Ok" button for the first error..I received another error..
      "Run-time error '2296'
       The password is not valid, the message wasnt sent.  
     ....Did I receive this error because the Ms. Outlook was not set?

   ,,,,,debug highlighted...
        DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
 
     ..... although, I discovered that tblSecurity.Password, tblPasswords_old.Password, tblPasswords_old,Changedate
      and perhaps the module were updated with the new randomized password.
 
In view of the above, what would you advise?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16827214
In that sample code u just posted, where is sPassword set when u call PerformEncryption?

u forgot to get password

   'This grabs a random 8 character string and replaces the old password with it
    sPassword = fRandomPword(8, True)
   
    'Next we encrypt password
    sEncrypted = PerformEncryption(sPassword, True)


Could that have somethng to do with it?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16827220
Add this command

DoCmd.SetWarnings  False

before you run the Update tblSecurity command
this will get rid of "You are about to append 1 row" msg

to reset it, put it after call to updateusertable

DoCmd.SetWarnings True
0
 
LVL 4

Author Comment

by:billcute
ID: 16827370
rockiroads,
both were included in my original test...When cut and pasted the code here earlier I forgot to . The errors I mentioned earlier are persistent with codes below...you may need to tes yourself.

Testing:
a. Add to frmEMail one text field and name it txtIntMailAddress bounded to IntMailAddress then

b. add the folowing text fields to frmEditUser:
i). txtPWChange - bounded to PWChange field in tblUsers
(ii). NameID form field bounded to tblUsers.NameID
(ii). txtIntMainAddess bounded to tblUsers.IntMailAddress

' *****Here is the up-to-date-code*****

Private Sub UpdateUserPassword()
        Dim sPassword As String
        Dim sEncrypted As String
        Dim sSql As String
       
        Dim strTo As String
        Dim strSubject As String
        Dim strMessage As String
    ' *************
    'This opens a form to enter the users E-Mail address if it is blank
If IsNull(txtIntMailAddress) Then
        DoCmd.OpenForm "frmEMail", , , "[SecurityID] = " & List0.Column(0)
    Else
            'This grabs a random 8 character string and replaces the old password with it
        sPassword = fRandomPword(8, True)

          'Next we encrypt password
        sEncrypted = PerformEncryption(sPassword, True)
   
         'Now we update the tables, store encrypted password
   
    'NOTE tblSecurity PWChange, is this set to TRUE for forcing password change
   
    'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
      DoCmd.SetWarnings  False               ' <<<----- Just added
        sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "', PWChange=True WHERE UserID = '" & Me.txtUser & "'"
        DoCmd.RunSQL sSql

    'How does tblPasswords_Old work, do we just insert a value?
        Updateusertable Me.txtUser, sPassword   'note we pass in unencrypted pswd as that func does it

       DoCmd.SetWarnings True            ' <<<----- Just added

    'Now we send the email
    strTo = Me.txtIntMailAddress
   
        strSubject = "Your Password Has Been Reset"
        strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
        DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
   
        MsgBox "The users password has been changed, and an E-Mail was sent to their account"
    End If
End Sub
' ***********

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16827527
ok, first thing

In UserEntryGate

why do u call InitialiseGlobals? this is resetting the global variables set up the earlier call to SetupGlobals


Secondly, in frmUserDetails, u have two textboxes that hold the email address

1. IntMailAddress
2. txtIntEmailAddress

Why do u have two? do u really need it?
Regardless, u need to ensure u pick the right one in the code when u set strTo


currently u do

strTo = Me.txtIntMailAddress


It should be

strTo = Me.txtIntEMailAddress
or
strTo = Me.IntMailAddress





If u remove InitialiseGlobals in UserEntryGate, then the global   g_sUserId is still set



if u add this in form_load in frmUserDetails, it loads up your details automatically

    Dim rs As DAO.Recordset
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[UserID] = '" & g_sUserID & "'"
    Me.Bookmark = rs.Bookmark

this is optional though,
0
 
LVL 4

Author Comment

by:billcute
ID: 16827597
I have implemented the changes but when I tested again, I still received the errors earlier mentioned above.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828639
Sorry, Bill, been in and out most of the day

I think we got wires cross somehow

From the earlier post, I thought it wa frmUserDetails we were working on

Its your recent post which mentions frmEmail
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828693
do u want this code in frmUserDetails or frmEditUser

I was going by this comment u said

"Although, I was formally thinking of having "Private Sub cmdPasswordReset_Click()" in frmEditUser - I have decided that the best place for it is a button in frmUserDetails"


With regards to frmEmail, can I make a suggestion?
in frmUserDetails, when they click on "Email Random Password"
do this, if the email address (internal) is null, then come up with a msgbox

"Sorry, but you must first setup an internal email address. Do u wish to set one up now"

And the other way to setup the email address is to say, double click on the field, then bring up frmEmail, so it allows u to change it as well



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828784
right this is the approach to frmEmail

**** STEP 1:

U have a recordsource for that already

it is currently set to

SELECT tblUsers.NameID, tblUsers.e_mail, [LName] & ", " & [FName] & "  " & [MI] AS UserName FROM tblUsers WHERE (((tblUsers.NameID)=Forms!fmnuUsers!NameID)) ORDER BY tblUsers.LName;

You need to change this because there is no such field called tblUsers.e_mail, is there

Change it to this (for editing internal email)

SELECT tblUsers.NameID, tblUsers.IntMailAddress, [LName] & ", " & [FName] & "  " & [MI] AS UserName FROM tblUsers;

Ensure the control source for the email field is setup correctly


**** STEP 2:

Now in frmUserDetails, u have a unbounded box called NameID
this does not store the NameID as its not associated with a field from your rowsource. This is because NameID is returned twice in that recordsource
therefore set the Control Source for NameID in frmUserDetails to tblUsers.NameID


**** STEP 3:

Now in frmUserDetails, where u call frmEmail

change it to this

DoCmd.OpenForm "frmEmail", , , "NameID = " & Me.NameID

This should then get the info for the selected user


0
 
LVL 4

Author Comment

by:billcute
ID: 16830236
rockiroads,
I did implement all your suggestions.

In the code below, when user receives msgbox whether or not he wants to add a new message. If the user's response
is "No", the error below surfaces

Run-Time Error '94': Invalid use of Null

..on

strTo = Me.IntMailAddress

(b). If the user's response is "Yes" from the above msgbox, then frmEMail inserts an E-Mail address but after e-mail
      addres is added, nothing else happened. I think after adding an e-mail, another msgbox should remind user
      whether or not he still wants to send a randomized password vis E-Mail...if "Yes" then update password and send
      e-mail but if "No" response, "DO NOTHING"
' ******************

Private Sub UpdateUserPassword()
        Dim sPassword As String
        Dim sEncrypted As String
        Dim sSql As String
       
        Dim strTo As String
        Dim strSubject As String
        Dim strMessage As String
    ' *************
    'This opens a form to enter the users E-Mail address if it is blank
If IsNull(IntMailAddress) Then
    If MsgBox("Sorry, but you must first setup an internal email address. Do u wish to set one up now", vbQuestion + vbYesNo, "Error") = vbYes Then

        Dim stDocName As String
        Dim stLinkCriteria As String

        stDocName = "frmEmail"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.OpenForm "frmEmail", , , "NameID = " & Me.NameID

    Else
   
        'This grabs a random 8 character string and replaces the old password with it
        sPassword = fRandomPword(8, True)

          'Next we encrypt password
        sEncrypted = PerformEncryption(sPassword, True)
   
    'Now we update the tables, store encrypted password
   
    'NOTE tblSecurity PWChange, is this set to TRUE for forcing password change
   
    'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
        sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "', PWChange=True WHERE UserID = '" & Me.txtUser & "'"
        DoCmd.RunSQL sSql

    'How does tblPasswords_Old work, do we just insert a value?
        Updateusertable Me.txtUser, sPassword   'note we pass in unencrypted pswd as that func does it
   
    'Now we send the email
        strTo = Me.IntMailAddress            ' <<---- Error line...RT '94' - Invalid use of Null

        strSubject = "Your Password Has Been Reset"
        strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
        DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
   
        MsgBox "The users password has been changed, and an E-Mail was sent to their account"
    End If
    End If
End Sub
' ***************

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16830258
rockiroads,
Also with code below pasted in frmUserDetails when frmUserDetails open, this error appears

Run-time error '91':
"Object variable and With Block variable not set

..on..
Set rs = Me.Recordset.Clone

from the form's On Load Event.

                   Private Sub Form_Load()
                    Dim rs As DAO.Recordset
                    Set rs = Me.Recordset.Clone
                    rs.FindFirst "[UserID] = '" & g_sUserID & "'"
                    Me.Bookmark = rs.Bookmark
Me. RecordSource = "Select.................
List0.RowSource = "Select.........

End Sub.

In addition if user has an e-mail address when he clicks the button to send random password / E-Mail, nothing happens which means password does not create and e-mail not sent...but when there is no e-mail address then everything works with the errors described above.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16830586
The problems are due to how u have coded it

The reason why u get a invalid use of null error is because of your newly created msgbox
look at the logic, if u select No, u end up processing it!

And the reason why no email gets sent is because all your code is inside the first IF statement
i.e. If IsNull(IntMailAddress) Then



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16830599
try this

Private Sub UpdateUserPassword()

    Dim sPassword As String
    Dim sEncrypted As String
    Dim sSql As String
   
    Dim strTo As String
    Dim strSubject As String
    Dim strMessage As String
   
   
    'Check user has enetered email address
    If IsNull(IntMailAddress) Then
        If MsgBox("Sorry, but you must first setup an internal email address. Do u wish to set one up now", vbQuestion + vbYesNo, "Error") = vbYes Then

            Dim stDocName As String
            Dim stLinkCriteria As String
   
            ' *************
            'This opens a form to enter the users E-Mail address if it is blank
            stDocName = "frmEmail"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
            DoCmd.OpenForm "frmEmail", , , "NameID = " & Me.NameID

        Else
            Exit Sub
        End If
    Else
   
        'This grabs a random 8 character string and replaces the old password with it
        sPassword = fRandomPword(8, True)

        'Next we encrypt password
        sEncrypted = PerformEncryption(sPassword, True)
   
        'Now we update the tables, store encrypted password
   
        'NOTE tblSecurity PWChange, is this set to TRUE for forcing password change
        DoCmd.SetWarnings False
        'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
        sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "', PWChange=True WHERE UserID = '" & Me.txtUser & "'"
        DoCmd.RunSQL sSql

        'How does tblPasswords_Old work, do we just insert a value?
        Updateusertable Me.txtUser, sPassword   'note we pass in unencrypted pswd as that func does it
   
        DoCmd.SetWarnings True
       
        'Now we send the email
        strTo = Me.IntMailAddress            ' <<---- Error line...RT '94' - Invalid use of Null

        strSubject = "Your Password Has Been Reset"
        strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"
        DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"
   
        MsgBox "The users password has been changed, and an E-Mail was sent to their account"
    End If
end sub

0
 
LVL 4

Author Comment

by:billcute
ID: 16831328
RT '94' error has disappeared but how do I resolve this question?

And the reason why no email gets sent is because all your code is inside the first IF statement
i.e. If IsNull(IntMailAddress) Then

What I was trying to do was first to DLookup e-mail address if found and outlook is installed then send e-mail to user,

if outlook is not installed do not send e-mail and give msgbox:
Ms. Outlook is not installed in this PC or if there is a way to send an -email without using Ms. Outlook as an interface it will be great.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16832841
Ok, only problem now is sending emails if they dont have outlook

hopefully you understood my last post

An option is to use CDO. I have an example at home.
But the thing is, u need to know their smtp server
0
 
LVL 4

Author Comment

by:billcute
ID: 16835053
If you could share this wioth me it will be great.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16836945
alright bill, try this
for more info, just read up on CDO on google or something (Collaboration Data Objects or something like that)


Public Function SendEmail(ByVal strTo As String, _
                          ByVal strMessage As String, _
                          ByVal strSubject As String, _
                          Optional ByVal strAttach As String = "")

    Dim objEmail As Object
   
   
    On Error Resume Next
   
    Set objEmail = CreateObject("CDO.Message")

    '**** email address of sender
    objEmail.From = "fred@smith.com"      
    objEmail.To = strTo
    objEmail.Subject = strSubject
    objEmail.TextBody = strMessage
    objEmail.AddAttachment strAttach
    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    '**** smtp.xxx.com - here u enter your smtp server name, whatever that is
    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.xx.com"

    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    objEmail.Send
    If Err.Number <> 0 Then
        MsgBox "Error in sending. " & Err.Description
    Else
        MsgBox "Sent"   'remove this if u dont want confirmation
    End If
    Set objEmail = Nothing

End Function




A working example can be found here  https://filedb.experts-exchange.com/incoming/ee-stuff/111-CDO.zip
0
 
LVL 4

Author Comment

by:billcute
ID: 16848575
rockiroads,
I have not been able to test your sample because of the CDO.dll...I did not find one for a download yet. As soosn as I do so, I'll let you know.

..meanwhile..
I have got a new post ..the code works, I only need to place the code appropriately in the Sub.

http://www.experts-exchange.com/Databases/MS_Access/Q_21877190.html

Regards,
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16850474
rockiroads,
This about my last post on Login Form..it's about 98% done. The code works right off the selve..but needed help in placing it appropriately n my on click event handler of btnForogtPassword.

Any help will be appreciated placing the code in the btnForgotPassword button code will be appreciated.

Regards
Bill.
0
 
LVL 4

Author Comment

by:billcute
ID: 16858322
rockiroads,
I clicked the website posted in your last code..I am getting Broiwser error "PAGE CANNOT BE FOUND"

on...

objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    '**** smtp.xxx.com - here u enter your smtp server name, whatever that is
    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.xx.com"

    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
' *******

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16858901
what website? there is none,
if u mean these lines
"http://schemas.microsoft.com/cdo/configuration/sendusing")

no, u dont click on it

all u need to do is set  

= "smtp.xx.com"

to the smtp server that u will use to send the email
0
 
LVL 4

Author Comment

by:billcute
ID: 16859915
rockiroads,
It seems there was one oversight on the coe below. The AccessID = 0 was not addressed. I will like to amend the code that further clarifies AccessID =0.

For example, if AccessID = 0...then msgbox "You are not authorized to view this details.

Here is the current code from an On Click event provided earlier on this post.

If (g_lAccessID <= 3 And List0.Column(5) >= g_lAccessID) Or (g_lAccessID > 3 And List0.Column(1) = g_sUserID) Then
            UpdateUserPassword
' ************

(2). How do I deal with the code below such that it will not interfere with the sending of e-mail as discussed earlier:

'Check user has entered email address
    If IsNull(IntMailAddress) Then
        If MsgBox("Sorry, but you must first setup an internal email address. Do u wish to set one up now", vbQuestion + vbYesNo, "Error") = vbYes Then

(3). How do I integrate your last suggested 'CDO' code with your earlier suggested code  for sending e-mails?

Regards
Bill


0
 
LVL 4

Author Comment

by:billcute
ID: 16880251
rockiroads,
I know that you are very busy - I will appreciate it if you could assist in tying the loose ends togther so I could close this post for good.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16881429
Hi Bill, Im back, ive been away, been in Germany last 3 days
Am totally exhausted though, but was a great weekend
will look at htis

whats with the accessid=0?

I took the same logic as per EDIT USER

0
 
LVL 4

Author Comment

by:billcute
ID: 16881694
Beautiful...hope you enjoyed your trip.

On the AccessID=0.

If anyone use the Shift key to enter the db, the accessID will be equals to 0.

In this case, I want to be able to deny all access to the form with AccessID < 1

Based on my previous comments, I want to be able to send e-mails to indivdual with the information on new password. There is also a sample db I have here for sending messages among users in a network. The sample is embedded in my db. If this simple message sending db can be untilized to send random password  - it will be great.

I can list another thread just for the topic and attach the sample db via EE upload.

Regards,
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16881786
Bill, I will get this done, at the moment. Im cleaning the house, seem to have a ant problem

Germany was fantastic, I went to see England vs Paraguay, amazing atmosphere

0
 
LVL 4

Author Comment

by:billcute
ID: 16881902
So you are English fan or Paraguay? I heard the two teams are great warriors. Who won?

Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16883351
Hi Bill, Im a English fan of course
England won 1-0

Right regarding accessid = 0

create a function somewhere in a module called CheckAccessID

e.g.

public function CheckAccessID as boolean
    if g_lAccessID < 1 then
        msgbox "Sorry but you do not access to this option",vbcritical,"Permission Denied"
        CheckAccessID = False
    else
        CheckAccessID = True
    end if
end function



Now all u do is call this
eg.

'Check access id, exit procedure if not valid
if CheckAccessID = False then exit sub


0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16883374
Regarding sending emails

where u have this

    DoCmd.SendObject , , , strTo, "", "", strSubject, strMessage, False, """"


u do this instead

    SendEmail strTo, strMessage, strSubject



Regarding this


'Check user has entered email address
    If IsNull(IntMailAddress) Then
        If MsgBox("Sorry, but you must first setup an internal email address. Do u wish to set one up now", vbQuestion + vbYesNo, "Error") = vbYes Then


What is wrong? if user has not entered a email address, u get prompted. If u say no or Exit, thn u cancel out
What do u want to do if no email address specified? Once setup, do u want them to send the email?

ok, based on the last UpdateUserPassword post

Private Sub UpdateUserPassword()

    Dim sPassword As String
    Dim sEncrypted As String
    Dim sSql As String
   
    Dim strTo As String
    Dim strSubject As String
    Dim strMessage As String
   

    'check accessid
    if CheckAccessID = 0 then exit sub
   
    'Check user has enetered email address
    If IsNull(IntMailAddress) Then
        If MsgBox("Sorry, but you must first setup an internal email address. Do u wish to set one up now", vbQuestion + vbYesNo, "Error") = vbYes Then

            DoCmd.OpenForm "frmEmail", , , "NameID = " & Me.NameID

        Else
            Exit Sub
        End If
    End if

    'Check again, just in case user has entered new one
    If IsNull(IntMailAddress) = false Then
   
        'This grabs a random 8 character string and replaces the old password with it
        sPassword = fRandomPword(8, True)

        'Next we encrypt password
        sEncrypted = PerformEncryption(sPassword, True)
   
        'Now we update the tables, store encrypted password
   
        'NOTE tblSecurity PWChange, is this set to TRUE for forcing password change
        DoCmd.SetWarnings False
        'Update tblSecurity - note we use Me.txtUser - assuiming this is what the field is called
        sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "', PWChange=True WHERE UserID = '" & Me.txtUser & "'"
        DoCmd.RunSQL sSql

        'How does tblPasswords_Old work, do we just insert a value?
        Updateusertable Me.txtUser, sPassword   'note we pass in unencrypted pswd as that func does it
   
        DoCmd.SetWarnings True
       
        'Now we send the email
        strTo = Me.IntMailAddress            ' <<---- Error line...RT '94' - Invalid use of Null

        strSubject = "Your Password Has Been Reset"
        strMessage = "Your password has been reset to: " & [sPassword] & " please be sure to use this next time you log on, and do not forget to change it immediately"

        'Send email using CDO
        SendEmail strTo, strMessage, strSubject
   
        MsgBox "The users password has been changed, and an E-Mail was sent to their account"
    End If
end sub







0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16883392
Now u will need to make the following changes to frmEmail

because the user has updated the password, the form that holds the email has to be updated

so try this

add this code in a module somewhere


Public Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            fIsLoaded = True
        End If
    End If
End Function



Now in frmEmail, when they change the password
we check the form is open (one that contains UpdateUserPassword) and update accordingly

0
 
LVL 4

Author Comment

by:billcute
ID: 16883941
.....thanks for your suggestions... I will test and let you know later.

 Thank you for the module...I think I already have function IsLoaded in my db....
...but how do you want me to apply the function to my frmEMail / frmUpDatePassword?....
....not too sure. ?

In one of my new posts?...and expert has just provided an healthy critizism....he noticed some flaws and particularly in the current PerformEncrytion function

...and I am willing to open a new post just to deal with that issue. I also noticed your suggested Encryption / decryption on this particular post on Randomization....should I use that suggested code instead of the PerformEncryption function?

Here is the post.. Please contribute your opinion...if possible.

http://www.experts-exchange.com/Databases/MS_Access/Q_21882316.html#16883571

...I am confused.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16884032
rockiroads,
This code works fine at the UpdateUserPassword function...  if CheckAccessID = False then exit sub

If I want to utilize the same to prevent AccessID =0 from openeing the form, Is there any modifcation to the code?

I place the same code in the open event, I received a msgbox box that ..."Sorry, you do not have access to the operation" but as soon as I clicked the on button of the msgbox, it opened the form I was trying to stop from opening.

On frmEmail, I will make the function available only to the Developer and Admin.

As you've rightly asked..

if User has no e-mail and frmEmail opens then as sonn as the e-mail address is supplied...
then, a msgbox can pop up ..do you still want to send e-mail to this user? vbYes/No

if yes response...please send the random password via e-mail.

if "No" response..then stop processing the e-mail and return to frmUserDetails.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16884406
rockiroads,
Do you know where I can download the cdo.dll for Office 2003? I checked google and could not find it...I need it to test the SendEMail sample attached here.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16884549
rockiroads,
Please visit the link referred above and check out LSM Consulting's comment...I will really like to post a new question on this.....but I need your professional judgement...If you agree with what they are saying I will open a new post on the topic..

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16890553
rockiroads,
I have just tested the sample you uploaded to EE. I received an error:

Error in sending...The transport failed to connect to a server

...which Server...I dont know.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16891852
Hi Bill
CDO can be downloaded from MS - try this  http://www.microsoft.com/downloads/details.aspx?FamilyID=2714320d-c997-4de1-986f-24f081725d36&displaylang=en

The sample I given works but you have to modify it to specify your own smtp server



Ok, u've got a few posts, I will try go thru them

Q. If I want to utilize the same to prevent AccessID =0 from openeing the form, Is there any modifcation to the code?

Place in form_open module, this has a Cancel argument which we can make use of

private sub Form_Open(Cancel As Integer)
    If CheckAccessID = False then Cancel = True
end sub


Q. if "No" response..then stop processing the e-mail and return to frmUserDetails

Ok, leave original code, dont bother changing frmEmail as we do not need to update



Now to security, the encryption I have given does unfortunately produce some non readable characters.
What you can do is add this extra encryption when saving to the table, therefore someone reading it will not be able to see it.
It does mean you have to decrypt
You have 2 levels now.

How secure do u want your DB to be?

Here is one thing

If u have just the MDB, then u should lock down everything, just allow your forms. You can disable shortcuts, disable bypass, remove toolbars and menus, etc
Place passwords on VBA module
If u compile it down to MDE, u still need to do some but the users do not have access to most things


0
 
LVL 4

Author Comment

by:billcute
ID: 16891976
Thanks for your general advise. I will post something on Security lockdown just for more ideas.

Just by the way...besides the smpte server name that I need to supply, ...is there any modification I need to make on your sample module before it would be available for use?

Thanks for everything. although I have not tested the e-mail code..if I have any other problem..I'll open a new thread on the subject.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16892017
rockiroads,
If you have an idea on how user.billcute can access KCombodb from a remote location to change user.billcute's password, I will definitely appreciate you chip in your suggestion.

Rehards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 16892306
rockiroads,
I will appreciate any help with my new listing...hopefully these should resolve all my concerns with my app.

1. http://www.experts-exchange.com/Databases/MS_Access/Q_21884278.html
2. http://www.experts-exchange.com/Databases/MS_Access/Q_21884231.html

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16892380
Hi Bill,
that sample, yes one more change, u need to change the Sender's name/email. I think it might be fred@smith.com
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16892422
To access remotely, how remote do u mean. From another PC but on the same network?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question