Hello Everyone!! UPDATE query VBA Code

[Jllera]

Hi Everyone!!

I am trying to complete my updatepasssword form. I have a script written out for it so that it will update information in my table but its not. Can someone please take a look at it and tell what I am doing wrong.. I would like it to update the info in my Users table. Please advise....

Private Sub Update_Click()
Dim Passwords As Variant
Dim DateUpdated As Date
Dim ExpiredDate As Date
Dim dbs As Database
Dim loginID As String

Set dbs = CurrentDb

'Check to see if data is entered into the OldPassword text box
If IsNull(Me.OldPassword.Value) = True Then
MsgBox "Please enter Old Password", vbInformation, "Old Password Required"
Me.OldPassword.SetFocus
Exit Sub
End If

'Check to see if data is entered into the NewPassword text box
If IsNull(Me.NewPassword.Value) = True Then
MsgBox "Please enter New Password", vbInformation, "New Password Required"
Me.NewPassword.SetFocus
Exit Sub
End If

'Check to see if data is entered into the Confirm text box
If IsNull(Me.ConfirmPassword.Value) = True Then
MsgBox "Please confirm password", vbInformation, "Confirmation Required"
Me.ConfirmPassword.SetFocus
Exit Sub
End If

If Len(Me.NewPassword.Value) > 0 Then
dbs.Execute "Update(Passwords, DateUpdated, ExpiredDate) Users"


Set Passwords = "(Me.NewPassword.Value, '" & DateUpdated = CStr(Now()) & "', '" & ExpiredDate = CStr(Now()) & "')))"
End If

DoCmd.OpenForm "Apogee Payment Systems", acNormal, , acFormAdd
DoCmd.Close acForm, "UpdatePassword"
DoCmd.Close acForm, "Login"

End Sub

[June7]

I've never seen that syntax for UPDATE action. You are saving dates into a text field? Why?

dbs.Execute "UPDATE Users SET Passwords='" & Me.NewPassword & "', DateUpdated=CStr(Now()), ExpiredDate=CStr(Now())"

[Jllera]

The Only text field is NewPassword. That's the text box on the form that I want to update the table. DateUpdated and ExpiredDate are fields in the table. Should I try the code that you presented?

[June7]

Remove the CStr() functions if date values saved to Date/Time type field.

[Jllera]

I used the code you gave me and it worked but it did not update the password to the right user. How can alter it to do so?

[June7]

Sorry, need WHERE clause in the action.

What control has the userID?

[ssanfu]

It would be better to use

Dim dbs As DAO.Database

None of the other declarations are used. Why are they in the code?

Private Sub Update_Click()
    Dim dbs As DAO.Database

    Dim Passwords As Variant  ' not used - no value assigned 
    Dim DateUpdated As Date  ' not used - no value assigned
    Dim ExpiredDate As Date  ' not used - no value assigned
    Dim loginID As String   ' not used - no value assigned

Also, did you manually walk through the code?
Lets say my current password is "Password1" (yeah, I know - not so secure)

Step 1) Enter the old Password:
I enter "WordPass" into Me.OldPassword

Step 2) Enter the new Password:
I enter "ABC123" into Me.NewPassword

Step 3) Re-enter the new Password
I enter "DEF456" into Me.ConfirmPassword

Step 4) Update the password.

Nowhere in your code did you check to see if the value in Me.OldPassword matches the current value for the password. If the old password doesn't match the entered password, shouldn't the password update be canceled?

Neither did you check to see if Me.NewPassword matched Me.ConfirmPassword. If Me.NewPassword doesn't match Me.ConfirmPassword, shouldn't the update also fail?

You just check to ensure that three values have been entered, then update the password.

Maybe you might want to rethink this bit of code???


Not trying to tear you down..... you did ask for advice. These are the problems that jumped out at me.

[Jllera]

Something like ?

WHERE LoginID = Me.NewPassword.Value

In my table the userID is labled LoginID. On my ok button procedure for my login form I delclared my LoginID as follows:

Dim LoginID(7) as string

LoginID(1) = userID

will that work?

[Jllera]

I am extremely new to access and programming. I didn't think of any of that. Can you help me get my script where it should be please?

[Jllera]

if we disregard the previous threads to each other and I send you my login script and updatepassword script can you help fix my problem and give some insight on how to foresee the issues in the future?

[Jllera]

in other words help me understand how to write code properly

[ssanfu]

I can't tell you how to write the code. It is the job of the developer/programmer to understand the problem/process, then try and think of everything that can go wrong/ be done wrong. Write code to make it idiot proof.

Not an easy job - witness Microsoft (for example) and their constant patches.

Not so much now, but I used to use paper and pencil to write what the procedure is supposed to do (define the problem), write pseudo code (how the process might be done), THEN enter the actual code in the IDE (way more detail - step by step).
It is similar to what I wrote in Post #7 for the password change.


Keep working on it.... post code if you have questions. Lots of great programmers here.

Good luck with your project.

[June7]

Re Post 8 - why would you compare LoginID to Password?