Checkbox event to control other checkbox selections

[Ruth]

I have been working for a couple of hours now trying different ways to
solve this problem and I'm out of ideas (or tired). :-/

Here's what I'm trying to do:
On a form I have a section with six checkboxes that represent 6
different mailing lists an organization can subscribe to: MailList01,
MailList02, etc.

I have a seventh checkbox named MailListOmit for orgs that want to opt
out of mailings.

If MailListOmit is checked, when the user attempts to click any one of
the MailList checkboxes, I want to return a dialog box that says,
"This organization has chosen to not receive mailings," and reset the
checkbox to False.

If a user attempts to click MailListOmit and any of the MailList
checkboxes are checked, I want to return a dialog box (with the
options Yes, No or Cancel that says, "Checking this box will remove
all current mail list selections." If yes, it will set MailListOmit to
True and clear the MailList check box values. If no or cancel,
MailListOmit will remain False and MailList checkboxes that are
currently checked will remain.

If no MailList checkboxes are checked, the user would be able to check
the MailListOmit checkbox without a prompt.

Here's the code I've written for the first part:
Private Sub MailList01_BeforeUpdate(Cancel As Integer)
With Me
If (Me.MailListOmit = True) Then
MsgBox "This organization has chosen to not receive
mailings.", 0, "No Mailings!"
Me.MailListOmit.SetFocus
Me.MailList01.Enabled = False

Else
Me.MailList01.Enabled = True
End If
End With
End Sub

I get this error:
Run-time error '2108':

You must save the field before you execute the GoToControl action, the
GoToControl method. or the SetFocus method.

Obviously I'm a coding novice... but I'm trying! Suggestions? TIA!

 

[Ruth]

PS... I know that my code isn't resetting the value to false, but
disabling the control. I've tried so many things.... I pasted the
last version of what I tried.

 

[Ruth]

OK... I've got the first part fixed. Changed the code as follows and
moved it to the After Update event.

Private Sub MailList01_AfterUpdate()
With Me
If (Me.MailListOmit = True) Then
MsgBox "This organization has chosen to not receive
mailings.", 0, "No Mailings!"
Me.MailList01 = False

Else
Me.MailList01 = True
End If
End With

End Sub

Now, any suggestions for the second part? Writing the multiple
evaluation part is what I don't know how to do.

Thanks!

 

[Allen Browne]

Ruth, to set the value of the check box instead of its Enabled property,
replace:
Me.MailList01.Enabled = False
with just:
Me.MailList01 = False

There is a better way to approach this issue. One client (person? company?)
can be on multiple mailing lists. Therefore it would be better to create a
related table to hold the mailing lists for the client. Instead of many
yes/no fields in one table, you then have many records in the other table (a
one-to-many relation.)

Client table (one record for each person/company), with fields:
ClientID AutoNumber primary key
ClientName
DoNotMail Yes/No

MailList table (one record for each mailing list you create):
MailListID AutoNumber primary key
MailList Text name of this mailing list.

ClientMaillist table, with fields:
ClientID Number relates to Client.ClientID
MailListID Number relates to MailList.MailListID
So, if client 7 want to be in 3 mailing lists, they have 3 *records* in this
table.

To interface this, you create a main form bound to the Client table, with a
subform bound to the ClientMaillist table. The subform is in continuous
view, so you can add as many rows as the client needs for the mailing lists
they want to be on. The subform will have a combo box that uses the MailList
table as its RowSource.

This will simplify your life no end. The day someone decides to create a new
mailing list, you don't have to modify your tables, queries, forms and
reports: you just add another record to the MailList table.

Finally, I've suggested that you can suppress all mailing lists with a field
in the Client table, since that's an attribute of the client. When you
actually export people for a mailing, you use a query that selects only the
clients where the DoNotMail box is False. This has the added benefit of
being able to temporarily suppress mailings to a client for a while (e.g. if
they are away for a few months), without losing track of which lists they
actually want to be on.

HTH