Runtime error "invalid Operation" 3219

[aditijangir]

Hi Friends,

I am a newbie in MS ACCESS. I am using A2010 Version. What I am expecting my code to do is: Compare the field of the table and update other field in the same row.

My code will make sense better htan words :)

Dim zDB As DAO.Database
     Dim zRS As DAO.Recordset
     Dim zSQL As String
     '
     zSQL = "UPDATE AddUser SET [Password]='" & Pwd & "' WHERE AddUser.EmployeeId='" & User & "';"
    
     '
     'See here is where I set the pointer to the current
     'database only once
     Set zDB = CurrentDb
     '
     'using recordsets...
    [B]Set zRS = zDB.OpenRecordset(zSQL, dbOpenSnapshot)  'getting ERROR in this line
[/B]     '
     '
     zRS.close
     If Not zRS Is Nothing Then Set zRS = Nothing
     If Not zDB Is Nothing Then Set zDB = Nothing

Need urgent help. Please provide valuable feedback.

[Seth Schrock]

The OpenRecordset method can't be used on UPDATE queries as it doesn't return any records. Instead you would do the following.

zDB.Execute zSQL, dbFailOnError

You can get rid of the zRS declaration in line 2 and all other lines that reference it.

[aditijangir]

Thank you so much for your reply.

System is still throwing an error after I made the provided changes.

ERROR: 128
Datatype mismatch

Note: EmployeeId is an Primary Key with Numeric datatype

Have a look to the code:

Dim zDB As DAO.Database
  '  Dim zRS As DAO.Recordset
     Dim zSQL As String

Set zDB = CurrentDb
zSQL = "UPDATE AddUser SET [Password]='" & Pwd & "' WHERE AddUser.EmployeeId='" & User & "';"
     zDB.Execute zSQL, dbFailOnError
  
    ' zRS.close
    ' If Not zRS Is Nothing Then Set zRS = Nothing
     If Not zDB Is Nothing Then Set zDB = Nothing

Please provide your feedback.

[Seth Schrock]

"Note: EmployeeId is an Primary Key with Numeric datatype"

This is the key to your problem. You are passing the User as a string because of the single quotes. Make your zSQL variable WHERE clause be the following.

WHERE AddUser.EmployeeId=" & User & ";"

[zmbd]

To provide context: Check for existing EmployeeId in the table and then update password in same table- post#8
(I also answered the reason for Error-3219 in this post :) )

[aditijangir]

Oh. Small mistake lead me in trouble :P

Thank you so much seth. I am really thankful for solving my problem. :D

[aditijangir]

Thank you zmdb for your wonderful help . :)