Concatenate Rows

[kike79]

Hello, help please, I need to extract email address from a query result in a field text to use it as TO: in a sendobject command. I hava an error in sql statement, in access query desing data shows without any problem, but with vba code I got this error:


Runtime error '3265' Item not found in this collection, and error points to
Me.Para = Me.Para & "; " & rs!tPAEmails.email

VBA Code:

Private Sub Command12_Click()
Dim rs As DAO.Recordset
Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT tMain.FaxDocAge, tTeamLead.idTL, tPAEmails.Email FROM tPAEmails INNER JOIN ((tMain INNER JOIN tRegion ON tMain.District = tRegion.F1) INNER JOIN tTeamLead ON tRegion.TeamLead = tTeamLead.idTL) ON tPAEmails.ICCLogIn = tMain.LogonName WHERE (((tMain.FaxDocAge)>=4))")

Do While Not rs.EOF
If Me.Para = "" Then
Me.Para = rs!tPAEmails.email
Else
Me.Para = Me.Para & "; " & rs!tPAEmails.email
End If
rs.MoveNext
Loop
' end Concatenate Multiple Records to Text Field
jump_out:
rs.Close
Set rs = Nothing

End Sub




Access Design Query SQL working code:

SELECT tMain.FaxDocAge, tTeamLead.idTL, tPAEmails.Email
FROM tPAEmails INNER JOIN ((tMain INNER JOIN tRegion ON tMain.District=tRegion.F1) INNER JOIN tTeamLead ON tRegion.TeamLead=tTeamLead.idTL) ON tPAEmails.ICCLogIn=tMain.LogonName
WHERE (((tMain.FaxDocAge)>=4) And ((tTeamLead.idTL)=Forms!fMainScreen!TLCombo.value));


Any solution?

Thank you !!!

 

[mdlueck]

Just a guess, put a watch on:

rs!tPAEmails.email

and see what it is set to at the time this code is executing. Perhaps it is really not pointing at what you think it is pointed at.

 

[spikepl]

rs!tPAEmails.email -> rs!email

 

[mdlueck]

I was anticipating a string value, not another object reference.

Perhaps that is why the concatenate is failing.

The only DAO object type I work with is Querydef objects, so I am not familiar with DAO.RecordSet objects. Perhaps someone else will lend a hand.

 

[kike79]

Thank you Mdlueck.

Another suggestion?

 

[penguino29]

Hi kike79,


Sorry to be ignorant, could you please explain what is Me.Para? Is it a field on a form?

I thought maybe you should use a string variable holding the email addresses, and then assign it to the form field afterwards. So it should be like this.

Dim str_Para As String
'---initalise str_Para to an empty string
str_Para = "" 

Do While Not rs.EOF
str_Para  = str_Para & "; " & rs!tPAEmails.email
......
.......
Set rs = Nothing

'--- assign it to the form field
Me!Para = str_Para
End Sub

Am I making sense or have I mistaken?

 

[mdlueck]

Another suggestion?

Oh, I just noticed that it was not a reply from you. Still put a watch on that object and see what is inside. Yes I understand that it is an object pointing to a table record... but what is its value? Oh, and datatype as well. You can find that out in the watches window.

 

[vbaInet]

I would be wary of using Email as a field name. I'm not sure if it's a reserved keyword but you can check that online. In the meantime I would use:

Me.Para = Me.Para & "; " & rs!tPAEmails.[email]

Or:

Put your SQL statement in a query and use the SELECT statement on that query. That is,

[COLOR=#4b0082]Set rs = db.OpenRecordset("SELECT * FROM [COLOR=black][B]QueryName[/B][/COLOR];")

[/COLOR]

Then using penguino's advice:

[COLOR=#4169e1]str_Para = str_Para & "; " & rs![/COLOR][B][[/B][COLOR=#4169e1]email[/COLOR][B]]

[/B]

 

[kike79]

Thank you VbaInet, I tried changing

Set rs = db.OpenRecordset("SELECT * FROM qMain;")

but I got this new error

Runtime error '3601'

Too few parameters. Expexted 1




I've changed a table instead a query and code works fine, I guess that filters that I have on query make the error. I'm still getting same error

Runtime error '3601'

Too few parameters. Expexted 1

I would be wary of using Email as a field name. I'm not sure if it's a reserved keyword but you can check that online. In the meantime I would use:

Me.Para = Me.Para & "; " & rs!tPAEmails.[email]

Or:

Put your SQL statement in a query and use the SELECT statement on that query. That is,

[COLOR=#4b0082]Set rs = db.OpenRecordset("SELECT * FROM [COLOR=black][B]QueryName[/B][/COLOR];")[/COLOR]

Then using penguino's advice:

[COLOR=#4169e1]str_Para = str_Para & "; " & rs![/COLOR][B][[/B][COLOR=#4169e1]email[/COLOR][B]][/B]

 

[vbaInet]

This is where that error is coming from:

(tTeamLead.idTL)=Forms!fMainScreen!TLCombo.value

Ensure that the form is opened and a value is selected in the combo box.

 

[kike79]

Thank you vbaInet, the form is open, I have the button on this form where code is executed

 

[vbaInet]

The recordset doesn't like the form reference. Create a function to get the value from the textbox and use this function in your query. That should resolve the error.

 

[kike79]

Thank you vbaInet, almost done but in last sentence I cant filter using parameters in red, any suggestion?

Code:

Private Sub Command12_Click()
Dim rs As DAO.Recordset
Dim teamlead As String
Set db = CurrentDb
Me.Para.Value = ""
teamlead = Me.txtTeamLead

Set rs = db.OpenRecordset("SELECT tMain.FaxDocumentID, tMain.DateReceived, tMain.AssignedTo, tMain.AccountName, tMain.QueueName, tMain.Date_Received, tMain.FaxDocAge, tMain.QueueType, tMain.DaysInQueue, tMain.District, tPAmails.mail, tRegion.TeamLead FROM (tMain INNER JOIN tPAEmails ON tMain.LogonName = tPAEmails.ICCLogIn) INNER JOIN tRegion ON tMain.District = tRegion.F1 WHERE (((tMain.FaxDocAge)>=4) AND ((tMain.FOBO)='BO') AND ((tRegion.TeamLead)= teamlead))")


Do While Not rs.EOF
If Me.Para = "" Then
Me.Para = rs![mail]
Else
Me.Para = Me.Para & "; " & rs![mail]
End If
rs.MoveNext
Loop
' end Concatenate Multiple Records to Text Field
jump_out:
rs.Close
Set rs = Nothing

End Sub

If I manually change filter value code works as expected: ((tRegion.TeamLead)= '1')

 

[vbaInet]

teamLead is not a function, it's a variable. I specifically mentioned in my last post that you need to create a function to get the value from the textbox. So basically,

Public Function GetTeamLead() As Long
    GetTeamLead = Forms![COLOR=Red]FormName[/COLOR]![COLOR=Red]txtTeamLead[/COLOR]
End Function

Then in your query:

tRegion.TeamLead = GetTeamLead[COLOR=Red]()[/COLOR]

 

[PeterF]

Because the SQL code in VBA is a string it won't evaluate your variable teamlead, you have to place the variable outside the string like:

((tRegion.TeamLead)= [COLOR="Red"]" &[/COLOR] teamlead [COLOR="red"]& "[/COLOR])) 'if teamlead is numeric 

((tRegion.TeamLead)= [COLOR="Red"]'" &[/COLOR] teamlead [COLOR="red"]& "'[/COLOR])) 'if teamlead is text

 

[vbaInet]

It looks like kike79 has reverted back to his original problem without informing us. He/she was advised in my post #8 to create a query based on the SELECT statement his written, and use this query as the new SELECT statement. This was why I advised him to use a function to get the value because the original SELECT statement is in another query which was causing the "Item not found in collection error".

 

[kike79]

Thank you all you guys both PeterF and VbaInet worked!!!!!!!

 

[vbaInet]

Glad to hear!