Empty Recordset

[Chris Kennedy]

I have a form whose recordsource is changed dyanically by an sql string
which is in part generated from input on the form. This works fine until the
sql returns no records and then my form goes blank. So I thought create a
recordset and test if it is empty before setting the recordsource. But even
when the sql should return something (and I have cut and pasted it from the
debugger etc) the recordset is always empty. I am using Access 2000.

Here is the code

txtFilter.SetFocus

strFilter = "SELECT DISTINCT Inscriptions.InscriptionID, Inscriptions.* " &
_
" FROM Inscriptions " & _
" WHERE (select top 1 tblNames.Surname from tblNames where " & _
" tblNames.InscriptionID =Inscriptions.InscriptionID) Like '" &
txtFilter.Text & "*'; "

'test to see if there are any records
Set connFilter = CurrentProject.Connection
Set rsFilter = New ADODB.Recordset
rsFilter.Open strFilter, connFilter, 2, 3

If rsFilter.EOF And rsFilter.BOF Then
MsgBox ("The filter returns no records")
Else
Forms!frmInscriptions.RecordSource = strFilter
End If

Set connFilter = Nothing
Set rsFilter = Nothing

 

[Guest]

I'm not the most expert Access user, but it appears your trap, for no
records, is occurring AFTER you are running your primary query.

Your check should come first, and then move your main query after the ELSE
statement, within the IF..THEN..ELSE of your check function. That way, if
your check fails, the query never runs, and your form never goes blank.

Sharkbyte

 

[Chris Kennedy]

Figured it out. Sort of..... My query is doing a subquery on 2 tables this
confuses ADO. So I just put the results of the query in a temporary table
and used that which works!