Using eval() for flexible recordset fieldnames 1

[waldemar]

and another (probably) quick one:

This function is supposed to empty a specified field in a table. The function accepts a parameters for the name of the field.... but what is correct eval() usage?

Function emptyfield(tablename As String, fieldname As String) As Boolean
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [" & tablename & "];", dbOpenDynaset)
If rs.EOF = True And rs.BOF = True Then
emptyfield = False
rs.Close: Set rs = Nothing: db.Close: Set db = Nothing
Exit Function
End If
rs.MoveFirst
Do Until rs.EOF = True
rs.Edit
rs![eval(fieldname)] = &quot;&quot; <<<<<<< !!!!
rs.Update
rs.MoveNext
Loop

rs.Close: Set rs = Nothing: db.Close: Set db = Nothing
End Function

appreciate any ideas...
regards
waldemar

 

[waldemar]

Eval (&quot;rs![&quot; & fieldname & &quot;] = &quot; & Chr$(34) & Chr$(34))

didnt work either...

 

[LouieLyle]

Use the following syntax
rs(fieldname)=&quot;&quot;
This works even if the fieldname has spaces in it. You do not need string delimiters around fieldname because it is a variable. However, if the field does not accept zero length values the procedure will raise an error. This is a setting in the field properties. There should be error handling in the code. An update query would be faster.

 

[LouieLyle]

There should also be an assignment at the end
emptyfield=true
otherwise the function will return false even when it is successful.