Recordset always empty

[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 don't have acces to the code
but I will post tomorrow if need be. I am using Access 2000.

 

[Dirk Goldgar]

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 don't have acces to the code but I will post tomorrow
if need be. I am using Access 2000.

You'd better post the code. In what event are you doing this?

 

[Chris Kennedy]

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

 

[Dirk Goldgar]

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

What event is this code running in? Have you examined strFilter at run
time to see if it contains what you expect? I don't see anything
fundamentally wrong with the recordset handling though it would be
better practice to explicitly close the recordset you opened.

Note that there is not normally any reason to SetFocus to txtFilter and
use the .Text property of the control to get the value of it -- not if
you're working with Access and an Access text box, rather than VB and a
VB text box. You can use the (default) .Value property of the control
instead, which doesn't require you to set the focus to it. Just write:

" tblNames.InscriptionID =Inscriptions.InscriptionID) Like '" & _
txtFilter & "*'; "

I don't see offhand how that could be contributing to your problem,
though.