Runtime Error 2465

[TiagoDM]

Hi, my brain is getting hot... I dunoo what to do anymore, it keeps showing 2465 error and I tried almost everything. I'm a newbie and this code was found on internet, but was modifyed to my db. I have read many many forums but I have found no solutions. I'm trying to send an email to recepients based on a query.
Here's the code:


Sub Enviar_email_Click()

Dim subject As String, Body As String, EmailAddress As String
Dim SQL As String, SQL2 as String
Dim count As Integer, recount As Integer
Dim db As DAO.Database, rs as DAO.Recordset

'set up the sql string that opens the Groupforemail query for the selected Group on the Form
SQL2 = "SELECT Crianças.[-Email], Crianças.[+Email]" & _
"FROM Salas" & _
"LEFT JOIN Crianças ON Salas.IDSala_salas = Crianças.IDSalas" & _
"WHERE (((Salas.Campo1)='" & [Formulários]![Pesquisa por Sala]![txSala] & "') AND ((Crianças.Desistência)='" & [Formulários]![Pesquisa por Sala]![Verificação13] & "'));" -> this is where the error shows
'SQL = "SELECT [+Email], [-Email] FROM Crianças" -> When I use this instead of SQL2, no problems

'set the Recordset to the SQL string to actually open the query
Set rs = db.OpenRecordset(SQL2)


rs.MoveLast

rs.MoveFirst

recount = rs.RecordCount

'start a for next loop to go through the records (could use For count = 1 To rs.RecordCount)
For count = 1 To recount


If (IsNull(rs![-Email])) And (IsNull(rs![+Email])) Then
EmailAdress = EmailAdress
'MsgBox recount & " " & EmailAddress
ElseIf IsNull(rs![+Email]) And Not IsNull(rs![-Email]) Then
EmailAddress = EmailAddress & rs![-Email] & "; "
'MsgBox recount & " " & EmailAddress
ElseIf IsNull(rs![-Email]) And Not IsNull(rs![+Email]) Then
EmailAddress = EmailAddress & rs![+Email] & "; "
'MsgBox recount & " " & EmailAddress
Else
EmailAddress = EmailAddress & rs![+Email] & "; " & rs![-Email] & "; "
'MsgBox recount & " " & EmailAddress
End If

rs.MoveNext

Next count

EmailAddress = Left(EmailAddress, (Len(EmailAddress) - 2))

'MsgBox recount & " " & EmailAddress

rs.Close

Set rs = Nothing

subject = "test"

Body = "Test to send email to multiple recipients"

DoCmd.SendObject , , , EmailAddress, , , subject, Body, True
End Sub

 

[TiagoDM]

I'm portuguese, so [Formulários] = [Forms] ... and
[txSala] is String
[Verificação13] is true/false.

SQL2 = "SELECT Crianças.[-Email], Crianças.[+Email]" & _
"FROM Salas" & _
"LEFT JOIN Crianças ON Salas.IDSala_salas = Crianças.IDSalas" & _
"WHERE (((Salas.Campo1)='" & [Formulários]![Pesquisa por Sala]![txSala] & "') AND ((Crianças.Desistência)='" & [Formulários]![Pesquisa por Sala]![Verificação13] & "'));"


When I substitute [Formulários]![Pesquisa por Sala]![txSala] for 'textstring' and [Formulários]![Pesquisa por Sala]![Verificação13] for 0, the code works fine. I was trying to 'get' the values from a form.

 

[JANR]

First note, SQL don't like it when keywords like Select, From etc dosen't have spaces around it. Second since VBA/SQL is uses english i don't think you can use Portoguise Formulários, use english Forms.

See if this helps:

SQL2 = " SELECT Crianças.[-Email], Crianças.[+Email]" & _
" FROM Salas" & _
" LEFT JOIN Crianças ON Salas.IDSala_salas = Crianças.IDSalas" & _
" WHERE (((Salas.Campo1)='" & [B]Forms[/B]![Pesquisa por Sala]![txSala] & "') AND ((Crianças.Desistência)='" & [B]Forms[/B]![Pesquisa por Sala]![Verificação13] & "'));"

Note the spaces before keywords.

JR

 

[TiagoDM]

First note, SQL don't like it when keywords like Select, From etc dosen't have spaces around it. Second since VBA/SQL is uses english i don't think you can use Portoguise Formulários, use english Forms.

See if this helps:

SQL2 = " SELECT Crianças.[-Email], Crianças.[+Email]" & _
" FROM Salas" & _
" LEFT JOIN Crianças ON Salas.IDSala_salas = Crianças.IDSalas" & _
" WHERE (((Salas.Campo1)='" & [B]Forms[/B]![Pesquisa por Sala]![txSala] & "') AND ((Crianças.Desistência)='" & [B]Forms[/B]![Pesquisa por Sala]![Verificação13] & "'));"

Note the spaces before keywords.

JR

That's great. Worked like a charm. Only I had to took the ' ' on (...) '" & Forms![Pesquisa por Sala]![Verificação13] & "' to work, I dunno very well but I think it's because the true/false values come out with 0 or 1.

Thankx once again best regards.

 

[JANR]

no problem.

JR