By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Move Value from one field to another of the same record.

P: 18
I need to move the value from one field to another field within the same record through VBA Code.

I have a table ("tblUsers") with fields: Password, Password1, Password2, and Password3. When the user changes their password, I want their old passwords to push down to the next field. I do this so the users can't use the last 3 passwords used.

I was thinking it would have been something simple like:
Expand|Select|Wrap|Line Numbers
  1. tblUsers.Password3.value = tblUsers.Password2
  2. tblUsers.Password2.value = tblUsers.Password1
  3. tblUsers.Password1.value = tblUsers.Password
  4. tblUsers.Password.value = me.txtPassword
I'm running this code from a form, the table is not accessible to any user and these old passwords are not on any forms. All password fields have "Password" masking at the table level.
Dec 1 '13 #1

✓ answered by ADezii

Let's assume that:
  1. You have a User named Fred.
  2. He changes his Current Password to AArdvArk.
  3. He wishes to maintain a Password History of his last 3 Passwords and 'Push' this Values down to Pwd2, Pwd3, and Pwd4. Pwd1 will be his 'New' Password.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim strUser As String
    4. Dim strNewPassword As String
    5.  
    6. strUser = "Fred"
    7. strNewPassword = "AArdvArk"
    8.  
    9. Set MyDB = CurrentDb
    10. Set rst = MyDB.OpenRecordset("SELECT * FROM tblUsers WHERE [UserName] = '" & strUser & "'", dbOpenDynaset)
    11.  
    12. If rst.BOF And rst.EOF Then
    13.   rst.Close
    14.     Set rst = Nothing
    15.       Exit Sub    'No Record
    16. End If
    17.  
    18. With rst
    19.   .Edit
    20.     ![Pwd4] = ![Pwd3]                'Move 3 down to 4
    21.     ![Pwd3] = ![Pwd2]                'Move 2 down to 3
    22.     ![Pwd2] = ![Pwd1]                'Move 1 down to 2
    23.     ![Pwd1] = strNewPassword         'Assign New Password
    24.   .Update
    25. End With
    26.  
    27. rst.Close
    28. Set rst = Nothing
    29.  
  4. Any questions, feel free to ask.

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,221
I'm not sure the format of your instructions shown is correct. If this is on a form I would expect:
Expand|Select|Wrap|Line Numbers
  1. me.Password3.value = me.password2
etc...
You could hide all the passwords but the one you expect the user to edit. You don't say when or how this form is run or anything about the event handling on the form. I would think the first 3 lines would run in the BeforeUpdate event of the password textbox and the last line wouldn't be necessary if tblusers.password was bound to the password textbox.

Jim
Dec 1 '13 #2

ADezii
Expert 5K+
P: 8,599
Let's assume that:
  1. You have a User named Fred.
  2. He changes his Current Password to AArdvArk.
  3. He wishes to maintain a Password History of his last 3 Passwords and 'Push' this Values down to Pwd2, Pwd3, and Pwd4. Pwd1 will be his 'New' Password.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim strUser As String
    4. Dim strNewPassword As String
    5.  
    6. strUser = "Fred"
    7. strNewPassword = "AArdvArk"
    8.  
    9. Set MyDB = CurrentDb
    10. Set rst = MyDB.OpenRecordset("SELECT * FROM tblUsers WHERE [UserName] = '" & strUser & "'", dbOpenDynaset)
    11.  
    12. If rst.BOF And rst.EOF Then
    13.   rst.Close
    14.     Set rst = Nothing
    15.       Exit Sub    'No Record
    16. End If
    17.  
    18. With rst
    19.   .Edit
    20.     ![Pwd4] = ![Pwd3]                'Move 3 down to 4
    21.     ![Pwd3] = ![Pwd2]                'Move 2 down to 3
    22.     ![Pwd2] = ![Pwd1]                'Move 1 down to 2
    23.     ![Pwd1] = strNewPassword         'Assign New Password
    24.   .Update
    25. End With
    26.  
    27. rst.Close
    28. Set rst = Nothing
    29.  
  4. Any questions, feel free to ask.
Dec 1 '13 #3

P: 18
Thank you... That's essentially what I was looking for (how to update a value of a record in a table) and useable to me in other areas as well!
Dec 2 '13 #4

ADezii
Expert 5K+
P: 8,599
Glad it worked out for you.
Dec 2 '13 #5

zmbd
Expert Mod 5K+
P: 5,287
ADezii, as usual, provides some very nice code; however, SgtTurbo this can be made a little more robust with some minor changes to the Database Normalization and Table Structures.

I'd also like to address the storing of passwords in plain text which can easly be solved by salting and digesting the passwords.

1) storing the passwords:
SHA2 Cryptographic Hash Algorithm for VBA and VBScript and (however, falling out of favor vs. SHA2 is the older) Generate secure user passwords using the MD5 hash class module

Most security experts will recomend using a "salt" to help obscure the base password; thus, making a straight dictionary type attack more difficult. A "salt" is a random number that is (most simply) appened to the actual password. From what I understand it should ideally be the same length as the final digest.
Normally, I generate the salt, don't wory too much about the length for my application, store this number.
I append the salt to the the password and calculate the SHA2, SHA2 the salt, append the two digests and finally togeither, SHA2 the entire thing and store this. Once again, from what I've read, there is no issue with storing the salt in plain text.
Why do this, say User1 and User2 both use "Apple" as their password, SHA2 will return the exact same digest.
IF you salt the password as I've indicated, at minimun with the simple appending of the random salt, then, even though the password used by both is the same, the stored digest is not the same because the acutual password was the salt and the user's password.
(you should find this to be a fairly straight forward explanation in detail of the above: How to encrypt user passwords This is for JAVA; however, it's the base theory which applies and is what I'm after here not the code.

2) the normaliztion.
Create a table
tbl_rollingpassword
[rollingpassword_pk] autonumber - primary key
[rollingpassword_date] date
[rollingpassword_fk_user] numeric(long) 1:M with your user table primary key
[rollingpassword_salt] numeric(long)
[rollingpassword_SHA2Digest] text(255)

Your recordset would poll on the [rollingpassword_fk_user] field for the user. Check for the 3 records, delete the oldest and save the newest.

Now to make this better (and what I have done) is that I don't link this table to the user table, nor do store the user's name in plain text.

tbl_rollingpassword
[rollingpassword_pk] autonumber - primary key
[rollingpassword_date] date
[rollingpassword_SHA2User] text(255)
[rollingpassword_salt] numeric(long)
[rollingpassword_SHA2Digest] text(255)

[rollingpassword_SHA2User] = the network login that I pull from an API call and their database login name that is then put thru the SHA2.

The nice thing here is that say you want to REALLY annoy your users, you can do like my company's policy on passwords and disallow using the same password that has been used within the last 13 months; thus, a user changes the password 50 times in a month... sucks to be them because all of those passwords hang around for 13 months.

Now I know, due to the length, I'll most likely crosspost with one of the other experts so my applogies in advance...
(^_^)
Dec 2 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.