By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,651 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

Runtime error "invalid Operation" 3219

P: 17
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 :)

Expand|Select|Wrap|Line Numbers
  1. Dim zDB As DAO.Database
  2.      Dim zRS As DAO.Recordset
  3.      Dim zSQL As String
  4.      '
  5.      zSQL = "UPDATE AddUser SET [Password]='" & Pwd & "' WHERE AddUser.EmployeeId='" & User & "';"
  6.  
  7.      '
  8.      'See here is where I set the pointer to the current
  9.      'database only once
  10.      Set zDB = CurrentDb
  11.      '
  12.      'using recordsets...
  13.     Set zRS = zDB.OpenRecordset(zSQL, dbOpenSnapshot)  'getting ERROR in this line
  14.      '
  15.      '
  16.      zRS.close
  17.      If Not zRS Is Nothing Then Set zRS = Nothing
  18.      If Not zDB Is Nothing Then Set zDB = Nothing
  19.  
  20.  
Need urgent help. Please provide valuable feedback.
May 13 '15 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. WHERE AddUser.EmployeeId=" & User & ";"

Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
The OpenRecordset method can't be used on UPDATE queries as it doesn't return any records. Instead you would do the following.
Expand|Select|Wrap|Line Numbers
  1. zDB.Execute zSQL, dbFailOnError
You can get rid of the zRS declaration in line 2 and all other lines that reference it.
May 13 '15 #2

P: 17
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:

Expand|Select|Wrap|Line Numbers
  1. Dim zDB As DAO.Database
  2.   '  Dim zRS As DAO.Recordset
  3.      Dim zSQL As String
  4.  
  5. Set zDB = CurrentDb
  6. zSQL = "UPDATE AddUser SET [Password]='" & Pwd & "' WHERE AddUser.EmployeeId='" & User & "';"
  7.      zDB.Execute zSQL, dbFailOnError
  8.  
  9.     ' zRS.close
  10.     ' If Not zRS Is Nothing Then Set zRS = Nothing
  11.      If Not zDB Is Nothing Then Set zDB = Nothing
  12.  
Please provide your feedback.
May 13 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,951
"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.
Expand|Select|Wrap|Line Numbers
  1. WHERE AddUser.EmployeeId=" & User & ";"
May 13 '15 #4

zmbd
Expert Mod 5K+
P: 5,397
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 :) )
May 13 '15 #5

P: 17
Oh. Small mistake lead me in trouble :P

Thank you so much seth. I am really thankful for solving my problem. :D
May 14 '15 #6

P: 17
Thank you zmdb for your wonderful help . :)
May 14 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.