Yes/No - Update 1 record to YES and change all other records to NO

[StlthBM]

What is the easiest way to update all records in a table so that when one is selected to YES, all other records are updated to NO?

The table is designed to keep track of statements, with only one statement being the "Endorsed" statement but the other iterations being kept track in the table. At the moment I've built the table and I'm in the process of building the form.

 

[theDBguy]

You should be able to use an UPDATE query to do that. For example,

UPDATE TableName SET FieldName = False WHERE ID <> 1

1 being the ID of the approved record.

 

[The_Doc_Man]

First, decide what is the identifying criterion for the record to become YES. For sake of argument, let's say you know the prime key for the record. If the table is MyTable, the PK field is MyID, and the value in question is in your form's ChosenID control, and the Yes/No indicator is MyYN:

strSQL = "UPDATE MyTable SET MyYN = ( MyID = " & Cstr( Me.ChosenID ) & " );"
CurrentDB.Execute strSQL, dbFailOnError

This will set every record's MyYN to the truth-value of "MyID = " & CStr( Me.ChosenID ); only one record will match and be YES, others will be NO

 

[arnelgp]

you need 2 update query (in case there is previous Yes in a record).

update yourTable set YesNoField = 0
update yourTable set YesNoField =-1 Where [ID] = 99

 

[The_Doc_Man]

Arnel, my method is one-pass, not two-pass.

 

[CJ_London]

you can do it in one query

update yourTable set YesNoField =[ID}=1

where 1 is the ID of the record selected to be yes

edit: Doc got there before me

 

[arnelgp]

Arnel, my method is one-pass, not two-pass

Any attribution?

 

[StlthBM]

Are you guys talking in SQL?
I can barely spell SQL correctly. My VBA and macro knowledge is about the same

 

[plog]

The table is designed to keep track of statements, with only one statement being the "Endorsed" statement but the other iterations being kept track in the table.

What does "the other iterationss being kept track in the table" mean? What are you tracking?

If only 1 statement is to be endorsed, you simply just store it and not the others. I fear you haven't structured your table correctly.

 

[MajP]

So likely you want to do this in the after update event in a form.
In the after update of the Endorsed field

What is the name of the Endorsed field? (is it "Endorsed")
What is the name of the Primary key and is it numeric or text?

it is probably some code that uses that above sql. Something like this in the afterupdate

dim PK as long
dim strSql as string
If Me.Endorsed then
    PK = me.NameOfPKField
    strSql = "Update tablName SET Endorsed = False"
    currentDB.execute strSql
    strSql = "Update tablename SET Endorsed = true WHERE NameOfPKfield = " & PK
   currentdb.execute strSql
end if