Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

Form Basics

Check only one checkbox in a subform by enuf4u
Posted: 24 May 04

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!

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close