Changing field values 1

[kkarren]

I want to change the value of a field from say 0 to 1 in all records. What syntax can Use to make this universal change. This would be like doing a find and replace all in the table. I want to do this in a VBA setting. Can anyone help me? Thanks for your time. [sig][/sig]

 

[MichaelRed]

Yes, but ....

This is really against the concept/reason of database management/programming. This is always(?) done with an update query.

The query will be FASTER than the looping VBA code to do the same thing.

The query will be easier to use than a function which resides in some obscure code module. You won't rember the name later, and it will be difficult to find, where the query can 'sit' in your database window with a description of what is is for and is much more accessible.

Someday, someone will somehow trigger the function - and anychanges made to the table since then will be -"poof - Gone". The query - if accidently triggered will at least warn the user he/she is about to change XXX records and they will have SOME opportunity to reflect on the consequences - and 'do the right thing'.

Having been through all that, if you just want to 'see' how it's done:


Public Function basSetFieldToValue()

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("MyTable", dbOpenDynaset)

'SQL for Update Query
'UPDATE MyTable SET MyTable.MyField = 1;


While Not rst.EOF
With rst
.Edit
MyField = 1
.Update
End With
rst.MoveNext
Wend

End Function


Where, as always, the items in italics need to be replaced with specific values from your application [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]