Should be asimple VBA SQL query

[carl6885]

Hi

I have built a query in the design view in Access and the query works but when moving it into VBA it doesn't!

I have a form that logs an error and the user can select two areas and two teams if they want. (Second is optional)

When they log it I want to retrieve the email from tblUsers of the team manager.

When using VBA it only returns 1 email when I know the options selected should return two and as I said it does in the Query Designer.

Here is the code: (FYI I know DAO is outdated I will be moving to ADO shortly)

Public Sub SendBreachEmail()

Dim LoggedArea1 As String
Dim LoggedArea2 As String
Dim LoggedTeam1 As String
Dim LoggedTeam2 As String


LoggedArea1 = Form_frmLogBreach.cboAreaCaused1
LoggedArea2 = Nz(Form_frmLogBreach.cboAreaCaused2, "")
LoggedTeam1 = Form_frmLogBreach.cboTeamCaused1
LoggedTeam2 = Nz(Form_frmLogBreach.cboTeamCaused2, "")

Dim sql As String
Dim rec As DAO.Recordset

If Form_frmLogBreach.cboAreaCaused2 = "" Then

sql = "SELECT tblUsers.Email FROM tblUsers WHERE (((tblUsers.UserGrade)='Manager') AND ((tblUsers.Department)='" & LoggedArea1 & "') AND ((tblUsers.Team)='" & LoggedTeam1 & "'"

Else

sql = "SELECT tblUsers.Email FROM tblUsers WHERE (((tblUsers.UserGrade)='Manager') AND ((tblUsers.Department)='" & LoggedArea1 & "') AND ((tblUsers.Team)='" & LoggedTeam1 & "')) OR (((tblUsers.Department)='" & LoggedArea2 & "') AND ((tblUsers.Team)='" & LoggedTeam2 & "'));"

End If


Set rec = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

MsgBox (rec.RecordCount)

rec.Close

Set rec = Nothing

End Sub

Any help would be greatly appreciated.

Thanks

Carl

 

[pbaldy]

You can't rely on the record count without doing a MoveLast. More info here:

http://www.baldyweb.com/RecordCounts.htm

 

[carl6885]

Paul - I cant believe it was so simple, it was only so I could check it was returning the expect number. i guess I could have iterated through to get the same result.

Thanks

Carl

 

[pbaldy]

Happy to help.

 

[nanscombe]

Assuming you had just wanted to count the records, which you may not have, you could have used the DCount() function which returns a recordcount.

DCount(Expr, Domain, Criteria)

Expr - The field you are interested in, ie EMail
Domain - The table or query, ie tblUsers
Criteria - The criteria, ie the WHERE clause of the sql that you used.

Rather than opening and closing a recordset you could have put the WHERE part of your SQL into a variable, say strWhere, and said ..

Public Sub SendBreachEmail()

Dim LoggedArea1 As String
Dim LoggedArea2 As String
Dim LoggedTeam1 As String
Dim LoggedTeam2 As String

LoggedArea1 = Form_frmLogBreach.cboAreaCaused1
LoggedArea2 = Nz(Form_frmLogBreach.cboAreaCaused2, "")
LoggedTeam1 = Form_frmLogBreach.cboTeamCaused1
LoggedTeam2 = Nz(Form_frmLogBreach.cboTeamCaused2, "")

Dim strWhere As String


strWhere = "(((tblUsers.UserGrade)='Manager') AND ((tblUsers.Department)='" & LoggedArea1 & "') AND ((tblUsers.Team)='" & LoggedTeam1 & "'"

If Form_frmLogBreach.cboAreaCaused2 <> "" Then

  strWhere =  strWhere & " OR (((tblUsers.Department)='" & LoggedArea2 & "') AND ((tblUsers.Team)='" & LoggedTeam2 & "'));"

End If

MsgBox DCount("EMail","tblUsers",strWhere)
End Sub

I could also pull a few tricks when creating the SQL string

' The basic SELECT statement should not return any records by itself because 1 will never equal 0
strSql = "SELECT tblUsers.Email FROM tblUsers WHERE (1 = 0)"

if LoggedArea1 <> "" then
' If LoggedArea1 is not empty then add on this bit
  strSql = strSql & " OR (((tblUsers.UserGrade)='Manager') AND ((tblUsers.Department)='" & LoggedArea1 & "') AND ((tblUsers.Team)='" & LoggedTeam1 & "'))"
endif

if LoggedArea2 <> "" then
' If LoggedArea2 is not empty then add on this bit
  strSql = strSql & " OR (((tblUsers.Department)='" & LoggedArea2 & "') AND ((tblUsers.Team)='" & LoggedTeam2 & "'))"
endif

strSql = strSql & ";"

I hope this may prove useful in the future.