Changing a value in a table

[cfoye130]

I'm a noob to access VBA so sorry for what seems like an easy question.

On the open of a Form titled "UpdateData" I want to run a macro that will change the value of ID 1, field "Data Update" to a value of 1, in table "Update Status." No idea how to do this but I think it can be done right?

Any help would be greatly appreciated! Thank you!

 

[Montez659]

To do it in VBA the SQL string would be something like:

UPDATE Update_Status SET Update_Status.Data_Update = "1"
WHERE (([Update_Status]![ID]="1"));

This is not perfect, but this and some research might get you headed in the right direction before someone corrects my mistakes. Just google SQL update and you can compare some stuff to see if I messed up.

Things to note: Make sure to include the code to connect to the database. Also, you might want to change your table names and field names to either have an underscore or no space at all where there are spaces (kind of like I did with Update_Status). Spaces make SQL stuff go mad!

 

[cfoye130]

That works. Thanks. Knew it was something simple.... so why does this not work if I am trying to empty a different field.....

sql = "UPDATE Update_Status SET Data_Through_Date = NULL WHERE ID=1"

 

[Montez659]

SQL is quite finicky (sp?). Make sure you include the parentheses, brackets, and table refs in the SQL. Try

UPDATE Update_Status SET Update_Status.Data_Through_Date = NULL
WHERE (([Update_Status]![ID]="1"));

Let me know if that works.

 

[james_lankford]

That works. Thanks. Knew it was something simple.... so why does this not work if I am trying to empty a different field.....

sql = "UPDATE Update_Status SET Data_Through_Date = NULL WHERE ID=1"

you may have the table design setup so that Data_Through_Date cannot be null

also,

do you actually have a column named ID ?
and is that column an integer, a long, or a text field ?

</pre>

 

[cfoye130]

I'll take a look.

 

[cfoye130]

That was exactly it, I had that field as required (duh). Thanks guys. I'm all good!

 

[Montez659]

I learned something. It is a good day after all!