Check only one checkbox in a subform

I had this problem when I was creating an inventory tracking database. I had a main form showing each part that I stocked. I had multiple suppliers for each part. I had a sub form on the main form that showed all possible suppliers for that specific part. On the sub form, I used a checkbox to denote the default supplier. This is how I queried who to order the part from. The problem I had was that the user could check more than one supplier default, thus causing me to order the same part from both checked suppliers. I want the user to only be able to check one checkbox on the sub form. I searched hi and low, and could not find a thread. This is the way I finally got around the problem:


Step 1

Create a query that shows the data in the sub form. Change it to an update query and update the checkbox(s) to false or no. Save the query. For this instance, my query was named "DefaultSupplierFalse" and my checkbox was named "Default".

Step 2

On the before update event of the checkbox in question on the sub form, place the following code:

Private Sub Default_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "DefaultSupplierFalse", acViewNormal
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.SetWarnings True
End Sub

I am an amateur, but this is what I see happening:

Before the checkbox is updated, the code runs the query, which sets all checkboxes in the sub form to false. Then the newly checked checkbox is updated!

Please note that "DoCmd.SetWarnings False" simply turns the pop up message "You are about to run an update query that will modify data in your table" off. "DoCmd.SetWarnings True" simply turns the warnings back on!

I hope this will save someone some time!