I have a query in the DB that runs fine when I use it by itself. When I put it on a code block, it throws an error that asks for a parameter. You can see in the code block below that there's a comment line with the query followed by a line with the SQL instead. Both throw the same error. What's up with this?
Private Sub cmbRegion_Change()
Dim dbsRegionTotals As DAO.Database
Dim rstRegionTotals As DAO.Recordset
Set dbsRegionTotals = CurrentDb
'Set rstRegionTotals = dbsRegionTotals.OpenRecordset("qryRegionEmailCount ")
Set rstRegionTotals = dbsRegionTotals.OpenRecordset("SELECT Count(tblAgents.Email) AS CountOfEmail " & _
"FROM tblAgents LEFT JOIN tblRegionRank ON (tblAgents.Brokerage = tblRegionRank.Brokerage) " & _
"AND (tblAgents.LastName = tblRegionRank.LastName) AND (tblAgents.FirstName = tblRegionRank.FirstName) " & _
"WHERE (((tblRegionRank.Region)=[Forms]![frmRegionData]![cmbRegion]));")
txtRegionEmailAddresses = rstRegionTotals!CountOfEmail
rstRegionTotals.Close 'Closes the recordset, but leaves it in memory to be re-opened later.
Set rstRegionTotals = Nothing 'Releases the recordset from memory
End Sub
Thanks,
Rin