validation failure in where loop

[skea]

Hello There, Can some one help me with this.
I have a button which calls a chart report but i need to validate it in that if there no records in the recordset then it displays my message but the problem is that i have failed to find where to put the validation in this where loop code snippet and which type of SQL statement i refer to. Do i create a new SQL statement?,Refer to SQL_str or WHERE_str.

private Sub LineGBtn_Click()
Dim SQL_str As String, WHERE_str As String
Dim newFlag As Boolean

SQL_str = "SELECT QryIncidents.* FROM QryIncidents "
WHERE_str = ""
newFlag = False

If Trim(Me.ChartDist) = "" And Trim(Me.ChartYear) = "" Then
Else

WHERE_str = WHERE_str & "WHERE ("

   If Not Trim(Me.ChartDist) = "" Then
   WHERE_str = WHERE_str & "((QryIncidents.District)='" & Trim(Me.ChartDist) & "')"
   newFlag = True
   End If
   
   If Not Trim(Me.ChartYear) = "" Then
   If newFlag = True Then
   WHERE_str = WHERE_str & " AND "
   End If
   
   WHERE_str = WHERE_str & "( (QryIncidents.Year)='" & Trim(Me.ChartYear) & "')"
    newFlag = True
    End If


WHERE_str = WHERE_str & ")"
SQL_str = SQL_str & " " & WHERE_str
 
End If

On Error GoTo Err_T

Go_On:
CurrentDb.CreateQueryDef "ChartQry", SQL_str       
DoEvents     
                                    
****Here is My Validation but which SQl statement *******
  'Dim rs As DAO.Recordset
  'Set rs = CurrentDb.OpenRecordset(SQL_str)
  'If rs.RecordCount = 0 Then
  'MsgBox "No Records To Display Line Graph"
   'Exit Sub
   'End If
**********************************************
DoCmd.OpenReport "LineGraph", acViewPreview
DoCmd.Close acForm, "ReporterF"
Me.Visible = False

Exit Sub

Err_T:
If Err.Number = 3012 Then                 
CurrentDb.Execute "drop table ChartQry"
GoTo Go_On
End If
End Sub

 

[richary]

your code looks fine, although I haven't checked the detailed logic building the SQL string.I reckon you should use the SQL string that underlies the Chart reportas a basis to check whether or not records have been returned.

However one thing that does stand out in that you use RecordCount to check the number of records returned in the commented out section. Access does not reliably calculate the number of records until it has moved to the last records in the recordset.You need to use a rst.MoveLast statement.