Value (1 Viewer)

[TajikBoy]

Hi guys,

I have this code for password change on a form, but cannot get it to work, keeps asking value for intUserID on execution, where as I already have assigned in green highlight , cboEmployee is a unbound filed on the form

WHere am I going wrong?

Private Sub Command147_Click()
Dim intUserID As Integer
Dim strOldPassword As String
'Check to see if data is entered into the password box
    If IsNull(Me.TxtCurrent) Or Me.TxtCurrent = "" Then
        MsgBox "You must enter your current password", vbOKOnly, "Required Data"
        Me.TxtCurrent.SetFocus
        Exit Sub
    End If
    If Me.txtNew <> Me.TxtNew2 Then
        MsgBox "You must enter your new password correctly in fields", vbOKOnly, "Required Data"
        Me.txtNew.SetFocus
        Exit Sub
    End If

intUserID = Me.cboEmployee
strOldPassword = DLookup("[strEmpPassword]", "tblEmployees", "[lngEmpID]=" & intUserID)

Select Case strOldPassword
Case Is = Me.TxtCurrent
    
    UserID = Me.cboEmployee
    Me.txtNew.SetFocus
    Dim strSQL As String
    strSQL = "UPDATE tblEmployees SET strEmpPassword = '" & Me.txtNew.Text & "' WHERE lngEmpID = intUserID"
    DoCmd.RunSQL strSQL

 

[Gasman]

That is because you have not concatenated intuserid into the SQL string

 

[TajikBoy]

That is because you have not concatenated intuserid into the SQL string

Sorry how do I do that? Im not that great when it comes to SQL

 

[Gasman]

Sorry how do I do that? Im not that great when it comes to SQL

Well you managed in the Dlookup() ?
You have also managed with Me.txtNew.Text in that sql string?

 

[TajikBoy]

Well you managed in the Dlookup() ?
You have also managed with Me.txtNew.Text in that sql string?

LOL... Absolute pure luck my friend......

 

[Gasman]

LOL... Absolute pure luck my friend......

strSQL = "UPDATE tblEmployees SET strEmpPassword = '" & Me.txtNew.Text & "' WHERE lngEmpID = " & intUserID

assuming intUserID is numeric

Tip:
Add a Debug.Print strSQL after such code and then you can check it is correct. Then comment out or delete when working.
Do the same for DLookUp() if more than one criteria. I would build the criteria and then use that in the domain function.

 

[TajikBoy]

strSQL = "UPDATE tblEmployees SET strEmpPassword = '" & Me.txtNew.Text & "' WHERE lngEmpID = " & intUserID

assuming intUserID is numeric

Tip:
Add a Debug.Print strSQL after such code and then you can check it is correct. Then comment out or delete when working.
Do the same for DLookUp() if more than one criteria. I would build the criteria and then use that in the domain function.

Thanks a lot mate, I'm seriously struggling with " and 's when it comes to SQL, will learn one day....

 

[Gasman]

If it is text surround with single quotes, unless the string has a single quote like Mc'Neil, then I think 3 double quotes will work? I allways need to experiment.
Numerics need no surrounding characters
Dates need #

Again debug.print is your friend.

 

[theDBguy]

Thanks a lot mate, I'm seriously struggling with " and 's when it comes to SQL, will learn one day....

Hi. See if this article helps clarify some of that.

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com