Checkbox on one form updates multiple checkboxes on another form

[Amy Adams via AccessMonster.com]

I have a checkbox on one form that if checked to equal True/false would
possibly update to another form that has multiple checkboxes that would now
also equal true/false. Can this be done using code, instead of using an
update query?

Using an example to help clarify:

Form1: product: InsuranceCompany. InsuranceCompany disolves.
InsuranceCompanyInactiveCheckbox equals true.

Form2: Customers: Mary, John, Frank, Don. Customers who used this
Insuance Company checkboxes also now equal true (b/c Insurance Company has
dissolved).

Thanks so much

 

[JP]

It can certainly be done using code.

In the AfterUpdate event of the checkbox (I'm assuming that it's a
standalone checkbox and not part of a frame), put some code to create a sql
statement that updates the appropriate records in the table that contains
the customers and then do a DOCMD.RUNSQL using that sql statement. For
example, assuming that the following:

1. the checkbox you want to update in the Customers table is called
"inactive_checkbox"
2. the Insurance Company ID field in the Customers table is called
"customer_insurance_company"
3. the Insurance Company ID field in the Product table is called
"product_insurance_company"

then it might look something like:

dim sSQL as string
sSQL = "update customers set inactive_checkbox = -1 where
customer_insurance_company = me.product_insurance_company"
DoCmd.RunSQL (sSQL)

A couple of caveats:

1. If product_insurance_company is a string, you have to put single quotes
around the contents of me.product_insurance_company
2. You might want to put a DoCmd.SetWarnings False and a DoCmd.SetWarnings
True around the DoCmd.RunSQL in order to prevent the user from seeing the
warning messages about "You are about to update xxx row", etc. when the sql
statement is run.

Having said all that, there is also an argument to be made that third normal
form normalization would argue against your propogating the information from
the product table to the customer table. Instead, you would just have the
InsuranceCompanyInactiveCheckbox in the product table and use that in any
reporting or on-screen displays to indicate that these customers are
associated with a dissolved company (through joined tables). I think that
might be the better approach in the long-run since -- sooner or later --
propogating data from one table to another will result in data that is out
of synch between the two tables.

I'd appreciate feedback from some of the MVPs on this issue of whether or
not to propogate the data from one table to another.