Need Help with VBA Code to Update Field to ""

[RHONDAK72]

Hello,

Can anyone provide example some VBA code that would remove the existing value of a field for a specific record?

Example:

.[Field] currently equals "John Smith". I want to clear out "John Smith" so that nothing is in

.[Field].

Thanks
Rhonda

 

[james_lankford]

make a copy of your database before running this

then run this and test it to make sure it gives what you want

'******************************************
Sub doit()


    Dim sql As String
    
    sql = ""
    sql = sql & "Update " & vbCrLf
    sql = sql & "  my_table " & vbCrLf
    sql = sql & "set " & vbCrLf
    sql = sql & "  my_field = Null " & vbCrLf
    sql = sql & "where " & vbCrLf
    sql = sql & "(  " & vbCrLf
    sql = sql & "  ( " & vbCrLf
    sql = sql & "    my_table.my_field = 'John Smith' " & vbCrLf
    sql = sql & "  )  " & vbCrLf
    sql = sql & ")  " & vbCrLf
    
    MsgBox sql
    
    CurrentDb().Execute sql
    
End Sub
'******************************************

 

[Micron]

The vbcrlf is pointless, sorry to say. There is no need to make the code resemble the line breaks you see in a query's sql view. Access removes them anyway. In this simple case, ( 's are not needed either.
Coded sql should look like:

svSql = "UPDATE tblName SET tblName.FldName = NULL WHERE " 'if you need a line break in code here...
svSql = svSql & "tblName.FldName='John Smith';"

Notes:
- easiest way is to create a query that works, then go to sql view and copy/paste into your module, then edit using your preferred method.
- watch nested quotes (see 'John Smith'). Using " will cause error, as will failure to use # around dates. Using variables within these quotes requires you to concatenate:
"tblName.FldName=' " & svPersonName & " ';" I have added a space between the ' and " only so you can see it better - should not be in your actual string.
- develop a habit of ending OR beginning each line with the needed space character (see WHERE )
-if you make this a function instead of a sub, you can call it from the immediate window to test its result. Subs cannot be called this way.

Function EditField() 'James's way of using currentdb.execute is simpler, but I'm in this habit. Things have a way of expanding on me!
Dim svSql as String
Dim db as DAO.Database

Set db = Currentdb

svSql = "UPDATE tblName SET tblName.FldName = NULL WHERE " 'if you need a line break in code here...
svSql = svSql & "tblName.FldName='John Smith';"
db.Execute svSql, dbfailonerror

Set db = Nothing
End Function

 

[Micron]

It's really stupid that there are limits on editing your post in this forum. I forgot to point out that you can only set the field to Null if the field properties permit it. You might be liminted to empty strings ("").