email problems

A

AJ

I have the following code on a check box on a form. For some records the
code works just fine. For others it doesn't. I can't seem to find a pattern
to when it works and when it doesn't. Can anyone review and provide any
assistance? Thanks in advance.

Dim emlstring1 As String
Dim emlstring2 As String
Dim emlstring3 As String
Dim emlstring4 As String
Dim emlstring5 As String
Dim emlstring6 As String
Dim emlstring7 As String
Dim emlstring8 As String
Dim emlstring9 As String
Dim emstring10 As String
Dim eaddress As String
Dim msgstring1 As String
Dim msgstring2 As String
Dim msgstring3 As String
Dim msgstring4 As String
Dim msgstring5 As String
Dim msgstring6 As String
Dim msgstring7 As String
Dim msgstring8 As String
Dim msgstring9 As String
Dim msgstring10 As String
Dim message As String

emlstring1 = IIf(Me.Combo464 = 0, "", Me.Combo464.Column(1) & ";")
emlstring2 = IIf(Me.Assigned_To_2 = 0, "", Me.Assigned_To_2.Column(1) & ";")
emlstring3 = IIf(Me.Assigned_To_3 = 0, "", Me.Assigned_To_3.Column(1) & ";")
emlstring4 = IIf(Me.Assigned_To_4 = 0, "", Me.Assigned_To_4.Column(1) & ";")
emlstring5 = IIf(Me.Assigned_To_5 = 0, "", Me.Assigned_To_5.Column(1) & ";")
emlstring6 = IIf(Me.Assigned_To_6 = 0, "", Me.Assigned_To_6.Column(1) & ";")
emlstring7 = IIf(Me.Assigned_To_7 = 0, "", Me.Assigned_To_7.Column(1) & ";")
emlstring8 = IIf(Me.Validation_Required = 0, "", "(e-mail address removed);")
emlstring9 = IIf(Me.Stability_Required = 0, "", "(e-mail address removed);")
emlstring10 = IIf(Me.MVTR_Required = 0, "", "(e-mail address removed)")

eaddress = emlstring1 & emlstring2 & emlstring3 & emlstring4 & emlstring5 &
emlstring6 & emlstring7 & emlstring8 & emlstring9 & emlstring10

msgstring1 = IIf(Me.Combo464 = 0, "", "Assigned to - " &
Me.Combo464.Column(1) & Chr(10) & "Assignment: " & Me![Documents 1] &
Chr(10) & "Date Due: " & Me![1 Due Date] & Chr(10) & Chr(10))
msgstring2 = IIf(Me.Assigned_To_2 = 0, "", "Assigned to - " & Me![Assigned
To 2].Column(1) & Chr(10) & "Assignment: " & Me![Documents 2] & Chr(10) &
"Date Due: " & Me![2 Due Date] & Chr(10) & Chr(10))
msgstring3 = IIf(Me.Assigned_To_3 = 0, "", "Assigned to - " & Me![Assigned
To 3].Column(1) & Chr(10) & "Assignment: " & Me![Documents 3] & Chr(10) &
"Date Due: " & Me![3 Due Date] & Chr(10) & Chr(10))
msgstring4 = IIf(Me.Assigned_To_4 = 0, "", "Assigned to - " & Me![Assigned
To 4].Column(1) & Chr(10) & "Assignment: " & Me![Documents 4] & Chr(10) &
"Date Due: " & Me![4 Due Date] & Chr(10) & Chr(10))
msgstring5 = IIf(Me.Assigned_To_5 = 0, "", "Assinged to - " & Me![Assigned
to 5].Column(1) & Chr(10) & "Assignement: " & Me![Documents 5] & Chr(10) &
"Date Due: " & Me![5 Due Date] & Chr(10) & Chr(10))
msgstring6 = IIf(Me.Assigned_To_6 = 0, "", "Assinged to - " & Me![Assigned
to 6].Column(1) & Chr(10) & "Assignement: " & Me![Documents 6] & Chr(10) &
"Date Due: " & Me![6 Due Date] & Chr(10) & Chr(10))
msgstring7 = IIf(Me.Assigned_To_7 = 0, "", "Assigned to - " & Me![Assigned
to 7].Column(1) & Chr(10) & "Assignement: " & Me![Documents 7] & Chr(10) &
"Date Due: " & Me![7 Due Date] & Chr(10) & Chr(10))
msgstring8 = IIf(Me.Validation_Required = 0, "", "Validation Required" &
Chr(10) & Chr(10))
msgstring9 = IIf(Me.Stability_Required = 0, "", "Stability Requried" &
Chr(10) & Chr(10))
msgstring10 = IIf(Me.MVTR_Required = 0, "", "MVTR Required")

message = msgstring1 & msgstring2 & msgstring3 & msgstring4 & msgstring5 &
msgstring6 & msgstring7 & msgstring8 & msgstring8 & msgstring10

DoCmd.SendObject acSendNoObject, , , eaddress, , , "Change Control
Assignments for CCR# - " & Me![Change Request #], message, , False

End Sub
 
C

Clifford Bass

Hi AJ,

When you say it does not work fine for others, what do you mean by
that? I notice that you have one variable declaration:

Dim emstring10 As String (without an l)

But later on you use emlstring10 (with an l). You might want to add
this statement at the top of your module below the other Option statement:

Option Explicit

This will make the compiler complain where you try to use a variable
that you did not declare. It will help catch misspellings. To compile, in
case you do not know, go to the Debug menu and choose Compile Database. If
that is not the only issue, place a break point on the emlstring1 = line by
clicking in the left margin. A big dot will appear. Then when you run the
code it will stop there and display the code. You can hover over variables
with your mouse pointer to see their values. Use Shift-F8 to step through
each line of code so you can see what is actually happening.

Now, because you used the IIf() function it will be harder to catch
quite what is going on. I would suggest you shift to the If ... Then ...
Else ... End If construct. Also, Iif() is slow compared to the If ... Then
construct. It will also allow you to do both of the things at once instead
of testing each value twice:

emlstring1 = IIf(Me.Combo464 = 0, "", Me.Combo464.Column(1) & ";")
and the later on:
msgstring1 = IIf(Me.Combo464 = 0, "", "Assigned to - " &
Me.Combo464.Column(1) & Chr(10) & "Assignment: " & Me![Documents 1] &
Chr(10) & "Date Due: " & Me![1 Due Date] & Chr(10) & Chr(10))

Would become:

If Me.Combo464 = 0 Then
emlstring1 = ""
msgstring1 = ""
Else
emlstring1 = Me.Combo464.Column(1) & ";"
msgstring1 = "Assigned to - " & Me.Combo464.Column(1) & vbCrLf & _
"Assignment: " & Me![Documents 1] & vbCrLf & "Date Due: " & _
Me![1 Due Date] & vbCrLf & vbCrLF
End If

Note that I converted you line feeds (LF's) (Chr(10)'s) into the VBA
constant vbCrLf which is a carriage return followed by a line feed. The
CR/LF combination is more common; just the LF can end up looking funky in
certain situations.

Hope that helps,

Clifford Bass

AJ said:
I have the following code on a check box on a form. For some records the
code works just fine. For others it doesn't. I can't seem to find a pattern
to when it works and when it doesn't. Can anyone review and provide any
assistance? Thanks in advance.

Dim emlstring1 As String
Dim emlstring2 As String
Dim emlstring3 As String
Dim emlstring4 As String
Dim emlstring5 As String
Dim emlstring6 As String
Dim emlstring7 As String
Dim emlstring8 As String
Dim emlstring9 As String
Dim emstring10 As String
Dim eaddress As String
Dim msgstring1 As String
Dim msgstring2 As String
Dim msgstring3 As String
Dim msgstring4 As String
Dim msgstring5 As String
Dim msgstring6 As String
Dim msgstring7 As String
Dim msgstring8 As String
Dim msgstring9 As String
Dim msgstring10 As String
Dim message As String

emlstring1 = IIf(Me.Combo464 = 0, "", Me.Combo464.Column(1) & ";")
emlstring2 = IIf(Me.Assigned_To_2 = 0, "", Me.Assigned_To_2.Column(1) & ";")
emlstring3 = IIf(Me.Assigned_To_3 = 0, "", Me.Assigned_To_3.Column(1) & ";")
emlstring4 = IIf(Me.Assigned_To_4 = 0, "", Me.Assigned_To_4.Column(1) & ";")
emlstring5 = IIf(Me.Assigned_To_5 = 0, "", Me.Assigned_To_5.Column(1) & ";")
emlstring6 = IIf(Me.Assigned_To_6 = 0, "", Me.Assigned_To_6.Column(1) & ";")
emlstring7 = IIf(Me.Assigned_To_7 = 0, "", Me.Assigned_To_7.Column(1) & ";")
emlstring8 = IIf(Me.Validation_Required = 0, "", "(e-mail address removed);")
emlstring9 = IIf(Me.Stability_Required = 0, "", "(e-mail address removed);")
emlstring10 = IIf(Me.MVTR_Required = 0, "", "(e-mail address removed)")

eaddress = emlstring1 & emlstring2 & emlstring3 & emlstring4 & emlstring5 &
emlstring6 & emlstring7 & emlstring8 & emlstring9 & emlstring10

msgstring1 = IIf(Me.Combo464 = 0, "", "Assigned to - " &
Me.Combo464.Column(1) & Chr(10) & "Assignment: " & Me![Documents 1] &
Chr(10) & "Date Due: " & Me![1 Due Date] & Chr(10) & Chr(10))
msgstring2 = IIf(Me.Assigned_To_2 = 0, "", "Assigned to - " & Me![Assigned
To 2].Column(1) & Chr(10) & "Assignment: " & Me![Documents 2] & Chr(10) &
"Date Due: " & Me![2 Due Date] & Chr(10) & Chr(10))
msgstring3 = IIf(Me.Assigned_To_3 = 0, "", "Assigned to - " & Me![Assigned
To 3].Column(1) & Chr(10) & "Assignment: " & Me![Documents 3] & Chr(10) &
"Date Due: " & Me![3 Due Date] & Chr(10) & Chr(10))
msgstring4 = IIf(Me.Assigned_To_4 = 0, "", "Assigned to - " & Me![Assigned
To 4].Column(1) & Chr(10) & "Assignment: " & Me![Documents 4] & Chr(10) &
"Date Due: " & Me![4 Due Date] & Chr(10) & Chr(10))
msgstring5 = IIf(Me.Assigned_To_5 = 0, "", "Assinged to - " & Me![Assigned
to 5].Column(1) & Chr(10) & "Assignement: " & Me![Documents 5] & Chr(10) &
"Date Due: " & Me![5 Due Date] & Chr(10) & Chr(10))
msgstring6 = IIf(Me.Assigned_To_6 = 0, "", "Assinged to - " & Me![Assigned
to 6].Column(1) & Chr(10) & "Assignement: " & Me![Documents 6] & Chr(10) &
"Date Due: " & Me![6 Due Date] & Chr(10) & Chr(10))
msgstring7 = IIf(Me.Assigned_To_7 = 0, "", "Assigned to - " & Me![Assigned
to 7].Column(1) & Chr(10) & "Assignement: " & Me![Documents 7] & Chr(10) &
"Date Due: " & Me![7 Due Date] & Chr(10) & Chr(10))
msgstring8 = IIf(Me.Validation_Required = 0, "", "Validation Required" &
Chr(10) & Chr(10))
msgstring9 = IIf(Me.Stability_Required = 0, "", "Stability Requried" &
Chr(10) & Chr(10))
msgstring10 = IIf(Me.MVTR_Required = 0, "", "MVTR Required")

message = msgstring1 & msgstring2 & msgstring3 & msgstring4 & msgstring5 &
msgstring6 & msgstring7 & msgstring8 & msgstring8 & msgstring10

DoCmd.SendObject acSendNoObject, , , eaddress, , , "Change Control
Assignments for CCR# - " & Me![Change Request #], message, , False

End Sub
Click to expand...
 
A

AJ

First, thank you very much. I am self-taught in Access, all of what I've
learned about coding is from this discussion group. Of which, I am very
thankful to have.

I have rewritten as you suggested below with If instead of IIf. I did the
debug and seems I have all the bugs out of all my code. Again, thank you
very much for the "lesson".

However, I still have the same problem.

What happens is on the form from a drop down box, I can select record # say
428. I can check the "email" box and the email pops up (very quickly now
thanks to you). I can drop down to record 383 and check the box and nothing
happens, ie, the email does not pop-up. From the user perspective absolutely
nothing happens except the check box goes from either checked to unchecked or
vice-versa, but nothing else.

Again, thanks in advance for any help.


Clifford Bass said:
Hi AJ,

When you say it does not work fine for others, what do you mean by
that? I notice that you have one variable declaration:

Dim emstring10 As String (without an l)

But later on you use emlstring10 (with an l). You might want to add
this statement at the top of your module below the other Option statement:

Option Explicit

This will make the compiler complain where you try to use a variable
that you did not declare. It will help catch misspellings. To compile, in
case you do not know, go to the Debug menu and choose Compile Database. If
that is not the only issue, place a break point on the emlstring1 = line by
clicking in the left margin. A big dot will appear. Then when you run the
code it will stop there and display the code. You can hover over variables
with your mouse pointer to see their values. Use Shift-F8 to step through
each line of code so you can see what is actually happening.

Now, because you used the IIf() function it will be harder to catch
quite what is going on. I would suggest you shift to the If ... Then ...
Else ... End If construct. Also, Iif() is slow compared to the If ... Then
construct. It will also allow you to do both of the things at once instead
of testing each value twice:

emlstring1 = IIf(Me.Combo464 = 0, "", Me.Combo464.Column(1) & ";")
and the later on:
msgstring1 = IIf(Me.Combo464 = 0, "", "Assigned to - " &
Me.Combo464.Column(1) & Chr(10) & "Assignment: " & Me![Documents 1] &
Chr(10) & "Date Due: " & Me![1 Due Date] & Chr(10) & Chr(10))

Would become:

If Me.Combo464 = 0 Then
emlstring1 = ""
msgstring1 = ""
Else
emlstring1 = Me.Combo464.Column(1) & ";"
msgstring1 = "Assigned to - " & Me.Combo464.Column(1) & vbCrLf & _
"Assignment: " & Me![Documents 1] & vbCrLf & "Date Due: " & _
Me![1 Due Date] & vbCrLf & vbCrLF
End If

Note that I converted you line feeds (LF's) (Chr(10)'s) into the VBA
constant vbCrLf which is a carriage return followed by a line feed. The
CR/LF combination is more common; just the LF can end up looking funky in
certain situations.

Hope that helps,

Clifford Bass

AJ said:
I have the following code on a check box on a form. For some records the
code works just fine. For others it doesn't. I can't seem to find a pattern
to when it works and when it doesn't. Can anyone review and provide any
assistance? Thanks in advance.

Dim emlstring1 As String
Dim emlstring2 As String
Dim emlstring3 As String
Dim emlstring4 As String
Dim emlstring5 As String
Dim emlstring6 As String
Dim emlstring7 As String
Dim emlstring8 As String
Dim emlstring9 As String
Dim emstring10 As String
Dim eaddress As String
Dim msgstring1 As String
Dim msgstring2 As String
Dim msgstring3 As String
Dim msgstring4 As String
Dim msgstring5 As String
Dim msgstring6 As String
Dim msgstring7 As String
Dim msgstring8 As String
Dim msgstring9 As String
Dim msgstring10 As String
Dim message As String

emlstring1 = IIf(Me.Combo464 = 0, "", Me.Combo464.Column(1) & ";")
emlstring2 = IIf(Me.Assigned_To_2 = 0, "", Me.Assigned_To_2.Column(1) & ";")
emlstring3 = IIf(Me.Assigned_To_3 = 0, "", Me.Assigned_To_3.Column(1) & ";")
emlstring4 = IIf(Me.Assigned_To_4 = 0, "", Me.Assigned_To_4.Column(1) & ";")
emlstring5 = IIf(Me.Assigned_To_5 = 0, "", Me.Assigned_To_5.Column(1) & ";")
emlstring6 = IIf(Me.Assigned_To_6 = 0, "", Me.Assigned_To_6.Column(1) & ";")
emlstring7 = IIf(Me.Assigned_To_7 = 0, "", Me.Assigned_To_7.Column(1) & ";")
emlstring8 = IIf(Me.Validation_Required = 0, "", "(e-mail address removed);")
emlstring9 = IIf(Me.Stability_Required = 0, "", "(e-mail address removed);")
emlstring10 = IIf(Me.MVTR_Required = 0, "", "(e-mail address removed)")

eaddress = emlstring1 & emlstring2 & emlstring3 & emlstring4 & emlstring5 &
emlstring6 & emlstring7 & emlstring8 & emlstring9 & emlstring10

msgstring1 = IIf(Me.Combo464 = 0, "", "Assigned to - " &
Me.Combo464.Column(1) & Chr(10) & "Assignment: " & Me![Documents 1] &
Chr(10) & "Date Due: " & Me![1 Due Date] & Chr(10) & Chr(10))
msgstring2 = IIf(Me.Assigned_To_2 = 0, "", "Assigned to - " & Me![Assigned
To 2].Column(1) & Chr(10) & "Assignment: " & Me![Documents 2] & Chr(10) &
"Date Due: " & Me![2 Due Date] & Chr(10) & Chr(10))
msgstring3 = IIf(Me.Assigned_To_3 = 0, "", "Assigned to - " & Me![Assigned
To 3].Column(1) & Chr(10) & "Assignment: " & Me![Documents 3] & Chr(10) &
"Date Due: " & Me![3 Due Date] & Chr(10) & Chr(10))
msgstring4 = IIf(Me.Assigned_To_4 = 0, "", "Assigned to - " & Me![Assigned
To 4].Column(1) & Chr(10) & "Assignment: " & Me![Documents 4] & Chr(10) &
"Date Due: " & Me![4 Due Date] & Chr(10) & Chr(10))
msgstring5 = IIf(Me.Assigned_To_5 = 0, "", "Assinged to - " & Me![Assigned
to 5].Column(1) & Chr(10) & "Assignement: " & Me![Documents 5] & Chr(10) &
"Date Due: " & Me![5 Due Date] & Chr(10) & Chr(10))
msgstring6 = IIf(Me.Assigned_To_6 = 0, "", "Assinged to - " & Me![Assigned
to 6].Column(1) & Chr(10) & "Assignement: " & Me![Documents 6] & Chr(10) &
"Date Due: " & Me![6 Due Date] & Chr(10) & Chr(10))
msgstring7 = IIf(Me.Assigned_To_7 = 0, "", "Assigned to - " & Me![Assigned
to 7].Column(1) & Chr(10) & "Assignement: " & Me![Documents 7] & Chr(10) &
"Date Due: " & Me![7 Due Date] & Chr(10) & Chr(10))
msgstring8 = IIf(Me.Validation_Required = 0, "", "Validation Required" &
Chr(10) & Chr(10))
msgstring9 = IIf(Me.Stability_Required = 0, "", "Stability Requried" &
Chr(10) & Chr(10))
msgstring10 = IIf(Me.MVTR_Required = 0, "", "MVTR Required")

message = msgstring1 & msgstring2 & msgstring3 & msgstring4 & msgstring5 &
msgstring6 & msgstring7 & msgstring8 & msgstring8 & msgstring10

DoCmd.SendObject acSendNoObject, , , eaddress, , , "Change Control
Assignments for CCR# - " & Me![Change Request #], message, , False

End Sub
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi AJ,

You are quite welcome!

So, it sounds like the problem now is not the code but that it does not
get fired? That is, if you place the break point back in, and you do the
first selection and clicking, the code breaks. (By the way, you can use F5
to tell it to resume execution so that it runs until it encounters another
break point.) Then, when you do the second selection and clicking, it does
not break? Is the "email" box a check box or a button? What is the
subroutine name of the code you posted?

Clifford Bass
 
A

AJ

Private Sub Check484_Click() is the name of the subroutine. It is a
checkbox. I've also tried to create an option button with the same code on
Enter with the exact same results.

When I attempt to put the Option Explicit in... my database "locks" then
closes.

Thanks again for all the help.
 
C

Clifford Bass

Hi AJ,

That makes me think there may be some kind of corruption. Make a
backup copy of your database then do a compact and repair on the original.
Then try adding in the Option Explicit and compiling. If it still crashes,
try doing the decompile and recompile documented at
<http://www.granite.ab.ca/access/decompile.htm>. Now check to see if it
works. If not, add at the beginning of the Check484_Click event this line:

MsgBox "Entered Check484_Click"

Test. Do you get the message each time you click in the check box?

Clifford Bass
 
A

AJ

I did the compact and repair. I added the Msgbox.

Yes, the Msgbox works for all records. Email still not working for all. I
did think of something else, but makes no sense that it is connected... other
than "coincidental" timing... Maybe it will make sense to you... and again
THANK YOU for the help.

I added more fields to the table/form. It is after that when the problem
started. The email is working for all records that have been added since the
update, it is not working for any of the records prior to the update.

Thanks again, for the help and for the quick responses.
 
C

Clifford Bass

Hi AJ,

You are welcome. The adding of fields does make the problem make
sense. You are probably dealing with the fact that the new fields are null
for those older records. Probably the best way to deal with that is to
update those fields that are null with the default value, then make all of
them required. So if you added Assigned_To_7 and it is a Yes/No field, you
might do this update query:

update tblMyTable set Assigned_To_7 = False where Assigned_To_7 is null;

Do a separate statement like that for all of the new fields.

Alternatively, if you want to allow null, you will need to check for
null values in your code. Maybe something like this:

If IsNull(Me.Assigned_To_7) Then

Else
If Me_Assigned_To_7 = 0 Then

Else

End If
End If

You will need to adapt for your actual column types and what you desire to
happen or not happen when null.

Hope that helps,

Clifford Bass
 
A

AJ

PERFECT!!!!

Thank you so much. I'd say sorry for leaving out vital info that went direct
to the solution... But, I learned alot, so I can't say as I'm that sorry :)
Thanks againg for the help
 
C

Clifford Bass

Hi AJ,

You are much welcome! Apology accepted. As you say, you learned much.
Plus you have a much better process. So, that is good!

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VB Send Mail using Thunderbird 6
Error 2465 setting recordsource to SQL 3
True or False for SNP or PDF format to email 12
MS Access setting outlook reminders from a DAO recordset 0
Email Through Windows Live! 2
Adding a CC to email code! 7
send listbox items to report 2
Items from listbox to report 1

Top