Count records in an SQL string

I

Ian Chappel

I have code-generated SQL string, which is to be the RecordSource of a
form.

How do I do a record count before I open the form (I want to avoid opening
if there are no records)?
 
A

Allen Browne

Cancel the Open event of the form if there are no records.

Something like this:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No records"
End If
End Sub
 
I

Ian Chappel

Thanks Allen,

Sorry, I wasn't accurate with my question.

The form's already open. I want to prevent the form's RecordSource being
changed when I click a cmdButton if the new RecordSource would have no
records. So I think I need to count the records as a result of my strSQL
_before_ it becomes the form's RecordSource?
 
A

Allen Browne

If you are building the SQL string dynamically, then you can build the WHERE
clause separately. Then use it in as the Criteria of a DLookup() to see if
any record is returned.

It is possible to respond to the Filter event after the fact, but a
DLookup() sounds like what you are after.

As a worst case, you could assign the SQL string to the SQL property of a
QueryDef you saved for this purpose, and then see if this query generates
any results.
 
I

Ian Chappel

Thanks Allen, All sorted now.

Allen Browne said:
If you are building the SQL string dynamically, then you can build the
WHERE clause separately. Then use it in as the Criteria of a DLookup() to
see if any record is returned.

It is possible to respond to the Filter event after the fact, but a
DLookup() sounds like what you are after.

As a worst case, you could assign the SQL string to the SQL property of a
QueryDef you saved for this purpose, and then see if this query generates
any results.
Click to expand...
 
Top