help with update

[ifoundgoldbug]

Greetings.

i have a quick question could someone post some sample code which would
go through the entirity of a database and check the value of each
record in 1 field and use an if statement to change it's value.

IE.

while not db.eof

if dept = 501 then
dept = 15501
end if

loop

 

[Brendan Reynolds]

You need to specify which table within the database you want to update, and
while it would be possible to do it by looping through a recordset, it may
be significantly more efficient to do it with an update query. Something
like ...

CurrentProject.Connection.Execute "UPDATE SomeTable SET dept = 501 WHERE
dept = 15501"

If 'dept' is a text field, you'll need quotes around the values ...

CurrentProject.Connection.Execute "UPDATE SomeTable SET dept = '501' WHERE
dept = '15501'"

 

[ifoundgoldbug]

thanks that worked very well

Gold Bug

You need to specify which table within the database you want to update, and
while it would be possible to do it by looping through a recordset, it may
be significantly more efficient to do it with an update query. Something
like ...

CurrentProject.Connection.Execute "UPDATE SomeTable SET dept = 501 WHERE
dept = 15501"

If 'dept' is a text field, you'll need quotes around the values ...

CurrentProject.Connection.Execute "UPDATE SomeTable SET dept = '501' WHERE
dept = '15501'"