Only one record yes in a yes no field

[Design by Sue]

My table has a yes/no field and about 10 records. I have created a form for the user to select which of the records he wishes to use - the records are displayed in a continuous form with a check box for the yes/no field. The user is to select one record by checking the check box in the record.

I know this should be obvious, but I can't see it. How can I make it so the user can only select one of the records (when he clicks on one, that one is yes and the others revert to no?

Thanks
Sue

 

[plog]

No this isn't obvious, or easy. Records are independent pieces of data that you can't really tie together via a simple command. You are going to need some sort of VBA to achieve this.

My method would be to attach an OnClick event to the checkbox. If the checkbox is changing to True, you run an UPDATE query that sets all the Yes/No fields to No, and after that runs, you set the selected record to True.

 

[Design by Sue]

Figured it would have to be coded, but did not want to write the code if there was a simpler way. Thanks for your direction. Think I can make that work.

Sue

 

[CJ_London]

you need to run some code in the control afterupdate event that will update all the records checkboxes to false except the current one

It will be something like

currentdb.execute("UPDATE myTables set chk = false where ID<>" & me.ID)

 

[Design by Sue]

CJ - Thanks - that cut the code from 2 lines to one! works perfectly

Sue