Null RecordSource Problem

[Jeff via AccessMonster.com]

I have a search button on my form that sets the recordsource to a new
recordset based on the search criteria. My problem is that if the search
returns no records then I get an error message. To get around this I'll
have to check if the recordset is null before I set it. Unfortunately, I
can't seem to get it working. Here's the code that works without null
recorsets. Can someone help me insert the null check into this code.
Thanks,

Dim IDCheck, SQLString AS String

IDCheck = InputBox("Please enter the Case ID you wish to search for.",
"Search", "")

If IDCheck = "" Then
'No value entered or cancel was clicked
Exit Sub
Else
'Search for ID
SQLString = "SELECT * FROM Table WHERE [ID] = "& IDCheck
Me.RecordSource = SQLString
End If

 

[Sylvain Lafontaine]

What is the error message that you get and on what occasion?

Usually, with a bound form, having a query that returns no record shouldn't
rise any message and simply creates an empty recordset with no record
located on a new and empty record (unless that AllowAdditions is set to
False). In your case, I'm not sure of the exact circonstances but you can
try with something like:

If (Me.Recordset.RecordCount = 0) Then ...

or:

If (Me.RecordsetClone.RecordCount = 0) then ...

 

[Jeff via AccessMonster.com]

I got it working with:

Dim IDCheck, SQLString AS String
Dim rs As New ADODB.Recordset
Dim Conn As ADODB.Connection

IDCheck = InputBox("Please enter the Case ID you wish to search for.",
"Search", "")

If IDCheck = "" Then
'No value entered or cancel was clicked
Exit Sub
Else
Set Conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open SQLString, Conn, adOpenKeyset, adLockOptimistic

If Not (rs.EOF And rs.BOF) Then
Me.RecordSource = SQLString
Else
MsgBox "No Records"
End If
End If

 

[Philipp Stiefel]

I have a search button on my form that sets the recordsource to a new
recordset based on the search criteria. My problem is that if the search
returns no records then I get an error message. To get around this I'll
have to check if the recordset is null before I set it. Unfortunately, I
can't seem to get it working. Here's the code that works without null
recorsets.

You should rather deal with that error in your form than
running the query twice to determine if it will return
records before you set it as recordsource. Access-Forms
usually do not produce errors if their recordsource does
not return records, so the error must be caused by your
code.

cheers
Phil