CODEWITHSUNDEEP
sqlms-accessvba
T-Rex
T-Rex

Reputation: 161

Unclosed Quotation mark after the character string 'test'

I have been racking my brain with searching on the internet for the solution, but to no avail, I have been unsuccessful. 

strSQL = "Update tTbl_LoginPermissions SET LoginName = '" & StrUserName & "', PWD = '" & StrPWD & "', fldPWDDate = '" & Now() & "'" & _
     "WHERE intLoginPermUserID = " & MyMSIDColumn0

Once I get the error out, I would like to actually use this where clause:

'WHERE intLoginPermUserID IN (SELECT intCPIIUserID From vw_ADMIN_Frm_LoginBuilder)

Here is the entire code:

Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strSQL As String
    Const cSQLConn = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey01;PWD=Mouse02;DATABASE=Regulatory;"

Dim StrUserName As String, StrPWD As String

'passing variables
StrUserName = FindUserName()
StrPWD = EncryptKey(Me.TxtConPWD)

    'Declaring the SQL expression to be executed by the server
     strSQL = "Update tTbl_LoginPermissions SET LoginName = '" & StrUserName & "', PWD = '" & StrPWD & "', fldPWDDate = '" & Now() & "#" & _
     "WHERE intLoginPermUserID = " & MyMSIDColumn0
     'WHERE intLoginPermUserID = ANY (SELECT intCPIIUserID From vw_ADMIN_Frm_LoginBuilder)

     Debug.Print strSQL
    'connect to SQL Server
    Set con = New ADODB.Connection
    With con
        .ConnectionString = cSQLConn
        .Open
    End With

    'write back
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandText = strSQL
        .CommandType = adCmdText
        .Execute
        Debug.Print strSQL
    End With

    'close connections
    con.Close
    Set cmd = Nothing
    Set con = Nothing

       MsgBox "You password has been set", vbInformation + vbOKOnly, "New Password"

NEWEST CODE Producing Error:

 '/Declaring the SQL expression to be executed by the server
    strSQL = "Update dbo_tTbl_LoginPermissions " _
    & "SET LoginName = '" & StrUserName & "' " _
    & "SET PWD = '" & StrPWD & "' " _
    & "SET fldPWDDate = '" & Now() & "' " _
    & "WHERE intLoginPermUserID = 3;"

I have gone to this site to try to figure out my mistake, but I still cannot figure it out:

Upvotes: 0

Views: 8321

Answers (3)

T-Rex
T-Rex

Reputation: 161

After much dilberation and help, it turns out the FindUserName that utilizes a Win32API function was not trimming the Username appropriately. I changed it to the following:

Public Function FindUserName() As String
    ' This procedure uses the Win32API function GetUserName
    ' to return the name of the user currently logged on to
    ' this machine. The Declare statement for the API function
    ' is located in the Declarations section of this module.

    Dim strBuffer As String
    Dim lngSize As Long

    strBuffer = Space$(255)
    lngSize = Len(strBuffer)

    If GetUserName(strBuffer, lngSize) = 1 Then
        FindUserName = Left$(strBuffer, lngSize - 1)
    Else
        FindUserName = "User Name not available"
    End If

 End Function

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Upvotes: 1

T-Rex
T-Rex

Reputation: 161

This was inevitably the code that corrected my (') mystery:

'/passing variables
StrUserName = FindUserName()
StrPWD = EncryptKey(Me.TxtConPWD)
StrUserId = Me.CboUser.Column(0)

 '/Declaring the SQL expression to be executed by the server
   strSQL = "Update tTbl_LoginPermissions SET " _
    & "LoginName = '" & StrUserName & "' , " _
    & "PWD = '" & StrPWD & "' ," _
    & "fldPWDDate = '" & Now() & "' " _
    & "WHERE intLoginPermUserID = '" & StrUserId & "'"

Upvotes: 0

John Bingham
John Bingham

Reputation: 2006

Try this:

strSQL = "Update tTbl_LoginPermissions SET LoginName = '" & replace(StrUserName, "'", "''") & "', PWD = '" & replace(StrPWD, "'", "''") & "', fldPWDDate = '" & Now() & "'" & _
 "WHERE intLoginPermUserID = " & MyMSIDColumn0

Upvotes: 0

Related Questions