Change field on all records in dataset

[ST4RCUTTER]

Hi all! Looking for some code help here. What I'm trying to do is change a value on all records once they are returned in a query. I have a yes/no field that I want to set to "yes" once the query returns the initial recordset. By changing this field to "yes" on all the records the idea is that no other query - which excludes a "yes" value in this field - will return results.

I think I will need to use the OnLoad event to run this code. The code must loop through every record in the query results and change the field "Field1" to "yes" from a default value of "no". For the sake of example, lets call this query "qryResults".



Isn't there code that tells Access to "run to the end of the file" or EOF? Thanks!

 

[MSAccessRookie]

Hi all! Looking for some code help here. What I'm trying to do is change a value on all records once they are returned in a query. I have a yes/no field that I want to set to "yes" once the query returns the initial recordset. By changing this field to "yes" on all the records the idea is that no other query - which excludes a "yes" value in this field - will return results.

I think I will need to use the OnLoad event to run this code. The code must loop through every record in the query results and change the field "Field1" to "yes" from a default value of "no". For the sake of example, lets call this query "qryResults".



Isn't there code that tells Access to "run to the end of the file" or EOF? Thanks!

This seems to be an awful lot of work for each time a record needs to be updated. is there a reason you want this? Perhaps there is anotehr option that would be easier on Access.

 

[pbaldy]

Why would you need code? Changing the value in the query should change it in the underlying data, so no other query would return those records.

 

[MSAccessRookie]

Why would you need code? Changing the value in the query should change it in the underlying data, so no other query would return those records.

I suspect he may not have a split database, or is sharing a single network FE, and I wanted to eliminate those two possibilities first

 

[ST4RCUTTER]

Nevermind..figured out. Here is the code in case anyone else might find this useful:

Dim rs As Recordset

Set rs = Me.Recordset

rs.MoveFirst
Do Until rs.EOF 'loop through our query created above
        If [fldIsLocked].Value <> "Yes" Then
        [fldIsLocked].Value = Yes
        End If
    rs.MoveNext
Loop
Set rs = Nothing

 

[firas_25]

Many Thanx