update access table thru form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a access database, with a table called VALUES, in the table there is a
Text column called ACTIVE, by default it is "Y". I want to change (update)
the value from "Y" to "N".

I know how to do it in sql (KINDA) but not access, in sql I would:

UPDATE VALUES SET ACTIVE = 'N' WHERE VALUE = DLookup("[VALUE]", "VALUES",
"[R_NUMBER] = 1")

Could anyone get this in the right format for me
 
You can use your SQL to do that from within Access:

strSQL = "UPDATE VALUES SET ACTIVE = 'N' WHERE [R_NUMBER] = 1;"
CurrentDb.Execute(strSQL), dbFailOnError
 
I get an error on the last line of your code

Klatuu said:
You can use your SQL to do that from within Access:

strSQL = "UPDATE VALUES SET ACTIVE = 'N' WHERE [R_NUMBER] = 1;"
CurrentDb.Execute(strSQL), dbFailOnError


tiredoftrying said:
I have a access database, with a table called VALUES, in the table there is a
Text column called ACTIVE, by default it is "Y". I want to change (update)
the value from "Y" to "N".

I know how to do it in sql (KINDA) but not access, in sql I would:

UPDATE VALUES SET ACTIVE = 'N' WHERE VALUE = DLookup("[VALUE]", "VALUES",
"[R_NUMBER] = 1")

Could anyone get this in the right format for me
Click to expand...
Click to expand...
 
First, when you report an error, the error number and description would be
nice to have; however, in this case, I believe the problem is with the SQL
statement. That line of code is correct. All it does is send the SQL string
to Jet to be processed. If you remove the dbFailOnError, you will not get an
error, nor will get get the expected results. I only guessed at the SQL
based on your original post. Clean the SQL up, and it will work as expected.
Try it this way:

strSQL = "UPDATE VALUES SET VALUES.ACTIVE = 'N' WHERE (([R_NUMBER] = 1));"

tiredoftrying said:
I get an error on the last line of your code

Klatuu said:
You can use your SQL to do that from within Access:

strSQL = "UPDATE VALUES SET ACTIVE = 'N' WHERE [R_NUMBER] = 1;"
CurrentDb.Execute(strSQL), dbFailOnError


tiredoftrying said:
I have a access database, with a table called VALUES, in the table there is a
Text column called ACTIVE, by default it is "Y". I want to change (update)
the value from "Y" to "N".

I know how to do it in sql (KINDA) but not access, in sql I would:

UPDATE VALUES SET ACTIVE = 'N' WHERE VALUE = DLookup("[VALUE]", "VALUES",
"[R_NUMBER] = 1")

Could anyone get this in the right format for me
Click to expand...
Click to expand...
Click to expand...
 
Back
Top