I have attached a cut down version of my Database.
What i am trying to achieve is that on the Form FrmQcNonConformanceDeail we have a field TBSubCat.
I am trying to look at this field, and then pull the email address's from the Query QryNCEmails. against that TbSubcat field and that Job, and then move on to the next email address for the same field tbsubcat, and the same job. So i can create a email to send to all the emails inthe Query QryNCEmails.
Example - I need it to pull both all email address's for the All Tbsubcat that are linked to job 00116634.
The first issue is have is that line Set Index line of code - Is bringing back a error 3061 Too few Parameters - Expected 1
The next issue i have is trying to loop through the email address's and bring back the relevant email address's for the users who are linked to job 00116634
appreciate any help.
What i am trying to achieve is that on the Form FrmQcNonConformanceDeail we have a field TBSubCat.
I am trying to look at this field, and then pull the email address's from the Query QryNCEmails. against that TbSubcat field and that Job, and then move on to the next email address for the same field tbsubcat, and the same job. So i can create a email to send to all the emails inthe Query QryNCEmails.
Example - I need it to pull both all email address's for the All Tbsubcat that are linked to job 00116634.
The first issue is have is that line Set Index line of code - Is bringing back a error 3061 Too few Parameters - Expected 1
The next issue i have is trying to loop through the email address's and bring back the relevant email address's for the users who are linked to job 00116634
appreciate any help.
Code:
Function GetEmailList()
Dim Index As Recordset
Dim WhereClause As String
GetEmailList = ""
usertype = "Buying"
WhereClause = "WHERE (User_Type_Desc='" & usertype & "') AND ([Product_range]='" & Forms!frmQcnonConformanceDetail.tbSubCat & "');"
Debug.Print WhereClause
Set Index = CurrentDb.OpenRecordset("SELECT DISTINCT EMail FROM tblqcusers INNER JOIN tblqcUserTypes ON tblqcusers.User_Type_ID = tblqcUserTypes.User_Type_ID " & WhereClause)
If Not Index.EOF Then
Index.MoveFirst
While Not Index.EOF
GetEmailList = GetEmailList & Nz(Index("Email"), "") & ";"
Index.MoveNext
Wend
End If
Index.Close
Set Index = Nothing
On Error Resume Next
GetEmailList = Left(GetEmailList, Len(GetEmailList) - 1)
On Error GoTo 0
End Function