Update a table value dependent on another value (1 Viewer)

[xyba]

New users to my db are assigned a default password ("password") but I want them to change this at first login and update the record password field in the table.

I'm assuming I would need to use a recordset? However I've never worked with recordsets so I'm unsure where to start so I'd be grateful for some help.

The current code for the on load event of the form is below and the form field the user would enter the new password is newPwdtxt

Private Sub LoginBtn_Click()


'Check to see if data is entered into the UserName combo box

    If IsNull(Me.TxtUsername) Or Me.TxtUsername = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.TxtUsername.SetFocus
        Exit Sub
    End If
            
    'Check to see if data is entered into the password box

    If IsNull(Me.TxtPassword) Or Me.TxtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.TxtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.TxtPassword.Value = DLookup("strEmpPassword", "tblUsers", _
            "[1ngEmpID]=" & Me.TxtUsername.Value) Then

        lngMyEmpID = Me.TxtUsername.Value
     
               
   'Close logon form and open splash screen

        DoCmd.Close acForm, "Login", acSaveNo
        DoCmd.OpenForm "Navigation Form"
       

    Else
      MsgBox "Invalid Password. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.TxtPassword.SetFocus
        Me.TxtPassword = Null
    End If

 
End Sub

 

[xyba]

I've tried adding extra code to achieve the result I'm after. This extra code is in red below but it's throwing up a runtime 3061 error "Too few parameters, Expected 1".

Firstly, should the extra code give me the result I'm looking for and, if so, what have I done wrong to get the runtime error?

Private Sub LoginBtn_Click()
[COLOR="Red"]Dim strPWDSQL As String
strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = Me.NewPwdtxt"[/COLOR]


'Check to see if data is entered into the UserName combo box

    If IsNull(Me.TxtUsername) Or Me.TxtUsername = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.TxtUsername.SetFocus
        Exit Sub
    End If
            
    'Check to see if data is entered into the password box

    If IsNull(Me.TxtPassword) Or Me.TxtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.TxtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.TxtPassword.Value = DLookup("strEmpPassword", "tblUsers", _
            "[1ngEmpID]=" & Me.TxtUsername.Value) Then

        lngMyEmpID = Me.TxtUsername.Value
        
        If Me.TxtPassword = "password" Then
        Me.NewPwdlbl.Visible = True
        Me.NewPwdtxt.Visible = True
        [COLOR="red"]CurrentDb.Execute strPWDSQL[/COLOR]
               
    End If
        
        'Close logon form and open splash screen
        DoCmd.Close acForm, "Login", acSaveNo
        DoCmd.OpenForm "Navigation Form"
       

    Else
      MsgBox "Invalid Password. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.TxtPassword.SetFocus
        Me.TxtPassword = Null
    End If

 
End Sub

 

[pbaldy]

You have to concatenate:

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "'"

 

[pbaldy]

Oh, and you're going to want to add a criteria. That will update all records to the same thing.

 

[arnelgp]

some litte addition, that's all:

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID='" & Me.txtUserName & "' And strPassword='" & Me.txtPassword & "'"

 

[xyba]

some litte addition, that's all:

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID='" & Me.txtUserName & "' And strPassword='" & Me.txtPassword & "'"

Thanks arnel. That throws up the following error though:

Run-time error 3075 – Syntax error (missing operator) in query expression ‘1ngEmpID=’1’ And strPassword=’password”

 

[arnelgp]

another try:

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID='" & Me.txtUserName & "' And strEmpPassword ='" & Me.txtPassword & "'"

 

[xyba]

another try:

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID='" & Me.txtUserName & "' And strEmpPassword ='" & Me.txtPassword & "'"

Still the same error.

 

[arnelgp]

then what are the fieldname on tblusers for username and password? I only borrow the fieldname from your code?
what are their fieldtype numeric, text or what???

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID=" & Me.txtUserName & " And strEmpPassword ='" & Me.txtPassword & "'"

 

[The_Doc_Man]

Just as a side note:

You have

If IsNull(Me.TxtUsername) Or Me.TxtUsername = "" Then

but this can be written more concisely as

If NZ( Me.TstUsername, "" ) =  "" Then

The same can be done for the password case.

You also have

If Me.TxtPassword.Value =

but here you can omit the .Value because that is the default property for anything that has a value property at all.

The other advice you are getting is on point so I won't comment directly. However, I will explain the error you got. Look at the stuff I highlighted with color below:

error 3075 – Syntax error (missing operator) in query expression ‘1ngEmpID=’1’ And strPassword=’password”

That mixed quoting is the problem. Inside a double-quoted string, apostrophes count as single-quotes. You have an isolated single quote that has, in effect, left something dangling. So that triggered the error parser to barf. It makes me think that you didn't exactly copy what Arnel told you because what was in his post DOES have the correct punctuation. Is there a chance that the input in txtPassword ended with an apostrophe? Because that would throw things out of whack quickly.

 

[xyba]

then what are the fieldname on tblusers for username and password? I only borrow the fieldname from your code?
what are their fieldtype numeric, text or what???

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID=" & Me.txtUserName & " And strEmpPassword ='" & Me.txtPassword & "'"

tblUsers
1ngEmpID – Autonumber
strEmpName – Text
strEmpPassword – Text

Login form
TxtUsername
TxtPassword
NewPwdTxt

 

[arnelgp]

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID=" & lngMyEmpID
& " And strEmpPassword ='" & Me.txtPassword & "'"

 

[xyba]

Just as a side note:

That mixed quoting is the problem. Inside a double-quoted string, apostrophes count as single-quotes. You have an isolated single quote that has, in effect, left something dangling. So that triggered the error parser to barf. It makes me think that you didn't exactly copy what Arnel told you because what was in his post DOES have the correct punctuation. Is there a chance that the input in txtPassword ended with an apostrophe? Because that would throw things out of whack quickly.

Thanks for your advice on removing .value etc.

I copied and pasted direct without any changes, and no apostrophe entered in the field either.

 

[xyba]

strPWDSQL = "UPDATE tblUsers SET tblUsers.strEmpPassword = '" & Me.NewPwdtxt & "' " & _
"Where 1ngEmpID=" & lngMyEmpID
& " And strEmpPassword ='" & Me.txtPassword & "'"

I'm still getting the same error for some reason.

Sorry to be a pain

 

[xyba]

I've attached a copy of my database in case it can aid in spotting why I'm getting the error.

 

[arnelgp]

here is the fix.

 

[xyba]

here is the fix.

Perfect!

Thank you very much for your help arnel.