Public Function EmailString()
Dim rs As dao.Recordset
Dim Email As String
Dim strSQL as String
Dim vResult as Variant
Dim I as Integer
'I build the string separately so I can debug.print it for trouble shooting
'and have a neater layout to see what I am doing.
StrSQL = "SELECT dbo_Contacts.EMail" & _
" FROM (tblWorkflowSteps INNER JOIN tblTitles" & _
" ON tblWorkflowSteps.TitleID = tblTitles.TitleID)" & _
" INNER JOIN (dbo_tbl_Sp_Users INNER JOIN dbo_Contacts" & _
" ON dbo_tbl_Sp_Users.ContactID = dbo_Contacts.ContactID)" &
_
" ON tblTitles.ActiveUserID = dbo_tbl_Sp_Users.ID" & _
" WHERE tblWorkflowSteps.WorkflowID = " & _
[Forms]![frmMainMenu]![subfrmWorkflowOverview].[Form]![WorkflowID]
Set rs = CurrentDb.OpenRecordset( StrSQL)
rs.MoveLast
vResult = rs.GetRows(rs.RecordCount)
'Loop through the array
For I = lBound(vResult) to Ubound(vResult)
Email = Email & ";" & vResult(0,i)
Next I
'Strip off the leading semi-colon
Email = Mid(Email,2)
'Note that this function doesn't return anything.
EmailString = Email
End Function
Of course, you should be able to just loop through the rs without using
getrows.
The following should be just as effective
Public Function EmailString()
Dim rs As dao.Recordset
Dim Email As String
Dim strSQL as String
'I build the string separately so I can debug.print it for trouble shooting
'and have a neater layout to see what I am doing.
StrSQL = "SELECT dbo_Contacts.EMail" & _
" FROM (tblWorkflowSteps INNER JOIN tblTitles" & _
" ON tblWorkflowSteps.TitleID = tblTitles.TitleID)" & _
" INNER JOIN (dbo_tbl_Sp_Users INNER JOIN dbo_Contacts" & _
" ON dbo_tbl_Sp_Users.ContactID = dbo_Contacts.ContactID)" & _
" ON tblTitles.ActiveUserID = dbo_tbl_Sp_Users.ID" & _
" WHERE tblWorkflowSteps.WorkflowID = " & _
[Forms]![frmMainMenu]![subfrmWorkflowOverview].[Form]![WorkflowID]
Set rs = CurrentDb.OpenRecordset( StrSQL)
'Loop through the recordset
If rs.recordCount > 0 then
rs.MoveFirst
while not rs.eof
email = Email & ";" & rs!Email
rs.MoveNext
wend
'Strip off the leading semi-colon
Email = Mid(Email,2)
End If 'records in recordset
'Note that this function doesn't return anything unless you do the following
EmailString = Email
End Function
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
nomadk said:
John,
You were spot on about the concatenation issue and also, I think, about
Join. I now get "Error '5': Invalid Procedure Call or Argument." and it
fails
on the "Email=Join..." line.
Any advice?
Thanks.
John Spencer said:
The problem is that
[Forms]![frmMainMenu]![subfrmWorkflowOverview].[Form]![WorkflowID] is not
being recognized. You need to concatenate in the value. Assuming that
[WorkflowID] is a number, you should be able to use something like the
following.
Public Function EmailString()
Dim rs As dao.Recordset
Dim Email As String
Dim strSQL as String
'I build the string separately so I can debug.print it for trouble
shooting
'and have a neater layout to see what I am doing.
StrSQL = "SELECT dbo_Contacts.EMail" & _
" FROM (tblWorkflowSteps INNER JOIN tblTitles" & _
" ON tblWorkflowSteps.TitleID = tblTitles.TitleID)" & _
" INNER JOIN (dbo_tbl_Sp_Users INNER JOIN dbo_Contacts" &
_
" ON dbo_tbl_Sp_Users.ContactID =
dbo_Contacts.ContactID)" &
_
" ON tblTitles.ActiveUserID = dbo_tbl_Sp_Users.ID" & _
" WHERE tblWorkflowSteps.WorkflowID = " & _
[Forms]![frmMainMenu]![subfrmWorkflowOverview].[Form]![WorkflowID]
Set rs = CurrentDb.OpenRecordset( StrSQL)
rs.MoveLast
Email = Join(rs.GetRows(rs.RecordCount), ";")
'NOTE: You may run into a problem with Join.
' I believe it expects a one dimensional array
' and I believe getrows returns a two-dimensional array
' however it may not matter
End Function"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Hi Everyone,
Access 2003 on XP Pro.
I'm hoping to use the following function to aggregate the results of a
query
into a single form field:
"Public Function EmailString()
Dim rs As dao.Recordset
Dim Email As String
Set rs = CurrentDb.OpenRecordset("SELECT dbo_Contacts.EMail FROM
(tblWorkflowSteps INNER JOIN tblTitles ON tblWorkflowSteps.TitleID =
tblTitles.TitleID) INNER JOIN (dbo_tbl_Sp_Users INNER JOIN dbo_Contacts
ON
dbo_tbl_Sp_Users.ContactID = dbo_Contacts.ContactID) ON
tblTitles.ActiveUserID = dbo_tbl_Sp_Users.ID WHERE
(((tblWorkflowSteps.WorkflowID) =
[Forms]![frmMainMenu]![subfrmWorkflowOverview].[Form]![WorkflowID]))")
rs.MoveLast
Email = Join(rs.GetRows(rs.RecordCount), ";")
End Function"
I call this function from a macro that uses SetValue to insert the
result
into a field on my form.
When I try to run the macro, though, I get "Run-time error '3061': Too
few
parameters. Expected 1." and the code fails on the "Set rs..." line.
The
query returns three records, all with valid Emails.
Suggestions?
TIA
