Concencate Emails, Error type declaration does not match

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I found this code on the message boards to build a string of email addresses
but I'm getting an error "Type Declaration does not match declared data type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; " How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", , MySubject,
MyMessage, True

End Sub
 
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "
 
Thanks. Both seem to work but I'm getting a different error now, "Too few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

Douglas J. Steele said:
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; " How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
 
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. Both seem to work but I'm getting a different error now, "Too few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

Douglas J. Steele said:
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; "
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks. This is how I have the code now per your advise. Now I'm getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", , MySubject,
MyMessage, True

End Sub

Douglas J. Steele said:
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. Both seem to work but I'm getting a different error now, "Too few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

Douglas J. Steele said:
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; "
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
You're missing spaces between Data and INNER JOIN, between ON and Data and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. This is how I have the code now per your advise. Now I'm getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

Douglas J. Steele said:
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. Both seem to work but I'm getting a different error now, "Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & ";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I didn't know what the underscores ment. Now it's saying "Runtime
Error 2295. Unknkown Message Recipients...". When I type Debug.Print ToVar
it gives the right string.

Debug.Print ToVar
(e-mail address removed); (e-mail address removed);


Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data " & "INNER JOIN Personel
ON " & "Data.TestAssignedTo = Personel.Initials " & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, , , MySubject, MyMessage, True

End Sub

Douglas J. Steele said:
You're missing spaces between Data and INNER JOIN, between ON and Data and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. This is how I have the code now per your advise. Now I'm getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

Douglas J. Steele said:
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. Both seem to work but I'm getting a different error now, "Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & ";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
One possibility is the semi-colon at the end. Try adding Left(ToVar,
Len(ToVar)-1):

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop
ToVar = Left$(ToVar, Len(ToVar) - 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. I didn't know what the underscores ment. Now it's saying
"Runtime
Error 2295. Unknkown Message Recipients...". When I type Debug.Print
ToVar
it gives the right string.

Debug.Print ToVar
(e-mail address removed); (e-mail address removed);


Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data " & "INNER JOIN
Personel
ON " & "Data.TestAssignedTo = Personel.Initials " & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, , , MySubject, MyMessage, True

End Sub

Douglas J. Steele said:
You're missing spaces between Data and INNER JOIN, between ON and Data
and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is
a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to
ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. This is how I have the code now per your advise. Now I'm
getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN
Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

:

Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the
quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. Both seem to work but I'm getting a different error now,
"Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match
declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") &
";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " &
Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks. That worked.
Marcie

Douglas J. Steele said:
One possibility is the semi-colon at the end. Try adding Left(ToVar,
Len(ToVar)-1):

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop
ToVar = Left$(ToVar, Len(ToVar) - 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. I didn't know what the underscores ment. Now it's saying
"Runtime
Error 2295. Unknkown Message Recipients...". When I type Debug.Print
ToVar
it gives the right string.

Debug.Print ToVar
(e-mail address removed); (e-mail address removed);


Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data " & "INNER JOIN
Personel
ON " & "Data.TestAssignedTo = Personel.Initials " & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, , , MySubject, MyMessage, True

End Sub

Douglas J. Steele said:
You're missing spaces between Data and INNER JOIN, between ON and Data
and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is
a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to
ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. This is how I have the code now per your advise. Now I'm
getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN
Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

:

Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the
quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. Both seem to work but I'm getting a different error now,
"Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match
declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") &
";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " &
Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top