Empty recordset stops 1

[MacroAlan]

I am building a query and then opening it in a RecordSet. Most of the queries bring back one or more records, but some will only infrequently have records. The variable QUO brings the " character.

    SQLstr = "SELECT ExhNameFirst([tblExhibitors]![ID]) AS Name, tblExhibits.ExhingYr, tblExhibits.Title, tblExhibits.Class, tblExhibits.Division, tblJudgingResults.ShowMedalLevel, tblJudgingResults.Score" & vbCrLf
    SQLstr = SQLstr & " FROM (tblExhibitors LEFT JOIN tblExhibits ON tblExhibitors.ID = tblExhibits.ExhibitorID) LEFT JOIN tblJudgingResults ON tblExhibits.ID = tblJudgingResults.ExhbitID" & vbCrLf
    SQLstr = SQLstr & " WHERE (((tblExhibits.Title) Is Not Null) And ((tblExhibits.Class) = " & QUO & ExClass(0) & QUO & ") And ((tblJudgingResults.ShowMedalLevel) = " & QUO & Medal(6) & QUO & "))" & vbCrLf
    SQLstr = SQLstr & " ORDER BY tblExhibits.Class, tblJudgingResults.ShowMedalLevel, tblJudgingResults.Score DESC;"

 
On Error GoTo Med8
    Set EXH = DB.OpenRecordset(SQLstr, dbOpenDynaset)

--- This fails if no records found for Medal(6) from Array; never uses the On Error

      EXH.MoveFirst
        If EXH.RecordCount = 0 Then GoTo Med8   'Empty dataset
   
        Do While Not EXH.EOF

Any ideas??

 

[dhookom]

Sometimes you can use a DCount() in your code before opening the recordset. Otherwise I would try the MoveFirst after the RecordCount.

 

[MacroAlan]

Thanks, I am on to something with the DCount()

    G = DCount("[Name]", "qryAllJudgingResults", "[ShowMedalLevel]='" & Medal(0) & "' And [Class]='" & ExClass(0) & "'")
    If G = 0 Then GoTo Med2

Counts the people based on the 2 criteria I need and uses the items from my 2 arrays

 

[Andrzejek]

You can also do:

If EXH.BOF = EXH.EOF Then
    'No records  :-(
Else
    'Do your magic here
End If