Delete Data in a field in all records 2

[jbento]

All,
I have a Yes/No field called answerYes, which is in a table called tbl_Data.

I have a switchboard with a command button that I would like to reference the tbl_Data and if the value is True for answerYes in all records, I would like to make the value False in every record.

Is there an easy way to do this?

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[dverdi]

There is probably an easier, or at least more elegant way to do this, but my first thought is:

Dim nFalse as Integer
Dim strSql as String
Dim myDB As DAO.Database
Dim myRST As DAO.Recordset

Set myDB = whatever
strSql = "SELECT yes/no field FROM table"
Set myRST = myDB.OpenRecordset(strSql, dbOpenDynaset)
nFalse = 0
myRST.MoveFirst
While Not myRST.EOF
If myRST!yes/no field = FALSE Then
nFalse = nFalse + 1
End If
Wend

If nFalse <> 0 Then
myRST.MoveFirst
While Not myRST.EOF
myRST!yes/no field = FALSE
Wend
End If

MyRST.Close
MyDB.Close

 

[Edski]

dverdi,
I think you forgot the MyRST.MoveNext statement for both loops.
Also, in the second loop, you need to issue MyRST.Edit{/B] and then MyRST.Update before any updates can occur to the recordset.
In addition, your second loop conditional expression should be If nFalse = 0 Then.
Apart from that, the code should work

Alternatively, you could add a conditional expression in the query string: strSql = "SELECT yesNo FROM Table1 WHERE yesNo = FALSE" and then check if any records are returned (myRST.EOF will be true). If so then execute an update query that sets the field to FALSE for every record.
Cheers.

 

[Edski]

Here's a modified code of dverdi's:

Dim strSql As String
Dim myDB As DAO.Database, myRST As DAO.Recordset
Set myDB = CurrentDb

strSql = "SELECT YesNo FROM Table1 WHERE YesNo = FALSE"
Set myRST = myDB.OpenRecordset(strSql, dbOpenDynaset)

If myRST.EOF Then
    strSql = "UPDATE Table1 SET Table1.[YesNo] = False;"
    myDB.Execute strSql
End If

myRST.Close
myDB.Close

 

[jbento]

This worked like a charm:

Dim strSql As String
Dim myDB As DAO.Database, myRST As DAO.Recordset
Set myDB = CurrentDb

strSql = "SELECT YesNo FROM Table1 WHERE YesNo = FALSE"
Set myRST = myDB.OpenRecordset(strSql, dbOpenDynaset)

If myRST.EOF Then
strSql = "UPDATE Table1 SET Table1.[YesNo] = False;"
myDB.Execute strSql
End If

myRST.Close
myDB.Close

Thanks so much to BOTH of you and have a great evening.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!