If statement logic

[Guest]

I have 5 check boxes on my form and what I want to do is write an "IF"
statement that checks these boxes to see if they are checked and if they are
then trigger my email function. I already have the email function created
except for the email addresses. Since there can be many different scenarios
with these boxes I need to know how to tell the email function which emails
to use. For example say 3 of them are checked then how do I tell me email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each one
represent a select from table(s), such as "local clients", "international
suppliers", etc?

Either way, the same principle applies, but the second one is obviously more
complex.

Just write some code to text the state of each checkbox and append the email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you can
pass to your email function.

 

[Guest]

Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are true
then how does it pull only those email addresses for the email?

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each one
represent a select from table(s), such as "local clients", "international
suppliers", etc?

Either way, the same principle applies, but the second one is obviously more
complex.

Just write some code to text the state of each checkbox and append the email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 5 check boxes on my form and what I want to do is write an "IF"
statement that checks these boxes to see if they are checked and if they
are
then trigger my email function. I already have the email function created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your code, is
that if the email addresses need to be changed you must open your form in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they could be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are true
then how does it pull only those email addresses for the email?

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each one
represent a select from table(s), such as "local clients", "international
suppliers", etc?

Either way, the same principle applies, but the second one is obviously
more
complex.

Just write some code to text the state of each checkbox and append the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 5 check boxes on my form and what I want to do is write an "IF"
statement that checks these boxes to see if they are checked and if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me
email
function to use the email addresses for those 3 check boxes?

 

[Guest]

I understand it better now but where do I put these email addresses? I didn't
notice where in the code you wrote. Also are these check boxes bound to my
table or unbound? I would assume they would be bound since they can be
different for other records. If that's the case then do I add this code in
the currentevent of the form?

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your code, is
that if the email addresses need to be changed you must open your form in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they could be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are true
then how does it pull only those email addresses for the email?

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each one
represent a select from table(s), such as "local clients", "international
suppliers", etc?

Either way, the same principle applies, but the second one is obviously
more
complex.

Just write some code to text the state of each checkbox and append the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message I have 5 check boxes on my form and what I want to do is write an "IF"
statement that checks these boxes to see if they are checked and if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me
email
function to use the email addresses for those 3 check boxes?

 

[Guest]

Oh and how do I carry that string of email addresses to the ".To" line of my
email function?

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your code, is
that if the email addresses need to be changed you must open your form in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they could be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are true
then how does it pull only those email addresses for the email?

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each one
represent a select from table(s), such as "local clients", "international
suppliers", etc?

Either way, the same principle applies, but the second one is obviously
more
complex.

Just write some code to text the state of each checkbox and append the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message I have 5 check boxes on my form and what I want to do is write an "IF"
statement that checks these boxes to see if they are checked and if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me
email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

Hmmmm... I didn't realise these checkboxes were bound. I somehow assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I understand it better now but where do I put these email addresses? I
didn't
notice where in the code you wrote. Also are these check boxes bound to my
table or unbound? I would assume they would be bound since they can be
different for other records. If that's the case then do I add this code in
the currentevent of the form?

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your code,
is
that if the email addresses need to be changed you must open your form in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and append the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message I have 5 check boxes on my form and what I want to do is write an
"IF"
statement that checks these boxes to see if they are checked and if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me
email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

Yes - that string should work perfectly in the To line of an email.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oh and how do I carry that string of email addresses to the ".To" line of
my
email function?

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your code,
is
that if the email addresses need to be changed you must open your form in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and append the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message I have 5 check boxes on my form and what I want to do is write an
"IF"
statement that checks these boxes to see if they are checked and if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me
email
function to use the email addresses for those 3 check boxes?

 

[Guest]

Well here's exactly what I'm trying to do. I want to put 5 checkboxes on my
form that will trigger an email address in 5 different textboxes. So when a
user clicks on one of the check boxes then the email address that is tied to
that check box appears in the corresponding textbox. Once the user checks all
the boxes he/she needs then they can click a command button to generate the
email to those email addresses that are shown in the text boxes. I haven't
added any of these fields yet until I know exactly how to set it up. The form
I use is a single view so I would need the users to be able to choose
different checkboxes depending on the record they are updating. I think I
understand the code you wrote but the bounb/unbound part is confusing me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null

Hmmmm... I didn't realise these checkboxes were bound. I somehow assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I understand it better now but where do I put these email addresses? I
didn't
notice where in the code you wrote. Also are these check boxes bound to my
table or unbound? I would assume they would be bound since they can be
different for other records. If that's the case then do I add this code in
the currentevent of the form?

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your code,
is
that if the email addresses need to be changed you must open your form in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure I
follow you with your code though. If it finds 3 check boxes that are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and append the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message I have 5 check boxes on my form and what I want to do is write an
"IF"
statement that checks these boxes to see if they are checked and if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function which
emails
to use. For example say 3 of them are checked then how do I tell me
email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

Do the checkboxes have anything in their ControlSource properties? This is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there either by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is bound to
that field - if the field changes the value in the control changes, and
vice-versa.

If the checkboxes all correspond to fields in your source table then they
are bound, and will change as you move from one record to the next. This is
what you implied in your penultimate post, but not in your last post, so I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table somewhere?

What else is on your form? Is there anything in the content of the current
record that preordains the state of the checkboxes (or the corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Well here's exactly what I'm trying to do. I want to put 5 checkboxes on
my
form that will trigger an email address in 5 different textboxes. So when
a
user clicks on one of the check boxes then the email address that is tied
to
that check box appears in the corresponding textbox. Once the user checks
all
the boxes he/she needs then they can click a command button to generate
the
email to those email addresses that are shown in the text boxes. I haven't
added any of these fields yet until I know exactly how to set it up. The
form
I use is a single view so I would need the users to be able to choose
different checkboxes depending on the record they are updating. I think I
understand the code you wrote but the bounb/unbound part is confusing me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null

Hmmmm... I didn't realise these checkboxes were bound. I somehow assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I understand it better now but where do I put these email addresses? I
didn't
notice where in the code you wrote. Also are these check boxes bound to
my
table or unbound? I would assume they would be bound since they can be
different for other records. If that's the case then do I add this code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your
code,
is
that if the email addresses need to be changed you must open your form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure
I
follow you with your code though. If it finds 3 check boxes that are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does
each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and append
the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that
you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message I have 5 check boxes on my form and what I want to do is write an
"IF"
statement that checks these boxes to see if they are checked and
if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function
which
emails
to use. For example say 3 of them are checked then how do I tell
me
email
function to use the email addresses for those 3 check boxes?

 

[Guest]

Hi Graham,

After several test attempts I finally have it working. I had to tinker with
it a bit but it doing exactly what you said it would. Thank you very much! I
do have one follow up question...First I do agree that maybe putting these
into a table instead of hard coding it is a better approach. How would I set
up the DLookUp for this scenerio?

Do the checkboxes have anything in their ControlSource properties? This is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there either by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is bound to
that field - if the field changes the value in the control changes, and
vice-versa.

If the checkboxes all correspond to fields in your source table then they
are bound, and will change as you move from one record to the next. This is
what you implied in your penultimate post, but not in your last post, so I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table somewhere?

What else is on your form? Is there anything in the content of the current
record that preordains the state of the checkboxes (or the corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Well here's exactly what I'm trying to do. I want to put 5 checkboxes on
my
form that will trigger an email address in 5 different textboxes. So when
a
user clicks on one of the check boxes then the email address that is tied
to
that check box appears in the corresponding textbox. Once the user checks
all
the boxes he/she needs then they can click a command button to generate
the
email to those email addresses that are shown in the text boxes. I haven't
added any of these fields yet until I know exactly how to set it up. The
form
I use is a single view so I would need the users to be able to choose
different checkboxes depending on the record they are updating. I think I
understand the code you wrote but the bounb/unbound part is confusing me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null

Hmmmm... I didn't realise these checkboxes were bound. I somehow assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

message I understand it better now but where do I put these email addresses? I
didn't
notice where in the code you wrote. Also are these check boxes bound to
my
table or unbound? I would assume they would be bound since they can be
different for other records. If that's the case then do I add this code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box is
checked.

My example code is building up a list of email addresses in a string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your
code,
is
that if the email addresses need to be changed you must open your form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Hi Graham,

Yes each checkbox is associated with one email address. I'm not sure
I
follow you with your code though. If it finds 3 check boxes that are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does
each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and append
the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons, that
you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message I have 5 check boxes on my form and what I want to do is write an
"IF"
statement that checks these boxes to see if they are checked and
if
they
are
then trigger my email function. I already have the email function
created
except for the email addresses. Since there can be many different
scenarios
with these boxes I need to know how to tell the email function
which
emails
to use. For example say 3 of them are checked then how do I tell
me
email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless you give
some more details about your structure and business rules and answer my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is its
recordsource?

Is there anything in the content of the current record that preordains the
state of the checkboxes (or the corresponding email addresses) for each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

After several test attempts I finally have it working. I had to tinker
with
it a bit but it doing exactly what you said it would. Thank you very much!
I
do have one follow up question...First I do agree that maybe putting these
into a table instead of hard coding it is a better approach. How would I
set
up the DLookUp for this scenerio?

Do the checkboxes have anything in their ControlSource properties? This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there either by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is bound to
that field - if the field changes the value in the control changes, and
vice-versa.

If the checkboxes all correspond to fields in your source table then they
are bound, and will change as you move from one record to the next. This
is
what you implied in your penultimate post, but not in your last post, so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table somewhere?

What else is on your form? Is there anything in the content of the
current
record that preordains the state of the checkboxes (or the corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Well here's exactly what I'm trying to do. I want to put 5 checkboxes
on
my
form that will trigger an email address in 5 different textboxes. So
when
a
user clicks on one of the check boxes then the email address that is
tied
to
that check box appears in the corresponding textbox. Once the user
checks
all
the boxes he/she needs then they can click a command button to generate
the
email to those email addresses that are shown in the text boxes. I
haven't
added any of these fields yet until I know exactly how to set it up.
The
form
I use is a single view so I would need the users to be able to choose
different checkboxes depending on the record they are updating. I think
I
understand the code you wrote but the bounb/unbound part is confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

message I understand it better now but where do I put these email addresses?
I
didn't
notice where in the code you wrote. Also are these check boxes bound
to
my
table or unbound? I would assume they would be bound since they can
be
different for other records. If that's the case then do I add this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box
is
checked.

My example code is building up a list of email addresses in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email
function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to
store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your
code,
is
that if the email addresses need to be changed you must open your
form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message Hi Graham,

Yes each checkbox is associated with one email address. I'm not
sure
I
follow you with your code though. If it finds 3 check boxes that
are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does
each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and
append
the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons,
that
you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
I have 5 check boxes on my form and what I want to do is write
an
"IF"
statement that checks these boxes to see if they are checked
and
if
they
are
then trigger my email function. I already have the email
function
created
except for the email addresses. Since there can be many
different
scenarios
with these boxes I need to know how to tell the email function
which
emails
to use. For example say 3 of them are checked then how do I
tell
me
email
function to use the email addresses for those 3 check boxes?

 

[Guest]

Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter corrective
actions. The check boxes are used to send a notification email to the
managers to let them know the corrective action is complete and ready for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that preordains the
state of the checkboxes. It's just used as a trigger to let the email
function know which managers to email. What I want to do now is set up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup each email
address and send it to the ones that are checked using the checkboxes. Each
check box has been defined as "CEOCheck", "SalesCheck", "EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup could
look for the title that matches the check box and then use the email address
in that record. I hope what i wrote makes sense. Let me know if you need more
information.

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless you give
some more details about your structure and business rules and answer my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is its
recordsource?

Is there anything in the content of the current record that preordains the
state of the checkboxes (or the corresponding email addresses) for each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

After several test attempts I finally have it working. I had to tinker
with
it a bit but it doing exactly what you said it would. Thank you very much!
I
do have one follow up question...First I do agree that maybe putting these
into a table instead of hard coding it is a better approach. How would I
set
up the DLookUp for this scenerio?

Do the checkboxes have anything in their ControlSource properties? This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there either by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is bound to
that field - if the field changes the value in the control changes, and
vice-versa.

If the checkboxes all correspond to fields in your source table then they
are bound, and will change as you move from one record to the next. This
is
what you implied in your penultimate post, but not in your last post, so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table somewhere?

What else is on your form? Is there anything in the content of the
current
record that preordains the state of the checkboxes (or the corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




message Well here's exactly what I'm trying to do. I want to put 5 checkboxes
on
my
form that will trigger an email address in 5 different textboxes. So
when
a
user clicks on one of the check boxes then the email address that is
tied
to
that check box appears in the corresponding textbox. Once the user
checks
all
the boxes he/she needs then they can click a command button to generate
the
email to those email addresses that are shown in the text boxes. I
haven't
added any of these fields yet until I know exactly how to set it up.
The
form
I use is a single view so I would need the users to be able to choose
different checkboxes depending on the record they are updating. I think
I
understand the code you wrote but the bounb/unbound part is confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

message I understand it better now but where do I put these email addresses?
I
didn't
notice where in the code you wrote. Also are these check boxes bound
to
my
table or unbound? I would assume they would be bound since they can
be
different for other records. If that's the case then do I add this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the box
is
checked.

My example code is building up a list of email addresses in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email
function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to
store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in your
code,
is
that if the email addresses need to be changed you must open your
form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message Hi Graham,

Yes each checkbox is associated with one email address. I'm not
sure
I
follow you with your code though. If it finds 3 check boxes that
are
true
then how does it pull only those email addresses for the email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or does
each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and
append
the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" & ";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" & ";"
End If

You will then have a list of emails, separated by semicolons,
that
you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
I have 5 check boxes on my form and what I want to do is write
an
"IF"
statement that checks these boxes to see if they are checked
and
if
they
are
then trigger my email function. I already have the email
function
created
except for the email addresses. Since there can be many
different
scenarios
with these boxes I need to know how to tell the email function
which
emails
to use. For example say 3 of them are checked then how do I
tell
me
email
function to use the email addresses for those 3 check boxes?

 

[Graham Mandeno]

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3 etc and the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for CEOCheck is 1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress", "tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter corrective
actions. The check boxes are used to send a notification email to the
managers to let them know the corrective action is complete and ready for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that preordains
the
state of the checkboxes. It's just used as a trigger to let the email
function know which managers to email. What I want to do now is set up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup each
email
address and send it to the ones that are checked using the checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup could
look for the title that matches the check box and then use the email
address
in that record. I hope what i wrote makes sense. Let me know if you need
more
information.

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless you
give
some more details about your structure and business rules and answer my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is its
recordsource?

Is there anything in the content of the current record that preordains
the
state of the checkboxes (or the corresponding email addresses) for each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

After several test attempts I finally have it working. I had to tinker
with
it a bit but it doing exactly what you said it would. Thank you very
much!
I
do have one follow up question...First I do agree that maybe putting
these
into a table instead of hard coding it is a better approach. How would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is bound
to
that field - if the field changes the value in the control changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source table then
they
are bound, and will change as you move from one record to the next.
This
is
what you implied in your penultimate post, but not in your last post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content of the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




message Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different textboxes. So
when
a
user clicks on one of the check boxes then the email address that is
tied
to
that check box appears in the corresponding textbox. Once the user
checks
all
the boxes he/she needs then they can click a command button to
generate
the
email to those email addresses that are shown in the text boxes. I
haven't
added any of these fields yet until I know exactly how to set it up.
The
form
I use is a single view so I would need the users to be able to
choose
different checkboxes depending on the record they are updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all
the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message I understand it better now but where do I put these email
addresses?
I
didn't
notice where in the code you wrote. Also are these check boxes
bound
to
my
table or unbound? I would assume they would be bound since they
can
be
different for other records. If that's the case then do I add
this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the
box
is
checked.

My example code is building up a list of email addresses in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email
function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to
store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in
your
code,
is
that if the email addresses need to be changed you must open
your
form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so
they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
Hi Graham,

Yes each checkbox is associated with one email address. I'm
not
sure
I
follow you with your code though. If it finds 3 check boxes
that
are
true
then how does it pull only those email addresses for the
email?

:

Hi Secret Squirrel

Is each checkbox associated with a single email address, or
does
each
one
represent a select from table(s), such as "local clients",
"international
suppliers", etc?

Either way, the same principle applies, but the second one is
obviously
more
complex.

Just write some code to text the state of each checkbox and
append
the
email
address(es) to a string:

Dim sEmails as String
If CheckBox1 <> 0 then
sEmails = sEmails & "first email (or list of emails)" &
";"
End If
If CheckBox2 <> 0 then
sEmails = sEmails & "second email (or list of emails)" &
";"
End If

You will then have a list of emails, separated by semicolons,
that
you
can
pass to your email function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
I have 5 check boxes on my form and what I want to do is
write
an
"IF"
statement that checks these boxes to see if they are
checked
and
if
they
are
then trigger my email function. I already have the email
function
created
except for the email addresses. Since there can be many
different
scenarios
with these boxes I need to know how to tell the email
function
which
emails
to use. For example say 3 of them are checked then how do I
tell
me
email
function to use the email addresses for those 3 check
boxes?

 

[Guest]

Well wouldn't these checkboxes and textboxes have to be bound to save the
state they are in for that particular record? Also, if I'm going to use the
DLookup then I guess I don't even need the textboxes. I could just have the
checkboxes point to the table where the email addresses are stored.

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3 etc and the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for CEOCheck is 1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress", "tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter corrective
actions. The check boxes are used to send a notification email to the
managers to let them know the corrective action is complete and ready for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that preordains
the
state of the checkboxes. It's just used as a trigger to let the email
function know which managers to email. What I want to do now is set up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup each
email
address and send it to the ones that are checked using the checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup could
look for the title that matches the check box and then use the email
address
in that record. I hope what i wrote makes sense. Let me know if you need
more
information.

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless you
give
some more details about your structure and business rules and answer my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is its
recordsource?

Is there anything in the content of the current record that preordains
the
state of the checkboxes (or the corresponding email addresses) for each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand


message Hi Graham,

After several test attempts I finally have it working. I had to tinker
with
it a bit but it doing exactly what you said it would. Thank you very
much!
I
do have one follow up question...First I do agree that maybe putting
these
into a table instead of hard coding it is a better approach. How would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is bound
to
that field - if the field changes the value in the control changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source table then
they
are bound, and will change as you move from one record to the next.
This
is
what you implied in your penultimate post, but not in your last post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content of the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




message Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different textboxes. So
when
a
user clicks on one of the check boxes then the email address that is
tied
to
that check box appears in the corresponding textbox. Once the user
checks
all
the boxes he/she needs then they can click a command button to
generate
the
email to those email addresses that are shown in the text boxes. I
haven't
added any of these fields yet until I know exactly how to set it up.
The
form
I use is a single view so I would need the users to be able to
choose
different checkboxes depending on the record they are updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and all
the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message I understand it better now but where do I put these email
addresses?
I
didn't
notice where in the code you wrote. Also are these check boxes
bound
to
my
table or unbound? I would assume they would be bound since they
can
be
different for other records. If that's the case then do I add
this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only the
box
is
checked.

My example code is building up a list of email addresses in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email
function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes to
store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in
your
code,
is
that if the email addresses need to be changed you must open
your
form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so
they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
Hi Graham,

Yes each checkbox is associated with one email address. I'm
not
sure
I
follow you with your code though. If it finds 3 check boxes
that
are
true

 

[Graham Mandeno]

I thought we'd established that they were unbound and that there were not
any fields in your record to store their state :-S

And no, you don't need the textboxes, but I thought you wanted them so the
user could see the email addresses.

So, what fields DO you have in your recordset that are related to this
emailing operation?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Well wouldn't these checkboxes and textboxes have to be bound to save the
state they are in for that particular record? Also, if I'm going to use
the
DLookup then I guess I don't even need the textboxes. I could just have
the
checkboxes point to the table where the email addresses are stored.

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3 etc and
the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for CEOCheck is
1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the
corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress", "tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter
corrective
actions. The check boxes are used to send a notification email to the
managers to let them know the corrective action is complete and ready
for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that
preordains
the
state of the checkboxes. It's just used as a trigger to let the email
function know which managers to email. What I want to do now is set up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup each
email
address and send it to the ones that are checked using the checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup
could
look for the title that matches the check box and then use the email
address
in that record. I hope what i wrote makes sense. Let me know if you
need
more
information.

:

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless you
give
some more details about your structure and business rules and answer
my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is its
recordsource?

Is there anything in the content of the current record that preordains
the
state of the checkboxes (or the corresponding email addresses) for
each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand


message Hi Graham,

After several test attempts I finally have it working. I had to
tinker
with
it a bit but it doing exactly what you said it would. Thank you very
much!
I
do have one follow up question...First I do agree that maybe putting
these
into a table instead of hard coding it is a better approach. How
would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there
either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is
bound
to
that field - if the field changes the value in the control changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source table
then
they
are bound, and will change as you move from one record to the next.
This
is
what you implied in your penultimate post, but not in your last
post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content of the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




in
message Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different textboxes.
So
when
a
user clicks on one of the check boxes then the email address that
is
tied
to
that check box appears in the corresponding textbox. Once the
user
checks
all
the boxes he/she needs then they can click a command button to
generate
the
email to those email addresses that are shown in the text boxes.
I
haven't
added any of these fields yet until I know exactly how to set it
up.
The
form
I use is a single view so I would need the users to be able to
choose
different checkboxes depending on the record they are updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I
somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and
all
the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
I understand it better now but where do I put these email
addresses?
I
didn't
notice where in the code you wrote. Also are these check boxes
bound
to
my
table or unbound? I would assume they would be bound since
they
can
be
different for other records. If that's the case then do I add
this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only
the
box
is
checked.

My example code is building up a list of email addresses in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end
up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email
function.

If you give all the checkboxes "similar" names, such as
chkSelectEmail1,
chkSelectEmail2, etc then you can use a loop as follows:

Dim sEmails as String
Dim cb as CheckBox
Dim i as Integer
For i = 1 to 5
Set cb = Me("chkSelectEmail" & i)
If cb <> 0 then sEmails = sEmails & cb.Tag & ";"
Next i

Here you would use the Tag property of each of the textboxes
to
store
the
corresponding email address.

The disadvantage with this, as with hard-coding the emails in
your
code,
is
that if the email addresses need to be changed you must open
your
form
in
design view and change the tag properties or change the code.

It would be better to store the email addresses in a table so
they
could
be
retrieved by a DLookup and easily updated when necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
Hi Graham,

Yes each checkbox is associated with one email address. I'm
not
sure
I
follow you with your code though. If it finds 3 check boxes
that
are
true

 

[Guest]

I guess I'm getting confused here. There aren't any fields in my table that
are tied to these checkboxes but I would want to record the state for each
record. Sorry about the confusion. Right now they're not bound to anything
but I would like them to be to store their state.

I do want to see the email addresses but if I can access them from a
different form for modifications, etc. then I'd prefer that. I wasn't aware
of the DLookup function and that's why I chose to put the textboxes.

I have no current fields that are linked to this email function. I would
like to create the fields for these checkboxes to store the state. I guess I
wasn't understanding everything you said. Sorry again!

I thought we'd established that they were unbound and that there were not
any fields in your record to store their state :-S

And no, you don't need the textboxes, but I thought you wanted them so the
user could see the email addresses.

So, what fields DO you have in your recordset that are related to this
emailing operation?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Well wouldn't these checkboxes and textboxes have to be bound to save the
state they are in for that particular record? Also, if I'm going to use
the
DLookup then I guess I don't even need the textboxes. I could just have
the
checkboxes point to the table where the email addresses are stored.

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3 etc and
the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for CEOCheck is
1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the
corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress", "tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter
corrective
actions. The check boxes are used to send a notification email to the
managers to let them know the corrective action is complete and ready
for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that
preordains
the
state of the checkboxes. It's just used as a trigger to let the email
function know which managers to email. What I want to do now is set up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup each
email
address and send it to the ones that are checked using the checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup
could
look for the title that matches the check box and then use the email
address
in that record. I hope what i wrote makes sense. Let me know if you
need
more
information.

:

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless you
give
some more details about your structure and business rules and answer
my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is its
recordsource?

Is there anything in the content of the current record that preordains
the
state of the checkboxes (or the corresponding email addresses) for
each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand


message Hi Graham,

After several test attempts I finally have it working. I had to
tinker
with
it a bit but it doing exactly what you said it would. Thank you very
much!
I
do have one follow up question...First I do agree that maybe putting
these
into a table instead of hard coding it is a better approach. How
would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there
either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is
bound
to
that field - if the field changes the value in the control changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source table
then
they
are bound, and will change as you move from one record to the next.
This
is
what you implied in your penultimate post, but not in your last
post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content of the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




in
message Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different textboxes.
So
when
a
user clicks on one of the check boxes then the email address that
is
tied
to
that check box appears in the corresponding textbox. Once the
user
checks
all
the boxes he/she needs then they can click a command button to
generate
the
email to those email addresses that are shown in the text boxes.
I
haven't
added any of these fields yet until I know exactly how to set it
up.
The
form
I use is a single view so I would need the users to be able to
choose
different checkboxes depending on the record they are updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I
somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and
all
the
relevant fields. There is probably a better way to do all this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
I understand it better now but where do I put these email
addresses?
I
didn't
notice where in the code you wrote. Also are these check boxes
bound
to
my
table or unbound? I would assume they would be bound since
they
can
be
different for other records. If that's the case then do I add
this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only
the
box
is
checked.

My example code is building up a list of email addresses in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will end
up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your email
function.

 

[Graham Mandeno]

OK, now I see where we are and where we are going

I will put together a suggested solution for you, but I don't have time to
do so today. First though, can you please tell me what is the name and data
type of the primary key in tblCorrectiveActions?
--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

I guess I'm getting confused here. There aren't any fields in my table that
are tied to these checkboxes but I would want to record the state for each
record. Sorry about the confusion. Right now they're not bound to anything
but I would like them to be to store their state.

I do want to see the email addresses but if I can access them from a
different form for modifications, etc. then I'd prefer that. I wasn't
aware
of the DLookup function and that's why I chose to put the textboxes.

I have no current fields that are linked to this email function. I would
like to create the fields for these checkboxes to store the state. I guess
I
wasn't understanding everything you said. Sorry again!

I thought we'd established that they were unbound and that there were not
any fields in your record to store their state :-S

And no, you don't need the textboxes, but I thought you wanted them so
the
user could see the email addresses.

So, what fields DO you have in your recordset that are related to this
emailing operation?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Well wouldn't these checkboxes and textboxes have to be bound to save
the
state they are in for that particular record? Also, if I'm going to use
the
DLookup then I guess I don't even need the textboxes. I could just have
the
checkboxes point to the table where the email addresses are stored.

:

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3 etc
and
the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for CEOCheck
is
1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the
corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress", "tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter
corrective
actions. The check boxes are used to send a notification email to
the
managers to let them know the corrective action is complete and
ready
for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that
preordains
the
state of the checkboxes. It's just used as a trigger to let the
email
function know which managers to email. What I want to do now is set
up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup
each
email
address and send it to the ones that are checked using the
checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup
could
look for the title that matches the check box and then use the email
address
in that record. I hope what i wrote makes sense. Let me know if you
need
more
information.

:

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless
you
give
some more details about your structure and business rules and
answer
my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is
its
recordsource?

Is there anything in the content of the current record that
preordains
the
state of the checkboxes (or the corresponding email addresses) for
each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand


in
message Hi Graham,

After several test attempts I finally have it working. I had to
tinker
with
it a bit but it doing exactly what you said it would. Thank you
very
much!
I
do have one follow up question...First I do agree that maybe
putting
these
into a table instead of hard coding it is a better approach. How
would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource
properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there
either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated
and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is
bound
to
that field - if the field changes the value in the control
changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source table
then
they
are bound, and will change as you move from one record to the
next.
This
is
what you implied in your penultimate post, but not in your last
post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content of
the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




"Secret Squirrel" <[email protected]>
wrote
in
message
Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different
textboxes.
So
when
a
user clicks on one of the check boxes then the email address
that
is
tied
to
that check box appears in the corresponding textbox. Once the
user
checks
all
the boxes he/she needs then they can click a command button to
generate
the
email to those email addresses that are shown in the text
boxes.
I
haven't
added any of these fields yet until I know exactly how to set
it
up.
The
form
I use is a single view so I would need the users to be able to
choose
different checkboxes depending on the record they are
updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I
somehow
assumed
they were unbound.

Please tell us a bit about the structure of your table(s) and
all
the
relevant fields. There is probably a better way to do all
this.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"Secret Squirrel" <[email protected]>
wrote
in
message
I understand it better now but where do I put these email
addresses?
I
didn't
notice where in the code you wrote. Also are these check
boxes
bound
to
my
table or unbound? I would assume they would be bound since
they
can
be
different for other records. If that's the case then do I
add
this
code
in
the currentevent of the form?

:

Thanks for the clarification.

The line:
If Checkbox1 <> 0 Then
tests to see if Checkbox1 is checked.

The next block of code (up to the End If) is executed only
the
box
is
checked.

My example code is building up a list of email addresses
in a
string
variable names sEmails.

If only boxes 1, 3 & 5 are checked, then this string will
end
up
looking
like this:

"(e-mail address removed);[email protected];[email protected];"

A string like this should be suitable to pass to your
email
function.

 

[Guest]

The name of the primary key is "RMA" and it's datatype is AutoNumber.

OK, now I see where we are and where we are going

I will put together a suggested solution for you, but I don't have time to
do so today. First though, can you please tell me what is the name and data
type of the primary key in tblCorrectiveActions?
--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

I guess I'm getting confused here. There aren't any fields in my table that
are tied to these checkboxes but I would want to record the state for each
record. Sorry about the confusion. Right now they're not bound to anything
but I would like them to be to store their state.

I do want to see the email addresses but if I can access them from a
different form for modifications, etc. then I'd prefer that. I wasn't
aware
of the DLookup function and that's why I chose to put the textboxes.

I have no current fields that are linked to this email function. I would
like to create the fields for these checkboxes to store the state. I guess
I
wasn't understanding everything you said. Sorry again!

I thought we'd established that they were unbound and that there were not
any fields in your record to store their state :-S

And no, you don't need the textboxes, but I thought you wanted them so
the
user could see the email addresses.

So, what fields DO you have in your recordset that are related to this
emailing operation?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Well wouldn't these checkboxes and textboxes have to be bound to save
the
state they are in for that particular record? Also, if I'm going to use
the
DLookup then I guess I don't even need the textboxes. I could just have
the
checkboxes point to the table where the email addresses are stored.

:

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3 etc
and
the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for CEOCheck
is
1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the
corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress", "tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter
corrective
actions. The check boxes are used to send a notification email to
the
managers to let them know the corrective action is complete and
ready
for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that
preordains
the
state of the checkboxes. It's just used as a trigger to let the
email
function know which managers to email. What I want to do now is set
up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup
each
email
address and send it to the ones that are checked using the
checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the DLookup
could
look for the title that matches the check box and then use the email
address
in that record. I hope what i wrote makes sense. Let me know if you
need
more
information.

:

Hi SS,

I'm sorry, I really can't tell you the best way to do this unless
you
give
some more details about your structure and business rules and
answer
my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is
its
recordsource?

Is there anything in the content of the current record that
preordains
the
state of the checkboxes (or the corresponding email addresses) for
each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand


in
message Hi Graham,

After several test attempts I finally have it working. I had to
tinker
with
it a bit but it doing exactly what you said it would. Thank you
very
much!
I
do have one follow up question...First I do agree that maybe
putting
these
into a table instead of hard coding it is a better approach. How
would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource
properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put there
either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is calculated
and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control is
bound
to
that field - if the field changes the value in the control
changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source table
then
they
are bound, and will change as you move from one record to the
next.
This
is
what you implied in your penultimate post, but not in your last
post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content of
the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




"Secret Squirrel" <[email protected]>
wrote
in
message
Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different
textboxes.
So
when
a
user clicks on one of the check boxes then the email address
that
is
tied
to
that check box appears in the corresponding textbox. Once the
user
checks
all
the boxes he/she needs then they can click a command button to
generate
the
email to those email addresses that are shown in the text
boxes.
I
haven't
added any of these fields yet until I know exactly how to set
it
up.
The
form
I use is a single view so I would need the users to be able to
choose
different checkboxes depending on the record they are
updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I
somehow
assumed
they were unbound.

 

[Graham Mandeno]

OK, now the WRONG way to do this is to add five yes/no fields (one for each
email that was sent) to tblCorrectiveActions. This would be breaking one of
the fundamental rules of database design, which is that repeating items of
data of the same type (in this case, who got an email) should be stored one
per record, and not one per field in a single record.

Storing them in different fields creates problems when you want to
subsequently query the data, not to mention the upheaval that would occur
when you decide that you need to add a 6th or 7th possible email address.

The CORRECT way to do this is using two tables, one for Corrective Actions
and one for possible email addresses, and then use a THIRD table to model a
many-to-many relationship between the first two. One RMA report can be sent
to many addresses, and one address can receive many reports. This table is
known as a "junction table".

So, the first step is to create a table for the email addresses. Call it
tblEmails, with the following fields:
EmailID: AutoNumber, primary key
EmailTitle: Text, required, indexed with no duplicates
EmailAddress: Text

Load this up with your five email recipients.

Now, create the junction table, tblNotifications:
RMA: Number, long integer (delete the default value of 0)
EmailID: Number, long integer (delete the default value of 0)
These two fields both form a composite primary key - select both fields and
click the PK button on the toolbar.

Now, so far this is all standard stuff. If you add a record to
tblNotifications, it indicates that the report for the given RMA was emailed
to the given EmailID.

The tricky bit comes in setting up a form to manage the creation and
deletion of these records in a user-friendly way.

Traditionally, you would create a subform on your Corrective Actions form
and add records using a combo box listing all the possible emails, and if
necessary delete them using the delete record button. However, I find this
very clunky and ugly and not at all intuitive. As you have already
suggested, it is much nicer to have a list of possibilities with checkboxes.

So, we do that like this. Follow these instructions carefully!

Create a small form named sbfNotifications. Set the following form
properties:
Record Source: <leave blank>
Default View: Continuous Forms
Scroll Bars: Neither
and set all these properties to "No":
Allow Edits
Allow Deletions
Allow Additions
Data Entry
Record Selectors
Navigation Buttons

Add a form header and footer and make the header have zero height, and the
footer enough height for a command button.

Add to the detail section a checkbox, a textbox, and a very small command
button. Set the following properties:

Checkbox:
Name: chkSelect
Control Source: RecordExists
Locked: Yes
Enabled: No

Textbox:
Name: txtTitle
Control Source: EmailTitle
Locked: Yes
Enabled: No

Command button:
Name: cmdToggle
Transparent: Yes
(the size and position of this command button do not matter)

Now, select the command button and do Format>Bring to front, then select
both the checkbox and do Format>Send to back

Now add a command button to the form footer:
Name: cmdSendEmails
Caption: Send Emails

Now open the code module for the form and paste this code:

'============= START OF CODE ================
Option Compare Database
Option Explicit

Private Sub Form_Load()
' position cmdToggle directly over the checkbox
With cmdToggle
.Top = chkSelect.Top
.Left = chkSelect.Left
.Height = chkSelect.Height
.Width = chkSelect.Width
End With
End Sub

Public Sub SetRecordSource()
' synchronise the recordsource with the parent form
Dim sRecSource As String
On Error GoTo ProcErr
sRecSource = "Select EmailID, EmailTitle, EmailAddress, " _
& "EmailID In (select EmailID from tblNotifications where RMA=" _
& Nz(Me.Parent!RMA, 0) & ") as RecordExists from tblEmails " _
& "order by EmailID"
Me.RecordSource = sRecSource
ProcEnd:
On Error Resume Next
Exit Sub
ProcErr:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume ProcEnd
End Sub

Private Sub cmdToggle_Click()
' add or delete junction record as required
Dim sSql As String
On Error GoTo ProcErr
If Me!RecordExists Then
sSql = "Delete * from tblNotifications where RMA=" _
& Me.Parent!RMA & " and EmailID=" & Me!EmailID
Else
sSql = "Insert into tblNotifications (RMA, EmailID) " _
& "values (" & Me.Parent!RMA & "," & Me!EmailID & ")"
End If
CurrentDb.Execute sSql, dbFailOnError
ProcEnd:
On Error Resume Next
DoEvents
Me.Requery
Exit Sub
ProcErr:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume ProcEnd
End Sub

Private Sub cmdSendEmails_Click()
' send emails to selected recipients
Dim rsc As DAO.Recordset
Dim sEmails As String
On Error GoTo ProcErr
Set rsc = Me.RecordsetClone
With rsc
.MoveFirst
Do Until .EOF
If !RecordExists Then
sEmails = sEmails & !EmailAddress & ";"
End If
.MoveNext
Loop
End With
If Len(sEmails) = 0 Then
MsgBox "No recipients selected"
Else
sEmails = Left(sEmails, Len(sEmails) - 1)
' insert call to your email function <<<<<<<
MsgBox "Sending email to " & sEmails
End If
ProcEnd:
On Error Resume Next
Set rsc = Nothing
Exit Sub
ProcErr:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume ProcEnd
End Sub
'============= END OF CODE ================

Now, save this form and open your Corrective Actions form in design view.

Delete any existing email checkboxes and add sbfNotifications as a subform.

Finally, add the following line of code to both the Form_Current and
Form_AfterInsert event procedures:

Call Me.sbfNotifications.Form.SetRecordSource

Adjust the height of the subform so it is tall enough for all the possible
email addresses, plus the footer with the command button.

Phew!!!! Let me know how you get on.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

The name of the primary key is "RMA" and it's datatype is AutoNumber.

OK, now I see where we are and where we are going

I will put together a suggested solution for you, but I don't have time
to
do so today. First though, can you please tell me what is the name and
data
type of the primary key in tblCorrectiveActions?
--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

I guess I'm getting confused here. There aren't any fields in my table
that
are tied to these checkboxes but I would want to record the state for
each
record. Sorry about the confusion. Right now they're not bound to
anything
but I would like them to be to store their state.

I do want to see the email addresses but if I can access them from a
different form for modifications, etc. then I'd prefer that. I wasn't
aware
of the DLookup function and that's why I chose to put the textboxes.

I have no current fields that are linked to this email function. I
would
like to create the fields for these checkboxes to store the state. I
guess
I
wasn't understanding everything you said. Sorry again!

:

I thought we'd established that they were unbound and that there were
not
any fields in your record to store their state :-S

And no, you don't need the textboxes, but I thought you wanted them so
the
user could see the email addresses.

So, what fields DO you have in your recordset that are related to this
emailing operation?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Well wouldn't these checkboxes and textboxes have to be bound to
save
the
state they are in for that particular record? Also, if I'm going to
use
the
DLookup then I guess I don't even need the textboxes. I could just
have
the
checkboxes point to the table where the email addresses are stored.

:

OK, so the five checkboxes and the five textboxes are all unbound?

In this case, let's say the checkboxes are named chkEmail1, 2, 3
etc
and
the
textboxes are named txtEmail1, 2, 3 etc, and the EmailID for
CEOCheck
is
1,
SalesCheck is 2, etc.

You can bind each textbox to an expression that looks up the
corresponding
email address.

For txtEmail1:
=IIf(chkEmail1=0, Null, DLookup("EmailAddress",
"tblEmailAddresses",
"EmailID=1"))

....and so on for the others.

You might have to requery the corresponding textbox in the
AfterUpdate
procedure of each checkbox if it doesn't happen automatically:

Private Sub chkEmail1_AfterUpdate()
txtEmail1.Requery
End Sub


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

in
message Hi Graham,

I will do my best to answer your questions...

The form has about 20 more controls on it and is used to enter
corrective
actions. The check boxes are used to send a notification email to
the
managers to let them know the corrective action is complete and
ready
for
them to sign off on it. The form is bound to a table called
"tblCorrectiveActions". There is nothing else on the form that
preordains
the
state of the checkboxes. It's just used as a trigger to let the
email
function know which managers to email. What I want to do now is
set
up
another table with just 3 fields in it. "EmailID", "Title", and
"EmailAddress". Then I want to use the DLookup function to lookup
each
email
address and send it to the ones that are checked using the
checkboxes.
Each
check box has been defined as "CEOCheck", "SalesCheck",
"EngineeringCheck",
"ProductionCheck", and "QualityCheck". So I was thinking the
DLookup
could
look for the title that matches the check box and then use the
email
address
in that record. I hope what i wrote makes sense. Let me know if
you
need
more
information.

:

Hi SS,

I'm sorry, I really can't tell you the best way to do this
unless
you
give
some more details about your structure and business rules and
answer
my
previous questions. In particular:

What else is on your form? I assume the form is bound - what is
its
recordsource?

Is there anything in the content of the current record that
preordains
the
state of the checkboxes (or the corresponding email addresses)
for
each
record?

--

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Secret Squirrel" <[email protected]>
wrote
in
message
Hi Graham,

After several test attempts I finally have it working. I had
to
tinker
with
it a bit but it doing exactly what you said it would. Thank
you
very
much!
I
do have one follow up question...First I do agree that maybe
putting
these
into a table instead of hard coding it is a better approach.
How
would
I
set
up the DLookUp for this scenerio?

:

Do the checkboxes have anything in their ControlSource
properties?
This
is
what defines bound/unbound.

ControlSource can either be:
a) blank: the control in unbound and any value is put
there
either
by
the user (if it's editable) or by code.
b) an expression starting with "=": the value is
calculated
and
cannot
be modified by the user or by code
c) the name of a field in your recordsource: the control
is
bound
to
that field - if the field changes the value in the control
changes,
and
vice-versa.

If the checkboxes all correspond to fields in your source
table
then
they
are bound, and will change as you move from one record to the
next.
This
is
what you implied in your penultimate post, but not in your
last
post,
so
I'm
still unsure of the situation.

Where do the email addresses come from? Are they in a table
somewhere?

What else is on your form? Is there anything in the content
of
the
current
record that preordains the state of the checkboxes (or the
corresponding
email addresses) for each record?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand




"Secret Squirrel" <[email protected]>
wrote
in
message
Well here's exactly what I'm trying to do. I want to put 5
checkboxes
on
my
form that will trigger an email address in 5 different
textboxes.
So
when
a
user clicks on one of the check boxes then the email
address
that
is
tied
to
that check box appears in the corresponding textbox. Once
the
user
checks
all
the boxes he/she needs then they can click a command button
to
generate
the
email to those email addresses that are shown in the text
boxes.
I
haven't
added any of these fields yet until I know exactly how to
set
it
up.
The
form
I use is a single view so I would need the users to be able
to
choose
different checkboxes depending on the record they are
updating. I
think
I
understand the code you wrote but the bounb/unbound part is
confusing
me.

i.e.
CheckBox1 = True
CheckBox2 = True
CheckBox3 = True
CheckBox4 = False
CheckBox5 = False

TextBox1 = "(e-mail address removed)"
TextBox2 = "(e-mail address removed)"
TextBox3 = "(e-mail address removed)"
TextBox4 = Null
TextBox5 = Null


:

Hmmmm... I didn't realise these checkboxes were bound. I
somehow
assumed
they were unbound.