Use Checkbox to Set Record as Default

[joecamel9166]

So I have multiple shipping addresses for each customer. I can select the customer, then I have a query defining the ship to combo box where I can select the shipping addresses for that customer.
I added a checkbox to my tblCustShip and to my subCustShip.

Right now when I select the customer(cmbCust), its subform(subCustomer) loads, then I have to go to cmbCustShip and when I select the address, its subform loads.



What I want to do, if possible, is use a checkbox on subCustShip to assign that record as the default. So when I select the customer, not only does that load subCustomer, but subCustShip loads as well to the default record.
Also on top of that, I would like to make that checkbox only valid on one record. so if record3 has the checkbox set to true, then I set the checkbox for record5 to true, record3 checkbox is automatically set to false.

[ranman256]

put the check box in the sub form,
connect the checkbox to the table field.DEFAULT

then when the sub form loads, just mark the one you want to be default.
BUT you have to unmark any previous checks. I cant think of a way to prevent multiple checks=yes.

[joecamel9166]

Thank you! I got it to work, but it makes more work having to deselect the check boxes. If you (or anyone else) can think of a way to do this, please let me know. Otherwise I will just have to leave it as it is.

[John_G]

You could use the After Update Event of the checkbox to clear all the other checkboxes for that customer if the current one was selected. This means you will need a field or fields to uniquely identify the current address record (could just be an autonumber). The code in the After Update event might look something like this:

if Checkbox = true then
  SQL = "Update tblAddress set chkDefault = False where Customer_ID = " & me!Cust_ID & " AND Address_ID <> " & me!AddressID
  currentdb.execute SQL, dbFailOnError
  me.requery   
endif

Replace all the names I use with the actual names in your table and form.

[ranman256]

yeah, that.

[joecamel9166]

Thanks guys. As always, your help is much appreciated!!!