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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ruth" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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!
Similar Threads
