Hi Guys,
Im trying to loop through a query results to extract email addresses from a query result.
I have the following code
NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
sqlStr = "SELECT tbl_Courses.[E-Mail Address] " & _
"FROM tbl_Course_Details INNER JOIN tbl_Courses ON (tbl_Course_Details.[Course Name] = tbl_Courses.[Course Name]) AND (tbl_Course_Details.[Course Date] = tbl_Courses.Date) " & _
"WHERE (((tbl_Course_Details.[Course Name])="" & NameCriteria & "") AND ((tbl_Course_Details.[Course Date])=#" & DateCriteria & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlStr)
rs.MoveFirst
Do While Not rs.EOF
MsgBox "User Email = " & rs![E-Mail Address]
rs.MoveNext
Loop
If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?
Someone on another post said that sql uses US date format so i tried converting to us date and still no results?
Thanks in advance.
Im trying to loop through a query results to extract email addresses from a query result.
I have the following code
NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
sqlStr = "SELECT tbl_Courses.[E-Mail Address] " & _
"FROM tbl_Course_Details INNER JOIN tbl_Courses ON (tbl_Course_Details.[Course Name] = tbl_Courses.[Course Name]) AND (tbl_Course_Details.[Course Date] = tbl_Courses.Date) " & _
"WHERE (((tbl_Course_Details.[Course Name])="" & NameCriteria & "") AND ((tbl_Course_Details.[Course Date])=#" & DateCriteria & "#));"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlStr)
rs.MoveFirst
Do While Not rs.EOF
MsgBox "User Email = " & rs![E-Mail Address]
rs.MoveNext
Loop
If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?
Someone on another post said that sql uses US date format so i tried converting to us date and still no results?
Thanks in advance.