Changing Value in Field

[Anne]

I want to reset true values back to false in a query (all
the values in the query are initially "true").

Why isn't this working:

Public Function ResetToZero(strRecSource As String,
strFieldName As String) As String
On Error Resume Next
Dim db As DAO.Database
Dim rst As DAO.Recordset

' Open the recordset and make sure it has records
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset(strRecSource)
If rst.EOF And rst.BOF = True Then
Exit Function
End If

' loop trough the values on the field and set to
zero
While Not rst.EOF
rst(strFieldName).Value = 0
rst.MoveNext
Wend
Set rst = Nothing
Set db = Nothing

End Function

arrg. Please help. I want to like life again.

 

[Allen Browne]

To find out why it is not working, remove the line:
On Error Resume Next
so Access will tell you where the problem is.

Here is an alternative approach:

dbEngine(0)(0).Execute "UPDATE " & strRecSource & " SET " & strFieldName &
" = False;", dbFailOnError

 

[Marshall Barton]

I want to reset true values back to false in a query (all
the values in the query are initially "true").

Why isn't this working:

Public Function ResetToZero(strRecSource As String,
strFieldName As String) As String
On Error Resume Next
Dim db As DAO.Database
Dim rst As DAO.Recordset

' Open the recordset and make sure it has records
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset(strRecSource)
If rst.EOF And rst.BOF = True Then
Exit Function
End If

' loop trough the values on the field and set to
zero
While Not rst.EOF
rst(strFieldName).Value = 0
rst.MoveNext
Wend
Set rst = Nothing
Set db = Nothing

End Function

You have to place the recordset into edit mode before you
can change the values:

' loop trough the values on the field and set to zero
Do Until rst.EOF
rst.Edit
rst(strFieldName).Value = 0
rst.Update
rst.MoveNext
Loop
rst.Close : Set rst = Nothing
Set db = Nothing

I don't know what the data source for this recordset is,
but, if possible, you should use an Update query instead of
looping through the records in code:

DbEngine(0)(0).Execute "UPDATE table SET fieldname = 0"