If statement logic

[Guest]

Wow that was a mouthful! Thanks for doing this. I will test it out tonight
and let you know how it goes. I greatly appreciate your help!

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

message 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